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:[ bad table1

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

You are wrong. The XML you posted is not from the dataset Writexml method. Take your DataSet set and write to a file. The XML produced has roo tag the name of the DataSet. The next Level Tags are the Table names. The next level tags are the rows. And finally the fourth level tags are the column data. I do not know where you got your xml from. — jdweng, Jun 24 '22 at 08:50
Dataset ReadXml and DataSetWrite XML are complimentary. Both have limitsof 4 layers of tags. You have 5 layers. So the ReadXml when you get over 4 levels fragments data into multiple tables and it is impossible to recombine the fragments. — jdweng, Jun 24 '22 at 10:08

score 0 · Accepted Answer · answered Jun 24 '22 at 10:24

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

            }

        }
    }
 
}

Dataset ReadXml returns Rows Instead of Columns

1 Answers1