xml to sql using linq and C#

Question

I have an xml file from an external system that looks like this.

<?xml version="1.0" encoding="UTF-8"?>
<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
    <Body>
        <Element1>
            <Element2 day="2009-10-18">
                <Element3 name="Joe">
                    <Element4 time="1">
                        <Element5 amount="0" price="16.58"/>
                    </Element4>
                </Element3>
                <Element3 name="Fred">
                    <Element4 time="5">
                        <Element5 amount="0" price="15.41"/>
                    </Element4>
                </Element3>
            </Element2>
        </Element1>
    </Body>
</Envelope>

I need to flatten this and put it into a sql table that will look like this.

day, name, time, amount, price
2009-10-18, Joe, 1, 0, 16.58
2009-10-18, Fred, 5, 0, 15.41

What is the best way to read the xml and insert it into my DB? I've been playing with linq but without much sucess thus far.

The answer will depend greatly on whether or not you need the code to build the table structure, or just insert the data into a preexisting table. — Robert Harvey
– Robert Harvey, Commented Jan 21, 2010 at 3:47
Thanks Robert, I need to insert data into a pre existing table. — nelsonwebs
– nelsonwebs, Commented Jan 22, 2010 at 13:54

nelsonwebs · Accepted Answer · 2010-02-01 20:45:16Z

This article is a good starting point for the basic idea.

Extracting XML and Insert Bulk Data using LINQ to XML and LINQ to SQL

Adding a bit more here for those who might be interested in a similar solution.

            XDocument doc = XDocument.Load(@"myxml.xml");
            DataContext bt = new DataContext();

            var docxml = from c in doc.Elements("Envelope").Elements("Body").Elements("Element1").Elements("Element2").Elements("Element3")

                     select new mytable()
                     {
                         MyKey = Guid.NewGuid(),
                         day = Convert.ToDateTime(c.Parent.Attribute("day").Value),
                         name = c.FirstAttribute.Value,
                         time = Convert.ToInt32(c.Element("Element4").FirstAttribute.Value),
                         price = Convert.ToDecimal(c.Element("Element4").Element("Element5").Attribute("price").Value),
                         amount = Convert.ToDecimal(c.Element("Element4").Element("Element5").Attribute("amount").Value)

                     };

            bt.mytable.InsertAllOnSubmit(docxml);
            bt.SubmitChanges();

marc_s · Accepted Answer · 2010-01-21 06:24:51Z

Do you need to insert that into a SQL Server table?? If so: what version of SQL Server?

You could easily just shred that apart in SQL Server using XQuery and insert the data into a table. Use something like:

;WITH XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS ns)
SELECT
    @input.value('(/ns:Envelope/ns:Body/ns:Element1/ns:Element2/@day)[1]', 'varchar(50)') AS 'DayElement',
    node.el.value('(@name)[1]', 'varchar(50)') AS 'Name',
    node.el.value('(ns:Element4/@time)[1]', 'int') AS 'Time',
    node.el.value('(ns:Element4/ns:Element5/@amount)[1]', 'decimal(15,2)') AS 'Amount',
    node.el.value('(ns:Element4/ns:Element5/@price)[1]', 'decimal(15,2)') AS 'Price'
FROM
    @input.nodes('/ns:Envelope/ns:Body/ns:Element1/ns:Element2/ns:Element3') AS node(el)

and that gives you an output something like:

Day         Name    Time    Amount   Price
2009-10-18  Joe       1      0.00    16.58
2009-10-18  Fred      5      0.00    15.41

Of course, you could use that to supply the data to a INSERT INTO dbo.MyTable()..... statement and thus store your data into a table right away

mythz · Accepted Answer · 2010-02-02 13:43:16Z

0

I have useful Open Source XLinq Extensions and other Utils that simplify parsing xml, persisting to the db, text serializing POCO classes, etc The complete example is below:

    //source code for this example available at: 
    //http://code.google.com/p/servicestack/source/browse/trunk/Common/ServiceStack.Common/ServiceStack.Common.Tests/Xlinq/XlinqExtensionsTests.cs
    public class XmlData : IHasId<int>
    {
      [AutoIncrement]
      public int Id { get; set; }
      public string Day { get; set; }
      public string Name { get; set; }
      public int Time { get; set; }
      public int Amount { get; set; }
      public decimal Price { get; set; }
    }

    [Test]
    public void Insert_data_from_xml_into_db()
    {
      //OrmLiteConfig.DialectProvider = SqlServerOrmLiteDialectProvider.Instance;
      OrmLiteConfig.DialectProvider = SqliteOrmLiteDialectProvider.Instance;

      var element2 = XElement.Parse(xml).AnyElement("Body").AnyElement("Element1").AnyElement("Element2");

      using (var db = ":memory:".OpenDbConnection())
      using (var dbCmd = db.CreateCommand())
      {
        dbCmd.CreateTable<XmlData>(true);
        foreach (var element3 in element2.AllElements("Element3"))
        {
          var xmlData = new XmlData {
            Day = element2.AnyAttribute("day").Value,
            Name = element3.AnyAttribute("name").Value,
            Time = int.Parse(element3.FirstElement().AnyAttribute("time").Value),
            Amount = int.Parse(element3.FirstElement().FirstElement().AnyAttribute("amount").Value),
            Price = decimal.Parse(element3.FirstElement().FirstElement().AnyAttribute("price").Value),
          };
          dbCmd.Insert(xmlData);
        }
        dbCmd.Select<XmlData>().ForEach(x => Console.WriteLine(StringSerializer.SerializeToString(x)));
      }
    }

//Prints out:
//{Id:1,Day:2009-10-18,Name:Joe,Time:1,Amount:0,Price:16.58}
//{Id:2,Day:2009-10-18,Name:Fred,Time:5,Amount:0,Price:15.41}

edited Feb 2, 2010 at 13:43

answered Jan 26, 2010 at 2:52

mythz

144k31 gold badges253 silver badges400 bronze badges

3 Comments

nelsonwebs Over a year ago

Interesting but I think the solution I added above is more simple and doesn't required additional components.

mythz Over a year ago

Agreed, but I consider it good practice when processing data to do it in code as its strongly-typed, easily testable, portable (i.e. non vendor-specific) and re-usable, etc. The above example only works on specific versions of Sql Server (i.e. 2005+) This is a complete working version in code with only 1 additional component.

mythz Over a year ago

Apologies, I was comparing it against the wrong answer. Yeah yours is in code (which is good) but not complete and requires a generated Linq2Sql model, configuration, existing table in Sql Server, etc - mine doesn't.

Collectives™ on Stack Overflow

xml to sql using linq and C#

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related