CODEWITHSUNDEEP
c#sql-serverxmldatatable
ic3man7019
ic3man7019

Reputation: 711

How can I create a DataTable from an XML file in C#?

I asked a similar question recently, but it turns out that I had the wrong idea going into it, so the answer to the question I asked was not exactly what I was looking for. I have the following XML file: 

<?xml version="1.0" encoding="UTF-8"?>
<items found="132" limit="1000">
    <sampleField>
        <att1>18
        </att1>
        <att2>4343
        </att2>
        <att3>94007961
        </att3>
        <att4>Test
        </att4>
        <att5>Example
        </att5>
   </sampleField>

   <sampleField>
        <att1>40
        </att1>
        <att2>8945
        </att2>
        <att3>94089741
        </att3>
        <att4>Test2
        </att4>
        <att5>Example2
        </att5>
   </sampleField>
</items>

I want to create a DataTable (that will later be converted to a SQL Server database table) with the column names being the tag names of the children of one of the <sampleField> elements. The actual values of the <att#> elements should represent the different rows in the table. The table should look like this:

How could I go about creating this DataTable from this XML file in C#? I have tried dataTable.ReadXML(), but I got an error message that said this: DataTable does not support schema inference from Xml. I have also tried using a DataSet to get around this error, but it created two different tables. Any ideas?

Upvotes: 0

Views: 3130

Answers (1)

santosh singh
santosh singh

Reputation: 28672

You can access the required table from table collections

    DataSet ds=new DataSet();
    ds.ReadXml(@"XML PATH");
  var dataTable=  ds.Tables[1]

Update

foreach (DataRow dataRow in dataTable.Rows)
  {
   foreach (var item in dataRow.ItemArray)
   {
    Console.WriteLine(item);
   }
  }

Using Linq You can also use linq for parsing the xml

var doc = XDocument.Load(@"a.xml");
    var table = from x in doc.Descendants("sampleField")
                select new
                {
                    Attr1 = x.Element("att1").Value,
                    Attr2 = x.Element("att2").Value,
                    Attr3 = x.Element("att3").Value,
                    Attr4 = x.Element("att4").Value,
                    Attr5 = x.Element("att5").Value,


                };

Upvotes: 2

Related Questions