Convert XML to Dataset or Excel Tables

Question

I have the following xml file

    <rootTag Type="TheRootType" CollectionTime="2016-02-06T" secretNumber="12345">
  <Toyota Type="Car">
    <item Name="EToyotaID" Valuetype="System.Int32">1</item>
    <item Name="EIdentifier" Valuetype="System.String">KKPP</item>
    <item Name="THEID" Valuetype="System.Int32">0</item>
    <item Name="TheValue" Valuetype="System.Int32">5040</item>
    <item Name="Num" Valuetype="System.String">1104</item>
  </Toyota>
  <Toyota Type="Car">
    <item Name="EToyotaID" Valuetype="System.Int32">2</item>
    <item Name="EIdentifier" Valuetype="System.String">PPLL</item>
    <item Name="THEID" Valuetype="System.Int32">0</item>
    <item Name="TheValue" Valuetype="System.Int32">3230</item>
    <item Name="Num" Valuetype="System.String">1104</item>
  </Toyota>
  <Mazda Type="Car">
    <item Name="EMazdaID" Valuetype="System.Int32">1130</item>
    <item Name="EIdentifier" Valuetype="System.String">RRR</item>
    <item Name="TheValue" Valuetype="System.Int32">10</item>
    <item Name="Num" Valuetype="System.String">1104</item>
  </Mazda>
  <Mazda Type="Car">
    <item Name="EMazdaID" Valuetype="System.Int32">1131</item>
    <item Name="EIdentifier" Valuetype="System.String">SetTemp</item>
    <item Name="TheValue" Valuetype="System.Int32">7</item>
    <item Name="Num" Valuetype="System.String">1104</item>
  </Mazda>
  <Honda Type="Car">
    <item Name="EHondaID" Valuetype="System.Int32">1726</item>
    <item Name="EIdentifier" Valuetype="System.String">SetUp</item>
    <item Name="THEID" Valuetype="System.Int32">11</item>
    <item Name="IDNum" Valuetype="System.Int32">13</item>
    <item Name="TheValue" Valuetype="System.Int32">327</item>
    <item Name="Num" Valuetype="System.String">1104</item>
  </Honda>
  <Honda Type="Car">
    <item Name="EHondaID" Valuetype="System.Int32">1727</item>
    <item Name="EIdentifier" Valuetype="System.String">SetUp</item>
    <item Name="THEID" Valuetype="System.Int32">11</item>
    <item Name="IDNum" Valuetype="System.Int32">14</item>
    <item Name="TheValue" Valuetype="System.Int32">327</item>
    <item Name="Num" Valuetype="System.String">1104</item>
  </Honda>
</rootTag>

I would like to write this to Excel file where I have three tabs at the bottom. each for each car, so one for Toyota, one for Mazda, and one for Honda. In each tab I would like to have a table with the data for each car since all the cars with same name will have the same number of attributes.a I have tried to get a dataset from the xml but that seems not to work correctly. in the dataset I got the following 5 tables

1 ) rootTag
2 ) Toyota <= only has 2 columns "car" and "0" 
3 ) item  <== it has all the data I need but it is a mess
4 ) Mazda <= only has 2 columns "car" and "0" 
5 ) Honda <= only has 2 columns "car" and "0" 
here is the code I used 






public static DataSet ConvertXMLToDataSet(string xmlData)
    {
        StringReader stream = null;
        XmlTextReader reader = null;
        try
        {
            DataSet xmlDS = new DataSet("rootTag");
            stream = new StringReader(xmlData);
            // Load the XmlTextReader from the stream
            reader = new XmlTextReader(stream);
            xmlDS.ReadXml(reader);
            return xmlDS;
        }
        catch
        {
            return null;
        }
        finally
        {
            if (reader != null) reader.Close();
        }
    }// Use 


}

I also have the XML Schema but I am not sure how to make use of that in my case. thanks

Answer 1

Try this code:

var ds = new DataSet();
var xml = XElement.Load("test.xml");

var cars = xml.Elements().GroupBy(elem => elem.Name, elem => elem.Elements());

foreach (var car in cars)
{
    var dt = new DataTable(car.Key.LocalName);

    foreach (var elem in car.First())
    {
        dt.Columns.Add(elem.Attribute("Name").Value,
            Type.GetType(elem.Attribute("Valuetype").Value));
    }

    foreach (var elem in car)
    {
        dt.Rows.Add(elem.Select(x => x.Value).ToArray());
    }

    ds.Tables.Add(dt);
}

It gives you a filled dataset.

Answer 2

I use datasets to export (and load) XML files for such things like config files. The loading code looks OK, except I use StreamReader instead of StringReader. How was your XML created? The XML you posted won't import into a dataset/table. I suggest you loop though the XML nodes and add the values into a pre-built dataset. Then you can use the code below to create the correct schema.

    dsRelease _Data = null; // Dataset.

    private void Load_Data()
    {
        StreamReader sr;
        XmlReader reader;

        _Data = new dsRelease(); //new dataset

        sr = new StreamReader(_SavePath, Encoding.UTF8);
        reader = new XmlTextReader(sr);

        _Data.ReadXml(reader, XmlReadMode.ReadSchema);

        if (reader != null) reader.Close();
        if (sr != null) sr.Close();
    }

    public void Save_Data(string savepath)
    {
        XmlTextWriter writer = null;

        writer = new XmlTextWriter(savepath, Encoding.UTF8);

        //Write Dataset.
        _Data.WriteXml(writer, XmlWriteMode.WriteSchema);
    }