How can I update an SQL database table from an XML file?

I found an XML file that updates on a daily basic (on the web), and I would like to create a database table from the data in this file, and to have the option to update it, any help?

I'm using Microsoft visual web developer 2010, what should I do?

Answers


I would store it in a table as follows (with a primary key of your choosing, I'd guess either a surrogate identity column or composite key on currency code and last update):

CREATE TABLE dbo.Currency
(   
    LastUpdate DATE NOT NULL,
    CurrencyCode CHAR(3) NOT NULL,
    Name NVARCHAR(10) NOT NULL,
    Unit INT NOT NULL,
    Country NVARCHAR(50) NOT NULL,
    Rate DECIMAL(10, 4) NOT NULL,
    Change DECIMAL(10, 4) NOT NULL
);

Then you can save the file with a store procedure and query the XML with XQuery:

CREATE PROCEDURE dbo.SaveCurrencyXML @XML XML
AS

    INSERT dbo.Currency (LastUpdate, CurrencyCode, Name, Unit, Country, Rate, Change)
    SELECT  LastUpdate = @XML.value('CURRENCIES[1]/LAST_UPDATE[1]', 'DATE'),
            CurrencyCode = Currency.value('CURRENCYCODE[1]', 'CHAR(3)'),
            Name = Currency.value('NAME[1]', 'NVARCHAR(50)'),
            Unit = Currency.value('UNIT[1]', 'INT'),
            Country = Currency.value('COUNTRY[1]', 'NVARCHAR(50)'),
            Rate = Currency.value('RATE[1]', 'DECIMAL(10, 4)'),
            Change = Currency.value('CHANGE[1]', 'DECIMAL(10, 4)')
    FROM    @XML.nodes('CURRENCIES/CURRENCY') c (Currency);
    GO

Although if you do use a composite key you will need to use MERGE:

CREATE PROCEDURE dbo.SaveCurrencyXMLMERGE @XML XML
AS
    WITH C AS
    (   SELECT  LastUpdate = @XML.value('CURRENCIES[1]/LAST_UPDATE[1]', 'DATE'),
                CurrencyCode = Currency.value('CURRENCYCODE[1]', 'CHAR(3)'),
                Name = Currency.value('NAME[1]', 'NVARCHAR(50)'),
                Unit = Currency.value('UNIT[1]', 'INT'),
                Country = Currency.value('COUNTRY[1]', 'NVARCHAR(50)'),
                Rate = Currency.value('RATE[1]', 'DECIMAL(10, 4)'),
                Change = Currency.value('CHANGE[1]', 'DECIMAL(10, 4)')
        FROM    @XML.nodes('CURRENCIES/CURRENCY') c (Currency)
    )
    MERGE dbo.Currency cu
    USING C 
        ON C.LastUpdate = cu.LastUpdate
        AND C.CurrencyCode = cu.CurrencyCode
    WHEN MATCHED AND 
                C.Name != cu.Name 
            OR  C.Unit != cu.Unit
            OR  C.Country != cu.Country
            OR  C.Rate != cu.Rate
            OR  C.Change != cu.Change
        THEN UPDATE
        SET Name = C.Name,
            Unit = C.Unit,
            Country = C.Country,
            Rate = C.Rate,
            Change = C.Change
    WHEN NOT MATCHED THEN
        INSERT (LastUpdate, CurrencyCode, Name, Unit, Country, Rate, Change)
        VALUES (C.LastUpdate, C.CurrencyCode, C.Name, C.Unit, C.Country, C.Rate, C.Change);

Then you can simply call your procedures to save your data:

DECLARE @X XML = '<CURRENCIES>
                    <LAST_UPDATE>2014-01-30</LAST_UPDATE>
                    <CURRENCY><NAME>Dollar</NAME><UNIT>1</UNIT><CURRENCYCODE>USD</CURRENCYCODE><COUNTRY>USA</COUNTRY><RATE>3.492</RATE><CHANGE>0.057</CHANGE></CURRENCY>
                    <CURRENCY><NAME>Pound</NAME><UNIT>1</UNIT><CURRENCYCODE>GBP</CURRENCYCODE><COUNTRY>Great Britain</COUNTRY><RATE>5.7575</RATE><CHANGE>-0.312</CHANGE></CURRENCY>
                    <CURRENCY><NAME>Yen</NAME><UNIT>100</UNIT><CURRENCYCODE>JPY</CURRENCYCODE><COUNTRY>Japan</COUNTRY><RATE>3.4087</RATE><CHANGE>0.383</CHANGE></CURRENCY><CURRENCY><NAME>Euro</NAME><UNIT>1</UNIT><CURRENCYCODE>EUR</CURRENCYCODE><COUNTRY>EMU</COUNTRY><RATE>4.7492</RATE><CHANGE>-0.204</CHANGE></CURRENCY>
                    <CURRENCY><NAME>Dollar</NAME><UNIT>1</UNIT><CURRENCYCODE>AUD</CURRENCYCODE><COUNTRY>Australia</COUNTRY><RATE>3.0620</RATE><CHANGE>0.219</CHANGE></CURRENCY>
                    <CURRENCY><NAME>Dollar</NAME><UNIT>1</UNIT><CURRENCYCODE>CAD</CURRENCYCODE><COUNTRY>Canada</COUNTRY><RATE>3.1225</RATE><CHANGE>-0.24</CHANGE></CURRENCY>
                    <CURRENCY><NAME>krone</NAME><UNIT>1</UNIT><CURRENCYCODE>DKK</CURRENCYCODE><COUNTRY>Denmark</COUNTRY><RATE>0.6365</RATE><CHANGE>-0.188</CHANGE></CURRENCY>
                    <CURRENCY><NAME>Krone</NAME><UNIT>1</UNIT><CURRENCYCODE>NOK</CURRENCYCODE><COUNTRY>Norway</COUNTRY><RATE>0.5602</RATE><CHANGE>-0.568</CHANGE></CURRENCY>
                    <CURRENCY><NAME>Rand</NAME><UNIT>1</UNIT><CURRENCYCODE>ZAR</CURRENCYCODE><COUNTRY>South Africa</COUNTRY><RATE>0.3099</RATE><CHANGE>-1.054</CHANGE></CURRENCY>
                    <CURRENCY><NAME>Krona</NAME><UNIT>1</UNIT><CURRENCYCODE>SEK</CURRENCYCODE><COUNTRY>Sweden</COUNTRY><RATE>0.5378</RATE><CHANGE>-0.407</CHANGE></CURRENCY>
                    <CURRENCY><NAME>Franc</NAME><UNIT>1</UNIT><CURRENCYCODE>CHF</CURRENCYCODE><COUNTRY>Switzerland</COUNTRY><RATE>3.8827</RATE><CHANGE>0.072</CHANGE></CURRENCY>
                    <CURRENCY><NAME>Dinar</NAME><UNIT>1</UNIT><CURRENCYCODE>JOD</CURRENCYCODE><COUNTRY>Jordan</COUNTRY><RATE>4.9370</RATE><CHANGE>0.032</CHANGE></CURRENCY>
                    <CURRENCY><NAME>Pound</NAME><UNIT>10</UNIT><CURRENCYCODE>LBP</CURRENCYCODE><COUNTRY>Lebanon</COUNTRY><RATE>0.0232</RATE><CHANGE>0</CHANGE></CURRENCY>
                    <CURRENCY><NAME>Pound</NAME><UNIT>1</UNIT><CURRENCYCODE>EGP</CURRENCYCODE><COUNTRY>Egypt</COUNTRY><RATE>0.5016</RATE><CHANGE>0.06</CHANGE></CURRENCY>
                </CURRENCIES>';

EXECUTE dbo.SaveCurrencyXML @X;

Example on SQL Fiddle


ADDENDUM

I assume when you say you are inserting with:

Insert into History values('{0}','{1}','{2}','{3}','{4}')", date, id, "Deposit", amount, curr

You are actually using String.Format, i.e.:

var sql = String.Format("Insert into History values('{0}','{1}','{2}','{3}','{4}')", date, id, "Deposit", amount, curr);

If so PLEASE STOP IMMEDIATELY. This is really bad practise, it not only makes you vunerable to SQL injection, it also forces recompilation of the query every time as it is unable to make use of cached query plans. Another downside is that it is not strongly typed, in your query, assuming date is of the DateTime type, you automatically call the ToString() method on this, which will return a string dependant on the culture info of the thread calling it, there is no guarantee the output string will be in a format that the database recognises as a date time, so if your destination column is a datetime you will get a conversion error.

Instead you should use parameterised queries, but first, although I suspect you may also have shortened your query for example purposes, but it is always a good idea to explicitly state the column list when inserting, so your final query would become:

var sql = "INSERT History (Date, Id, Deposit, Amount, Curr) VALUES (@Date, @Id, @Deposit, @Amount, @Curr);";

You then need to add your parameters to your SqlCommand, e.g.

var date = DateTime.Now;
command.Parameters.AddWithValue("@Date", date);

This means that when sent to the DB the query will treat @Date as a DateTime (because the value passed was a datetime) meaning your query is much more type safe.

So for your example, to call the stored procedures above you would use something like:

//using System.Xml;
//using System.Data.SqlClient;
//using System.Data.SqlTypes;

XmlDocument xmldoc = new XmlDocument();
xmldoc.Load(@"YourXMLFile.xml");
using (var connection = new SqlConnection("ConnectionString"))
using (var command = new SqlCommand("dbo.SaveCurrencyXML", connection))
{
    var xml = new SqlXml(new XmlTextReader(xmldoc.InnerXml, XmlNodeType.Document, null));
    var param = new SqlParameter("@XML", SqlDbType.Xml).Value = xml;
    command.Parameters.Add(param);
    command.ExecuteNonQuery();
}

Need Your Help

Linq to XML query returns null

c# xml linq linq-to-xml

I have the following XML file:

Why will Java not open up a Batch file when executed with a ProcessBuilder?

java eclipse windows batch-file

I've coded a Java program to open up a Batch file that is imported into the resources of the program.