How to convert xml to DataTable?

Question

I have a file with the content like xml, I want to parse it and bind data to Datatable. How can I do it ? My file xml look like this

    <StudentListSubject>
        <Member personId="1" active="yes">
          <Name id="Mary"/>
          <SubjectList>
                <Subjectvalue>Maths</Subjectvalue>
                <Subjectvalue>Literature</Subjectvalue>
          </SubjectList>
        </Member>
        <Member personId="2" active="yes">
          <Name id="John"/>
          <SubjectList>
                <Subjectvalue>Physics</Subjectvalue>
                <Subjectvalue>Maths</Subjectvalue>
                <Subjectvalue>Literature</Subjectvalue>
          </SubjectList>
        </Member>
    </StudentListSubject>

DataTable will 3 columns as below

PersonID#ID#Subject
1#Mary#Maths,Literature
2#John#Physics,Maths,Literature

I have used XmlReader to get data, but I don't know how to get id and subject list, only get person id.

        var _dt = new DataTable();
        _dt.Columns.Add("ID", typeof(string));
        _dt.Columns.Add("Name", typeof(string));
        _dt.Columns.Add("Subject", typeof(string));
        var _rd = XmlReader.Create("C:\\test.xml");
        _rd.ReadToFollowing("Member");
        do
        {
            var _str = "";
            _rd.MoveToFirstAttribute();
            _str += _rd.Value; //get personID
            _rd.ReadToFollowing("Name");
            _rd.MoveToFirstAttribute();
            _str += "#" + _rd.Value; //get ID

            ////get subject list

            _dt.Rows.Add(_str.Split('#'));
        } while (_rd.ReadToFollowing("Member"));

Answer 1

you can use XDocument.Parse to prase the xml File. Remember to add this reference using System.Xml.Linq;

    string xml = File.ReadAllText("yourFile");

    XDocument doc = XDocument.Parse(xml);

    DataTable dataTable = new DataTable();
    dataTable.Columns.Add("PersonID");
    dataTable.Columns.Add("ID");
    dataTable.Columns.Add("Subject");

    foreach (var member in doc.Descendants("Member"))
    {
        string personId = member.Attribute("personId").Value;
        string id = member.Element("Name").Attribute("id").Value;
        string subjects = string.Join(",", member.Descendants("Subjectvalue").Select(sv => sv.Value));

        dataTable.Rows.Add(personId, id, subjects);
    }

see this code: fiddle