Geek Answers Handbook

Xml to sql using linq and c #

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 smooth this out and put in 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 xml and paste it into my db? I played with linq, but still have not had much success.

+1
source share
3 answers

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

Extract XML and insert bulk data using LINQ to XML and LINQ to SQL

Adding a little more for those who may 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();
+2
source

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

You can simply simply drop this into SQL Server using XQuery and paste the data into the 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 this will give you the result:

 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 can use this to provide data to the INSERT INTO dbo.MyTable()..... and thus immediately save your data in a table

+1
source

I have useful Open Source XLinq Extensions and other Utils that simplify xml parsing, persist in db, serialize POCO classes, etc. The following is a complete example :

  //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}
0
source

More articles:


All Articles