CODEWITHSUNDEEP
c#xmldatatable
Mark Allison
Mark Allison

Reputation: 7228

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?

Upvotes: 11

Views: 88187

Answers (8)

user26434999
user26434999

Reputation: 1

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;
}

Upvotes: 0

baltermia
baltermia

Reputation: 1343

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.

Upvotes: 3

Diptesh Satvilkar
Diptesh Satvilkar

Reputation: 1

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

Upvotes: 0

Carsten
Carsten

Reputation: 2191

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

Upvotes: 0

nghiavt
nghiavt

Reputation: 443

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>

Upvotes: 1

nosa
nosa

Reputation: 21

Use dataset in place of datatable

Upvotes: 0

A G
A G

Reputation: 22559

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>

Upvotes: 15

Stefan Steiger
Stefan Steiger

Reputation: 82136

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)

Upvotes: 2

Related Questions