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 flatten this and put 开发者_JS百科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.
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();
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
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}
加载中,请稍侯......
精彩评论