Writing into datatable from XML

Question

1.00/5 (1 vote)

See more:

Below is my XML structure.

XML

<school>
  <Identification rollno="01">
    <Information Name="ABC" Age="21" Sex="M" /> 
  </Identification>
</school>

I want to read this XML format into DataTable so that DataTable has data in form of as mentioned below

Rollno Name Age Sex
01 ABC 21 M

How can I achieve this?

Thank You.

What I have tried:

XmlTextReader xmlContent = new XmlTextReader(@"XMLPath");
DataSet tableSet = new DataSet();
tableSet.ReadXml(xmlContent);
DataTableObject = tableSet.Tables[0];
With the above code I was able to read the XML but the output which DataTable had was not as I have specified in the questio and it is also creating a cloumn names IdentificationID in the UI which I don't need

Posted 8-Jun-16 23:06pm

partha143

Updated 11-Jun-16 21:34pm

v2

Add a Solution

Comments

Richard Deeming 9-Jun-16 16:25pm

REPOST
This is exactly the same question that you posted yesterday:
http://www.codeproject.com/Questions/1105416/Writing-XML-into-datatable[^]

2 solutions

Solution 2

You can also do basically the same thing using the XElement Class (System.Xml.Linq)[^]

C#

string s = @"<school>
    <Identification rollno=""01"">
        <Information Name=""ABC"" Age=""21"" Sex=""M"" />
    </Identification>
    <Identification rollno=""02"">
        <Information Name=""BCD"" Age=""22"" Sex=""F"" />
    </Identification>
</school>";

XElement xe = XElement.Parse(s);
// Or if you have a file
// XElement xe = XElement.Load(fileName);
var result = (from el in xe.Elements("Identification")
              let subEl = el.Element("Information")
              select new
              {
                  RollNo = int.Parse(el.Attribute("rollno").Value),
                  Name = subEl.Attribute("Name").Value,
                  Age = int.Parse(subEl.Attribute("Age").Value),
                  Sex = char.Parse(subEl.Attribute("Sex").Value),
              }).ToList();

dataGridView.DataSource = result;

To convert the result to a DataTable, you have to loop through the list as suggested in Solution 1.

Posted 11-Jun-16 21:34pm

George Jonsson

Updated 11-Jun-16 21:36pm

v3

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2016-06-09T07:05:00

Quote:
it is also creating a cloumn names IdentificationID in the UI which I don't need

But if you look carefully at the value in that column it is 0.

If you also examine the contents of tableSet.Tables[1], it also has this "un-needed" column Identification_Id and it also has a value of 0.

It becomes more apparent what that extra column is doing if you change your XML to be

XML

<school>
  <Identification rollno="01">
    <Information Name="ABC" Age="21" Sex="M" /> 
  </Identification>
  <Identification rollno="02">
    <Information Name="BCD" Age="22" Sex="F" /> 
  </Identification>
  </school>

Now look at the two datatables in the dataset ... Identification_Id = 0 matches up your original data and Identification_Id = 1 matches up the rows with my new data item.

So you can JOIN the two data tables together like this

C#

var dt1 = tableSet.Tables[0];   // contains the identification stuff
var dt2 = tableSet.Tables[1];   // contains the Information stuff

var res = (from t1 in dt1.AsEnumerable()
    join t2 in dt2.AsEnumerable() on (int) t1["Identification_Id"] equals (int) t2["Identification_Id"]
    select new
    {
        rollno = t1["rollno"],
        Name = t2["Name"],
        Age = t2["Age"],
        Sex = t2["Sex"]
    }).ToList();

or if you prefer this format...

C#

var res = dt1.AsEnumerable()
            .Join(dt2.AsEnumerable(), t1 => (int) t1["Identification_Id"], t2 => (int) t2["Identification_Id"],
                (t1, t2) => new
                {
                    rollno = t1["rollno"],
                    Name = t2["Name"],
                    Age = t2["Age"],
                    Sex = t2["Sex"]
                }).ToList();

You've mentioned DataGrid in your tags so you could just use

C#

dataGrid1.DataSource = res;

Or if you really do want a DataTable as a result you can do something like

C#

var resultTable = new DataTable();
resultTable.Columns.Add("rollno");
resultTable.Columns.Add("Name");
resultTable.Columns.Add("Age");
resultTable.Columns.Add("Sex");

foreach (var i in res)
    resultTable.Rows.Add(i.rollno, i.Name, i.Age, i.Sex);

remembering to handle any null values if you need to.