How to read XML into a DataTable?

Question

I have some XML in a string in memory exactly like this:

<symbols>
  <symbol>EURCHF</symbol>
  <symbol>EURGBP</symbol>
  <symbol>EURJPY</symbol>
  <symbol>EURUSD</symbol>
</symbols>

I want to read this into a DataTable. I am doing it like this:

DataTable dt = new DataTable();
dt.TableName = "symbols";
dt.Columns.Add("symbol");

if (!String.IsNullOrEmpty(symbols))
{
    dt.ReadXml(new StringReader(symbols));
}

However when I check the number of rows, the DataTable ends up having zero rows. What am I doing wrong?

Thynne

Possible duplicate of Code for reading an XML file into a DataTable – Halberd

Answer 1

From here: http://www.dreamincode.net/code/snippet3186.htm

// <summary>
/// method for reading an XML file into a DataTable
/// </summary>
/// <param name="file">name (and path) of the XML file</param>
/// <returns></returns>
public DataTable ReadXML(string file)
{
    //create the DataTable that will hold the data
    DataTable table = new DataTable("XmlData");
    try
    {
        //open the file using a Stream
        using(Stream stream = new  FileStream(file, FileMode.Open, FileAccess.Read))
        {
            //create the table with the appropriate column names
            table.Columns.Add("Name", typeof(string));
            table.Columns.Add("Power", typeof(int));
            table.Columns.Add("Location", typeof(string));

            //use ReadXml to read the XML stream
            table.ReadXml(stream);

            //return the results
            return table;
        }                
    }
    catch (Exception ex)
    {
        return table;
    }
}

You might want to take a look at DataTable.ReadXml method.

EDIT: If you have xml object in memory you can use the ReadXml method directly. DataTable.ReadXml(MemoryStream Object);

EDIT 2: I did the export. The following XML Schema is required:

<?xml version="1.0" standalone="yes"?>
<DocumentElement>
  <symbols>
    <symbol>EURCHF</symbol>
  </symbols>
  <symbols>
    <symbol>EURGBP</symbol>
  </symbols>
  <symbols>
    <symbol>EURJPY</symbol>
  </symbols>
</DocumentElement>

Amalamalbena

That's what I'm doing. My question is why does my table have zero rows? What's wrong with my code? – Thynne

Most probably a schema problem.Try creating a similar datatable and call WriteXML. Then check what is written and compare to your xml. This should clear the doubt why datatable is empty. – Amalamalbena

Thanks - that helped me solve it. I was writing it in a different way and I'm now using DataTable.WriteXml and it works great. – Thynne

Answer 2

I've been searching for a easy way to do the same for some time too, but never really found what I actually wanted. Here's one solution I came across. It works, but I don't really like it as I first have to write the file into a DataSet and then put the created DataSet-Table into a DataTable.

Anyway, here's the code:

DataSet ds = new DataSet();
ds.ReadXml(path);
DataTable newDataTable = ds.Tables[0];

I also tried .ReadXml on my DataTable but that always threw an Exception.

I'm not happy with this solution, but it at least works.

Ninefold

Answer 3

Like this:

Dim strXmlString As String = "<tables><row><table_name>Table1</table_name><record_key>1</record_key></row>"
strXmlString += "<row><table_name>Table2</table_name><record_key>2</record_key></row></tables>"
Dim srXMLtext As System.IO.StringReader = New System.IO.StringReader(strXmlString)

Dim dt As New DataTable
dt.ReadXml(srXMLtext)

Automaton

Note: you'll still need to explicitly add the schema to the DataTable (which is missing in the example above.) – Requisition

Answer 4

Another way:

public DataTable ReadXML(string yourPath)
        {
            DataTable table = new DataTable("Item");
            try
            {
                DataSet lstNode = new DataSet();
                lstNode.ReadXml(yourPath);
                table = lstNode.Tables["Item"];
                return table;
            }
            catch (Exception ex)
            {
                return table;
            }
        }

And here's XML format:

<?xml version="1.0" encoding="utf-8" ?>
<db>
  <Item>
    <Id>222</Id>
    <OldCode>ZA</OldCode>
    <NewCode>ZAF</NewCode>
    <Name>Africa (South )</Name>
  </Item>
</db>

Viridissa

Answer 5

Use dataset in place of datatable

Maure

in fact, the dataSET solved my problem in Powershell. See my sample below. – Reface

Answer 6

Here is a sample-code in Powershell:

$xmlString = @"
<table1>
    <row1>
        <c1>value1</c1><c2>value2</c2>
    </row1>
    <row2>
        <c1>value3</c1><c2>value4</c2>
    </row2>
</table1>
"@
$sr =[System.IO.StringReader]($xmlString)

$dataset =[System.Data.DataSet]::new()
$null = $dataset.ReadXml($sr)

and this is the result of $dataset.tables:

c1     c2    
--     --    
value1 value2
value3 value4

Reface

Answer 7

Dynamic Xml to DataTable

public DataTable XMLToDataTable(string YourFilePath)
{
        DataTable table = new DataTable("XMLTABLE");
        try
        {
            #region &quot;&apos;< &lt;> &gt;&amp NOT VALID EXTENSTION IN XML
            var xmlContent = File.ReadAllText(YourFilePath);
            XmlDocument xDoc = new XmlDocument();
            xDoc.LoadXml(xmlContent.Replace("'", "&apos;").Replace("&", "&amp;"));
            xDoc.Save(YourFilePath);
            #endregion
            //All XML Document Content
            //XmlElement root = xDoc.DocumentElement;
            string RootNode = xDoc.DocumentElement.Name;
            string RootChildNode = xDoc.DocumentElement.LastChild.Name;
            DataSet lstNode = new DataSet();
            lstNode.ReadXml(YourFilePath);
            table = lstNode.Tables[RootChildNode];
            return table;
        }
        catch (Exception ex)
        {
            
        }
}

Whittle

This may be a correct answer, but it’d be useful to provide additional explanation of your code so developers can understand your reasoning. This is especially important when responding to old questions with several existing answers, as it should be clear why yours is different from the existing proposals. Would you mind updating your comment with additional details? – Dangerous

Answer 8

I'm a fan of simple, straighforward code. Could skip the finally section since both reader and stream are in a using statement.

public static DataTable XMLToDataTable(string str, string Dt_Name)
{
   DataSet xmlDs = new DataSet("ds");
   StringReader stream = null;
   XmlTextReader reader = null;
   try
   {
       using (stream = new StringReader(str))
       {
           using (reader = new XmlTextReader(stream))
           {
               xmlDs.ReadXml(reader);
               return xmlDs.Tables[Dt_Name];
           }
       }
   }
   catch (Exception ex)
   {
       //Get Exception
   }
   finally
   {
       reader?.Close();
       stream?.Close();
   }
   return null;
}

Ratsbane