Convert Xml to DataTable

I have an XML file I want to insert that in a Datatable.
The format of the xml file is like below:

<userid ID="37729">
  <TestId ID="84" TimeRemaining="60" />
  <QuestId ID="1">
    <Answer1>
    </Answer1>
    <Answer2>B</Answer2>
    <Answer3>
    </Answer3>
    <Answer4>
    </Answer4>
  </QuestId>
</userid>

Now I want to insert that in a data table like below:

Question Id     Answer1      Answer2        Answer3        Answer4
1                 A                                         D

2                             B              C     

3                                            C

Can any one help me to achieve this.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

I would first create a DataTable with the columns that you require, then populate it via Linq-to-XML.

You could use a Select query to create an object that represents each row, then use the standard approach for creating DataRows for each item …

class Quest
{
    public string Answer1;
    public string Answer2;
    public string Answer3;
    public string Answer4;
}

public static void Main()
{
    var doc = XDocument.Load("filename.xml");

    var rows = doc.Descendants("QuestId").Select(el => new Quest
    {
        Answer1 = el.Element("Answer1").Value,
        Answer2 = el.Element("Answer2").Value,
        Answer3 = el.Element("Answer3").Value,
        Answer4 = el.Element("Answer4").Value,
    });

    // iterate over the rows and add to DataTable ...

}

Method 2

DataSet ds = new DataSet();
ds.ReadXml(fileNamePath);

Method 3

How To Read XML Data into a DataSet by Using Visual C# .NET contains some details. Basically, you can use the overloaded DataSet method ReadXml to get the data into a DataSet. Your XML data will be in the first DataTable there.

There is also a DataTable.ReadXml method.

Method 4

You can use this code(Recommended)

 MemoryStream objMS = new MemoryStream();
 DataTable oDT = new DataTable();//Your DataTable which you want to convert
 oDT.WriteXml(objMS);
 objMS.Position = 0;
 XPathDocument result = new XPathDocument(objMS);

This is another way but first ex. is recommended

StringWriter objSW = new StringWriter();
DataTable oDt = new DataTable();//Your DataTable which you want to convert
oDt.WriteXml(objSW);
string result = objSW.ToString();

Method 5

Maybe this could be a little older article. but must of the above answers don´t help me as I need. Then I wrote a little snippet for that.

This accepts any XML that hast at least 3 levels (Like this sample):

<XmlData>
    <XmlRow>
        <XmlField1>Data 1</XmlField1>  
        <XmlField2>Data 2</XmlField2>  
        <XmlField3>Data 3</XmlField3>  
        .......
    </XmlRow>
</XmlData>

public static class XmlParser
{
    /// <summary>
    /// Converts XML string to DataTable
    /// </summary>
    /// <param name="Name">DataTable name</param>
    /// <param name="XMLString">XML string</param>
    /// <returns></returns>
    public static DataTable BuildDataTableFromXml(string Name, string XMLString)
    {
        XmlDocument doc = new XmlDocument();
        doc.Load(new StringReader(XMLString));
        DataTable Dt = new DataTable(Name);
        try
        {

            XmlNode NodoEstructura = doc.FirstChild.FirstChild;
            //  Table structure (columns definition) 
            foreach (XmlNode columna in NodoEstructura.ChildNodes)
            {
                Dt.Columns.Add(columna.Name, typeof(String));
            }

            XmlNode Filas = doc.FirstChild;
            //  Data Rows 
            foreach (XmlNode Fila in Filas.ChildNodes)
            {
                List<string> Valores = new List<string>();
                foreach (XmlNode Columna in Fila.ChildNodes)
                {
                    Valores.Add(Columna.InnerText);
                }
                Dt.Rows.Add(Valores.ToArray());
            }
        } catch(Exception)
        {

        }

        return Dt;
    }
}

This solve my problem

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0