Convert XmlDocument to DataTable using LinqToXml

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:

<sales>
<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>
<tipo code=”V”>
<row code=”002039055″ qty=”21″ price=”7.290″ />
<row code=”003366150″ qty=”87″ price=”4.600″ />
</tipo>
</day>
</month>
</year>
</pos>
</sales>

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
dataTable.Rows.Add(dataRow);
}

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

Technorati Tag: xmlDocument,linqToXml,dataTable