Dataset ReadXml returns Rows Instead of Columns

Question

I am trying to create a datatable from an xml file, using the dataset readxml method. however i am struggling to define the schema correctly to properly interpret this file.

i think the problem lies with the nesting and the fact that the ColumnUid (which ideally should be the column name) is a value rather than an element.

the datatable it returns at the moment has this structure:[

and i hope to make it return like this:

is this possible by defining the correct schema to pass to readxml... or even at all?

I could potentially transpose the datatable after the initial readxml, using eaither linq or a loop but would prefer to avoid this

the xml is as follows (shortened example):

 <?xml version="1.0" encoding="utf-16"?>
<DataTable Uid="dtDocRows">
 <Rows>
    <Row>
     <Cells>
    <Cell>
      <ColumnUid>DocEntry</ColumnUid>
      <Value>121496</Value>
    </Cell>
    <Cell>
      <ColumnUid>DocNum</ColumnUid>
      <Value>264803</Value>
    </Cell>
    <Cell>
      <ColumnUid>LineNum</ColumnUid>
      <Value>0</Value>
    </Cell>
    <Cell>
      <ColumnUid>ItemCode</ColumnUid>
      <Value>BENIGRAMST55060075L</Value>
    </Cell>       
    <Cell>
      <ColumnUid>Quantity</ColumnUid>
      <Value>1.000000</Value>
    </Cell>   
  </Cells> 
</Row>
<Row>
  <Cells>
    <Cell>
      <ColumnUid>DocEntry</ColumnUid>
      <Value>121658</Value>
    </Cell>
    <Cell>
      <ColumnUid>DocNum</ColumnUid>
      <Value>264965</Value>
    </Cell>
    <Cell>
      <ColumnUid>LineNum</ColumnUid>
      <Value>0</Value>
    </Cell>
    <Cell>
      <ColumnUid>ItemCode</ColumnUid>
      <Value>PYCCHANT202575L</Value>
    </Cell>      
    <Cell>
      <ColumnUid>Quantity</ColumnUid>
      <Value>1.000000</Value>
    </Cell>       
  </Cells>
</Row>

and the c# function to return the datatable is this:

private DataTable getDotNetDataTable()
{
    DataSet ds = new DataSet();
    XDocument xdoc = XDocument.Parse(dtDocRows.SerializeAsXML(SAPbouiCOM.BoDataTableXmlSelect.dxs_DataOnly));
    xdoc.Save(@"C:\1\xml\test.xml");
    ds.ReadXml(@"C:\1\xml\test.xml");
    return ds.Tables.Item(4);

    return dt;
}

Answer 1

Use XML Line :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;
using System.Data;
using System.IO;


namespace ConsoleApplication23
{

    class Program
    {
        const string FILENAME = @"c:\temp\test.xml";
        static void Main(string[] args)
        {
            //remove utc-16
            StreamReader reader = new StreamReader(FILENAME);
            reader.ReadLine();

            XDocument doc = XDocument.Load(reader);

            string[] columnNames = doc.Descendants("ColumnUid").Select(x => (string)x).Distinct().ToArray();

            DataTable dt = new DataTable();
            foreach (string columnName in columnNames)
            {
                dt.Columns.Add(columnName, typeof(string));
            }

            foreach (XElement xRow in doc.Descendants("Row"))
            {
                DataRow dtRow = dt.Rows.Add();

                foreach(XElement cell in xRow.Descendants("Cell"))
                {
                    string colName = (string)cell.Element("ColumnUid");
                    string value  = (string)cell.Element("Value");

                    dtRow[colName] = value;
                }

            }

        }
    }
 
}