Mark Allison Mark Allison - 3 years ago 291
C# Question

How to read XML into a DataTable?

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?

Answer Source

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>
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download