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:

<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.

Lascia un commento

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

%d blogger cliccano Mi Piace per questo: