linq xml to Datatable : The Official Microsoft ASP.NET Forums

Reply

jeyaseelan@a...

Contributor

5124 Points

2025 Posts

linq xml to Datatable

Feb 03, 2009 06:45 AM|LINK

Hi to all,

I have a xml file, first read some records from that xml file using LINQ then those filtered records i want to pass into DataTable object so i use CopyToDataTable() method now i look at the DataTable object it seems

HasAttributes	HasElements	IsEmpty	Value
Uncheckbox	CheckBox	Uncheckbox	1mary23
Uncheckbox	CheckBox	Uncheckbox	2joseph34
Uncheckbox	CheckBox	Uncheckbox	3john44
Uncheckbox	CheckBox	Uncheckbox	4yova23
Uncheckbox	CheckBox	Uncheckbox	5yehova32
Uncheckbox	CheckBox	Uncheckbox	6mary23
Uncheckbox	CheckBox	Uncheckbox	7joseph34
Uncheckbox	CheckBox	Uncheckbox	8john44
Uncheckbox	CheckBox	Uncheckbox	9yova23

This is my source code

string xmlFile = "simple.xml";

XDocument document = XDocument.Load(xmlFile);

var booksQuery = from books in document.Elements("NewDataSet").Elements("Table")

select books;

DataTable dttemp = new DataTable();

dttemp = booksQuery.CopyToDataTable();

actually in the xml file i have id,name and age column only but here all are combined in a single column and lot of unrelated columns also added automatically,And the sametime i dont know what are the column available in xml file so i cant specify column names and write a simple query and i should complete this task by LINQ

Experts anybody tell me how to display a valid column on DataTable from xml by LINQ.

Any doubts please feel free to ask me. If this post is answer of your question then don't forgot to Click "Mark As Answer".
J.Jeyaseelan

Edited by jeyaseelan@ajsquare.net on Feb 03, 2009 09:19 AM

Reply

jeyaseelan@a...

Contributor

5124 Points

2025 Posts

bug in LINQ?

Feb 04, 2009 03:13 AM|LINK

Experts i think this is a bug in LINQ because last 3 weeks i'm trying to find the solution but still now no solution,

actually i have this code

public DataTable XElementToDataTable(XDocument x)

{

DataTable dt = new DataTable();

XElement setup = (from p in x.Descendants("NewDataSet").Descendants("Table") select p).First();

DataTable dttemp = new DataTable();

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;

}

but here data added to Datatable by for ..loop but in my case i have millions of records in xml file so by this method performance will going down.

Experts please tell me is there any chance to solve this problem?

Any doubts please feel free to ask me. If this post is answer of your question then don't forgot to Click "Mark As Answer".
J.Jeyaseelan

Edited by jeyaseelan@ajsquare.net on Feb 04, 2009 03:18 AM

Reply

davidfowl

Contributor

2699 Points

611 Posts

Microsoft

Re: bug in LINQ?

Feb 04, 2009 06:10 AM|LINK

Try this:

public DataTable XElementToDataTable(XElement element) {
    DataSet ds = new DataSet();
    ds.ReadXml(new StringReader(element.ToString()));
    return ds.Tables[0];
}

I am not sure of the performance implication. You can compare it to your method.

David Fowler
Senior SDE, ASP.NET Team, Microsoft

Marked as answer by jeyaseelan@ajsquare.net on Feb 04, 2009 08:45 AM

Reply

jeyaseelan@a...

Contributor

5124 Points

2025 Posts

Re: bug in LINQ?

Feb 04, 2009 06:33 AM|LINK

Davidfowl thanks information. But i want to do this by LINQ only

Actually you just execute this code with break points

string xmlFile = "simple.xml";

XDocument document = XDocument.Load(xmlFile);

var booksQuery = from books in document.Elements("NewDataSet").Elements("Table")

select books;

DataTable dttemp = new DataTable();

dttemp = booksQuery.CopyToDataTable();

when the control cross above code you just move your mouse pointer to dttemp and click the Zoom option it will display the records like this

HasAttributes	HasElements	IsEmpty	Value
Uncheckbox	CheckBox	Uncheckbox	1mary23
Uncheckbox	CheckBox	Uncheckbox	2joseph34
Uncheckbox	CheckBox	Uncheckbox	3john44
Uncheckbox	CheckBox	Uncheckbox	4yova23
Uncheckbox	CheckBox	Uncheckbox	5yehova32
Uncheckbox	CheckBox	Uncheckbox	6mary23
Uncheckbox	CheckBox	Uncheckbox	7joseph34
Uncheckbox	CheckBox	Uncheckbox	8john44
Uncheckbox	CheckBox	Uncheckbox	9yova23

Acutally i have only id,name and age column in the xml file but why its coming like this, i expect the exact columns only, could you please tell me how to do this.

Any doubts please feel free to ask me. If this post is answer of your question then don't forgot to Click "Mark As Answer".
J.Jeyaseelan

Reply

davidfowl

Contributor

2699 Points

611 Posts

Microsoft

Re: bug in LINQ?

Feb 04, 2009 07:06 AM|LINK

Try this extension method:

public static class XElementExtensions {
    public static DataTable ToDataTable(this XElement element) {
        DataSet ds = new DataSet();
        string rawXml = element.ToString();
        ds.ReadXml(new StringReader(rawXml));
        return ds.Tables[0];
    }


    public static DataTable ToDataTable(this IEnumerable<XElement> elements) {
        return ToDataTable(new XElement("Root", elements));
    }
}

Usage:

protected void Page_Load(object sender, EventArgs e) {
    string xmlFile = Server.MapPath("~/Data.xml");
    XDocument document = XDocument.Load(xmlFile);
    var booksQuery = from b in document.Elements("NewDataSet").Elements("Table")
                      select b;

    DataTable table = booksQuery.ToDataTable();
    GridView1.DataSource = table;
    GridView1.DataBind();
}

David Fowler
Senior SDE, ASP.NET Team, Microsoft