Tag Archives: dataTable

Convert XmlDocument to DataTable using LinqToXml

3 Mag

I found this solution to import in my database an XML file received from an external legacy system via a POP3 server. I used an open source library to get the XML file on my hard disk. Now I need to bulk insert this information into a remote server where I can access only using MSSQL protocol so I dediced to use SqlBulkCopy class. In order to use this class I need to transfor the XML document into a DataTable.

This a sample of my XML file:

<pos code="000000" index="0000000003">
<year value="2007">
<month value="04">
<day value="02">
<tipo code="L">
<row code="002039055" qty="1" price="7.290" />
<row code="002309045" qty="134" price="2.390" />
<row code="002427274" qty="21" price="6.500" />
<row code="003366150" qty="1" price="4.600" />
<row code="003785045" qty="0.1" price="6.940" />
<tipo code="V">
<row code="002039055" qty="21" price="7.290" />
<row code="003366150" qty="87" price="4.600" />

and this the code
XElement xElement = XElement.Load("test.xml");
var elements = from f in xElement.Descendants("riga")
select new
Code = f.Attribute("code").Value,
Quantity = f.Attribute("qty").Value,
Price = f.Attribute("price").Value,
Type = f.Parent.Attribute("code").Value,
Day = f.Parent.Parent.Attribute("value").Value,
Month = f.Parent.Parent.Parent.Attribute("value").Value,
Year = f.Parent.Parent.Parent.Parent.Attribute("value").Value,
} ;

Now you can build you DataTable using the common syntax

DataTable dataTable = new DataTable("dbo.Vendite");
dataTable.Columns.Add("data", typeof(DateTime));
//others columns

and the you can add rows using a foreach loop

foreach (var element in elements)
DataRow dataRow = dataTable.NewRow();
dataRow["data"] = element.Date;
//others columns

now I can use SqlBulkCopy to insert data in the server locate inside LAN.