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:
| age | name | hobbies |
| 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.