Simpler way of loading XML into data table with columns declared before rows

Question

Typically XML that is easy to turn into datatable (using DataTable.ReadXML) is provided in format

<row ID="2185972">
   <Unit_Nbr>TGHU3274759</Unit_Nbr>
   <T-State>Inbound</T-State>
   <Category>Import </Category>
   <Seal/>
</row>
<row2 Id="row2ID">
    <col1>...

I have one arriving as a HttpResponseMessage in this format:

<query-response>
    <data-table filter="IA_LOOKUP" count="1">
        <columns>
            <column>Unit Nbr</column>
            <column>T-State</column>
            <column>Category</column>
            <column>Seal</column>
            ...
        </columns>
        <rows>
            <row primary-key="2185972">
                <field>TGHU3274759</field>
                <field>Inbound</field>
                <field>Import</field>
                <field/>
                ...
            </row>
             ...
        </rows>
    </data-table>
</query-response>

That I am trying to turn into a data source for a gridview in the format

Unit Nbr     T-State    Category  Seal
TGHU3274759  Inbound    Import     

It is getting bit more complicated than I've anticipated, probably because I have so much problem with transforming this XML (complete XML noob here....)

After reading a bit about XMLReader I've came with this code:

        DataTable dt = new DataTable();
        string elementName = "";
        bool startelement = false;
        List<string> fields = new List<string>();
        XmlReader rdr = XmlReader.Create(new System.IO.StringReader(result));
        while (rdr.Read())
        {

            if (rdr.NodeType == XmlNodeType.Element)
            {

                elementName = rdr.LocalName;
                if (startelement && rdr.LocalName == "field")
                    fields.Add("");  //To add empty element when row has no value
                startelement = true;

            }
            if (rdr.NodeType == XmlNodeType.Text && elementName == "column")
            {
                //Here I add columns to the datatable
                dt.Columns.Add(rdr.Value);

            }
            if (rdr.NodeType == XmlNodeType.Text && elementName == "field")
            {
                //Here I add the row elements to the list
                fields.Add(rdr.Value);

            }

            if (rdr.NodeType == XmlNodeType.EndElement && rdr.LocalName == "row")
            {
               //Here I put the list to the datatable as a new row
                    if (elementName != "error")
                    {
                        var shifArray = new string[fields.ToArray().Length];
                        if (shifArray.Length > 0)
                        {
                            Array.Copy(fields.ToArray(), 1, shifArray, 0, fields.ToArray().Length - 1);
                            Array.Resize(ref shifArray, shifArray.Length - 1);
                            dt.Rows.Add(shifArray);
                        }

                    }
                fields.Clear();
            }
            if (rdr.NodeType == XmlNodeType.EndElement)
            {

                startelement = false;
            }
        }

But it is a quite patchy way to create the table, since I am reading XML element by element, I have to keep an eye for empty rows in the "field" section and in the end I have to shift the array to match rows to the column.

Is there a simpler way?

Answer 1

The XmlReader class has many useful methods.

Try this way:

var dt = new DataTable();

using (var reader = XmlReader.Create(filename))
{
    reader.ReadToFollowing("column");
    do
    {
        dt.Columns.Add(reader.ReadElementContentAsString());
    } while (reader.ReadToNextSibling("column"));

    reader.ReadToFollowing("row");
    do
    {
        var fields = new List<string>();

        reader.ReadToFollowing("field");
        do
        {
            fields.Add(reader.ReadElementContentAsString());
        } while (reader.ReadToNextSibling("field"));

        dt.Rows.Add(fields.ToArray());
    } while (reader.ReadToNextSibling("row"));
}