loads of useful information, examples and tutorials pertaining to web development utilizing asp.net, c#, vb, css, xhtml, javascript, sql, xml, ajax and everything else...

 



Advertise Here
C-Sharpener.com - Programming is Easy!  Learn Asp.Net & C# in just days, Guaranteed!

XML to DataTable with LINQ

by naspinski 6/12/2008 5:47:00 AM

Easy way to get your XML into a DataTable

Now I might just be blind, or incredibly incapable at searching google or reading my LINQ books (very possible) but I hadn't found a simple way to get a 2-level XML document into a DataTable for use in a GridView or just simple DataTable manipulation utilizing LINQ (I assume this is because DTs are 2 dimensional, and XML files can be all sorts of mash-ups of information). Since LINQ is so powerful, I assumed it wouldn't be all that difficult, turns out I was right, it's pretty easy. Here is the code:

public DataTable XElementToDataTable(XElement x)
{
  DataTable dt = new DataTable();

  XElement setup = (from p in x.Descendants() select p).First();
  foreach (XElement xe in setup.Descendants()) // build your DataTable
    dt.Columns.Add(new DataColumn(xe.Name.ToString(), typeof(string))); // add columns to your dt

  var all = from p in x.Descendants(setup.Name.ToString()) select p;
  foreach (XElement xe in all)
  {
    DataRow dr = dt.NewRow();
    foreach (XElement xe2 in xe.Descendants())
      dr[xe2.Name.ToString()] = xe2.Value; //add in the values
    dt.Rows.Add(dr);
  }
  return dt;
}

This is completely dynamic, so it doesn't matter what or how many elements you have. It does rely on the first element to set the DataTable columns, so make sure that one is complete. Though the XML does have to be limited to 2-dimensional elements; in fact, I am not even sure what happens if you feed the function bad data? The XML should resemble this structure:

<?xml version="1.0" encoding="utf-8"?>
<root>
      <person>
            <age>26</age> 
            <name>stan</name> 
            <hobbies>partying</hobbies> 
      </person>
      <person>
            <age>26</age> 
            <name>matt</name> 
            <hobbies>being lame</hobbies> 
      </person>
</root>

In that structure, each person will be a row, and age, name and hobbies will the the columns in the datatable:
agenamehobbies
26 stan partying
26 matt being lame
Call it like this:

// load your xml file (this one is named people and it is in my App_Data folder)
XElement x = XElement.Load(Server.MapPath(".") + "\\App_Data\\people.xml");//get your file
// declare a new DataTable and pass your XElement to it
DataTable dt = XElementToDataTable(x);

And that's it, you have your DataTable.


Tags: , ,

c# | linq | xml

Related posts

Comments

6/12/2008 2:50:37 PM

jeffld
This post caught my attention because XML can often be difficult because some people tend to use it in very complicated ways.

I was especially interested in an XML file that was more than 1 level deep.

The XML that you have in this example is not valid. XML files must only have one root element.

How about if the XML had another level deep like this?

<?xml version="1.0" encoding="utf-8"?>
<root>
<person>
<name>stan</name>
<properties>
<age>26</age>
<hobbies>partying</hobbies>
</properties>
</person>
<person>
<name>matt</name>
<properties>
<hobbies>taking dumps</hobbies>
<age>26</age>
</properties>
</person>
</root>

jeffld us

6/13/2008 7:30:45 AM

naspinski
Thanks for pointing that out, when I was copy/pasting example xml, I accidentally left in that extra <root> -- fixed

The program actually works with what you presented (it was how the xml was supposed to read in the first place if I hadn't screwed it up).

naspinski us

1/2/2009 3:05:22 AM

Bennett
I'm confused - why not just use:

MyDataSet.ReadXmlSchema(schemafilepath)
MyDataSet.ReadXml(datafilepath)

Where the schema is an XML file that defines the datatables and the other file is the data itself. As far as I know the .NET framework can do all this for you.

I have a very complex XML file consisting of several inter-related datatables. I'm basically using the XML format as a file format to save the dataset and schema, but it means that I can if need be change the raw data or the database/datatable schema by editing the XML files. I can also make changes to the DT programmatically and save to XML with WriteXmlSchema and WriteXml.

When the DataSet is loaded I can either access the datatables contained in it on the fly but I've also used DataViews to filter and sort the underlying DataTables where needed.

I'm wondering if LINQ can make my searches/filters more efficient and that's how I stumbled on your post Smile

Bennett

Bennett us

4/2/2009 8:21:29 AM

Alex Jones
Thank you very much this worked extremely well for me. It was easy to understand and implement. I won't be scared of parsing xml files now. I will del.icio.us'ing this post.

Thanks a million Laughing

Alex Jones gb

5/23/2012 11:15:06 AM

pingback
Pingback from dkphp.com

Getting data from HTML table into a datatable | PHP Developer Resource

dkphp.com


Comments are closed