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?

Possible duplicate of [Code for reading an XML file into a DataTable](http://stackoverflow.com/questions/9781796/code-for-reading-an-xml-file-into-a-datatable) — Ryan Gates, Dec 03 '15 at 17:35

A G · Accepted Answer · 2010-12-16T14:11:34.603

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>

That's what I'm doing. My question is why does my table have zero rows? What's wrong with my code? — Mark Allison, Dec 16 '10 at 13:41
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. — A G, Dec 16 '10 at 14:03
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. — Mark Allison, Dec 16 '10 at 14:23

baltermia · Answer 2 · 2021-03-16T09:59:36.240

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.

score 2 · Answer 3 · answered Dec 16 '10 at 13:36

2

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)

answered Dec 16 '10 at 13:36

Stefan Steiger

78,642
66
377
442

1

Note: you'll still need to explicitly add the schema to the DataTable (which is missing in the example above.) – Daniel James Bryars Mar 08 '13 at 07:33

score 1 · Answer 4 · answered Oct 02 '17 at 02:21

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>

score 0 · Answer 5 · answered Dec 16 '15 at 14:42

0

Use dataset in place of datatable

answered Dec 16 '15 at 14:42

nosa

21

in fact, the dataSET solved my problem in Powershell. See my sample below. – Carsten Sep 19 '19 at 09:41

score 0 · Answer 6 · answered Sep 19 '19 at 09:48

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

score 0 · Answer 7 · edited Jun 20 '20 at 09:12

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)
        {
            
        }
}

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? — Jeremy Caney, May 12 '20 at 05:22

How to read XML into a DataTable?

7 Answers7

Dynamic Xml to DataTable

Linked