c# - Loading XML String into datatable

Question

           string parseData= "<TEST xmlns:dt="urn:schemas-microsoft-com:datatypes"><A dt:dt="string">10</A><B dt:dt="string">20</B></TEST>";

            DataSet ds = new DataSet("Whatev");

            TextReader txtReader = new StringReader(parseData);
            XmlReader reader = new XmlTextReader(txtReader);
            ds.ReadXml(reader);
            DataTable ad = new DataTable("newT");
            ad.ReadXml(reader);

For this I am getting empty table in ad, and three tables in ds. What I am expecting is, one table with two columns A and B, and one row with values 10 and 20.

What am I doing wrong?

Answer 1

You're simply not using it correctly. There's a number of problems:

1. You are reading from the same stream twice. In the first pass, the stream pointer is at the end of the stream. You attempt to read from it again when it's already at the end so nothing else is read. Either read into your data set or into your data table, not both. Or, at least seek back to the beginning of the stream if you really want to.

2. Your XML is not in the correct format. It has to be in the format:

   <SomeRoot>
       <TableName>
           <ColumnName>ColumnValue</ColumnName>
       </TableName>
       <TableName>
           <ColumnName>AnotherColumnValue</ColumnName>
       </TableName>
   </SomeRoot>
   

   You won't be able to use that method with any arbitrary XML.

3. Your tables do not have a schema set. You need to either read in a schema or set it up beforehand.

   var table = new DataTable("TEST");
   table.Columns.Add("A", typeof(string));
   table.Columns.Add("B", typeof(string));
   table.ReadXml(xmlReader);
   

Try this instead:

var xmlStr = @"<Root>
    <TEST>
        <A>10</A>
        <B>20</B>
    </TEST>
</Root>";
var table = new DataTable("TEST");
table.Columns.Add("A", typeof(string));
table.Columns.Add("B", typeof(string));
table.ReadXml(new StringReader(xmlStr));

---

If you decide to parse that XML yourself, LINQ can help you out here.

public static DataTable AsDataTable(XElement root, string tableName, IDictionary<string, Type> typeMapping)
{
    var table = new DataTable(tableName);

    // set up the schema based on the first row
    XNamespace dt = "urn:schemas-microsoft-com:datatypes";
    var columns =
       (from e in root.Element(tableName).Elements()
        let typeName = (string)e.Element(dt + "dt")
        let type = typeMapping.ContainsKey(typeName ?? "") ? typeMapping[typeName] : typeof(string)
        select new DataColumn(e.Name.LocalName, type)).ToArray();
    table.Columns.AddRange(columns);

    // add the rows
    foreach (var rowElement in root.Elements(tableName))
    {
        var row = table.NewRow();
        foreach (var column in columns)
        {
            var colElement = rowElement.Element(column.ColumnName);
            if (colElement != null)
                row[column.ColumnName] = Convert.ChangeType((string)colElement, column.DataType);
        }
        table.Rows.Add(row);
    }
    return table;
}

Then to use it:

var xmlStr = @"<Root>
    <TEST xmlns:dt=""urn:schemas-microsoft-com:datatypes"">
        <A dt:dt=""string"">10</A>
        <B dt:dt=""string"">20</B>
    </TEST>
</Root>";
var root = XElement.Parse(xmlStr);
var mapping = new Dictionary<string, Type>
{
    { "string", typeof(string) },
};
var table = AsDataTable(root, "TEST", mapping);

There probably is a better way to get the associated .NET type for a datatype but I don't know how to do that at the moment, I'll update if I find that out.