After importing DataSet from XML, new rows are not nested properly

Question

I'm using DataSet.ReadXml() to import an XML file into a new DataSet. Then I'm adding a new row to one of the tables inside the DataSet, and then I want to export that DataSet to XML again. The problem is that the new row is not nested properly and just gets appended to the end of the XML file.

Here is the program:

    using System;
    using System.Data;
    using System.IO;
    using System.Xml;

    public class Program
    {
        public static void Main()
        {
            string xml = @"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>
    <DATAPACKET Version=""2.0"">
        <METADATA>
            <FIELDS>
                <FIELD attrname=""CompanyID"" fieldtype=""string"" WIDTH=""10""/>
                <FIELD attrname=""Description"" fieldtype=""string"" WIDTH=""40""/>
            </FIELDS>
            <PARAMS/>
        </METADATA>
        <ROWDATA>
            <ROW CompanyID=""CC"" Description=""Contoso""/>
        </ROWDATA>
    </DATAPACKET>
    ";
            XmlReader reader = XmlReader.Create(new StringReader(xml));
            DataSet dataSet = new DataSet();
            dataSet.ReadXml(reader, XmlReadMode.InferTypedSchema);
            var rowTable = dataSet.Tables["ROW"];
            var newRow = rowTable.NewRow();
            newRow["CompanyID"] = "APPL";
            newRow["Description"] = "Apple";
            rowTable.Rows.Add(newRow);
            Console.WriteLine(dataSet.GetXml());
        }
    }

And here is the output:

    <DATAPACKET Version="2.0">
      <METADATA>
        <PARAMS />
        <FIELDS>
          <FIELD attrname="CompanyID" fieldtype="string" WIDTH="10" />
          <FIELD attrname="Description" fieldtype="string" WIDTH="40" />
        </FIELDS>
      </METADATA>
      <ROWDATA>
        <ROW CompanyID="CC" Description="Contoso" />
      </ROWDATA>
    </DATAPACKET>
    <ROW CompanyID="APPL" Description="Apple" />

What I want is for the new row to be nested with the other rows from that table like this:

    <DATAPACKET Version="2.0">
      <METADATA>
        <PARAMS />
        <FIELDS>
          <FIELD attrname="CompanyID" fieldtype="string" WIDTH="10" />
          <FIELD attrname="Description" fieldtype="string" WIDTH="40" />
        </FIELDS>
      </METADATA>
      <ROWDATA>
        <ROW CompanyID="CC" Description="Contoso" />
        <ROW CompanyID="APPL" Description="Apple" />
      </ROWDATA>
    </DATAPACKET>

What am I doing wrong? How do I get well formed XML out of DataSet.GetXml() ?

Here is the program running over at dotnetfiddle

Make two changes 1)From : var newRow = rowTable.NewRow(); To : var newRow = rowTable.Rows.Add(); 2)Remove : rowTable.Rows.Add(newRow); — jdweng, Sep 19 '19 at 02:42
I tried the edits you suggested and I got the same result. See the fiddle here for the output: https://dotnetfiddle.net/IjFgtA — DotNetPadawan, Sep 19 '19 at 03:09

DotNetPadawan · Accepted Answer · 2019-09-19T18:06:54.970

I solved my own question. The problem was that I was unaware that the auto generated relationships between the tables created foreign key columns that needed to be populated. For the ROW table, the auto generated foreign key is ROWDATA_Id.

Here is the updated code that works as expected:

using System;
using System.Data;
using System.IO;
using System.Xml;

public class Program
{
    public static void Main()
    {
        string xml = @"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>
<DATAPACKET Version=""2.0"">
    <METADATA>
        <FIELDS>
            <FIELD attrname=""CompanyID"" fieldtype=""string"" WIDTH=""10""/>
            <FIELD attrname=""Description"" fieldtype=""string"" WIDTH=""40""/>
        </FIELDS>
        <PARAMS/>
    </METADATA>
    <ROWDATA>
        <ROW CompanyID=""CC"" Description=""Contoso""/>
    </ROWDATA>
</DATAPACKET>
";
        XmlReader reader = XmlReader.Create(new StringReader(xml));
        DataSet dataSet = new DataSet();
        dataSet.ReadXml(reader, XmlReadMode.InferTypedSchema);
        var rowTable = dataSet.Tables["ROW"];
        var newRow = rowTable.NewRow();
        newRow["CompanyID"] = "APPL";
        newRow["Description"] = "Apple";
        newRow["ROWDATA_Id"] = 0; //This is what I was missing. This nests the row properly
        rowTable.Rows.Add(newRow);
        Console.WriteLine(dataSet.GetXml());
    }
}

An alternate solution is to set the DataColumn.DefaultValue to 0 for the foreign key column ROWDATA_Id

var rowTable = dataSet.Tables["ROW"];
rowTable.Columns["ROWDATA_Id"].DefaultValue = 0;

Here is the output for both solutions:

<DATAPACKET Version="2.0">
  <METADATA>
    <PARAMS />
    <FIELDS>
      <FIELD attrname="CompanyID" fieldtype="string" WIDTH="10" />
      <FIELD attrname="Description" fieldtype="string" WIDTH="40" />
    </FIELDS>
  </METADATA>
  <ROWDATA>
    <ROW CompanyID="CC" Description="Contoso" />
    <ROW CompanyID="APPL" Description="Apple" />
  </ROWDATA>
</DATAPACKET>

Here is the first solution running on dotnetfiddle

Here is the alternate solution running on dotnetfiddle

score 0 · Answer 2 · answered Sep 19 '19 at 04:40

The ReadXml fragments your xml into a lot of tables. ReadXml does following with following nested tags 1) DataSet name 2) DataTable name 3) Row Data : Column name is tag and innertext is value

See my code below which parses xml with xml linq :

using System;
using System.Data;
using System.IO;
using System.Xml;
using System.Xml.Linq;
namespace ConsoleApplication1
{
    class Program
    {
        public static void Main()
        {
            string xml = @"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>
    <DATAPACKET Version=""2.0"">
        <METADATA>
            <FIELDS>
                <FIELD attrname=""CompanyID"" fieldtype=""string"" WIDTH=""10""/>
                <FIELD attrname=""Description"" fieldtype=""string"" WIDTH=""40""/>
            </FIELDS>
            <PARAMS/>
        </METADATA>
        <ROWDATA>
            <ROW CompanyID=""CC"" Description=""Contoso""/>
        </ROWDATA>
    </DATAPACKET>
    ";

            XmlReader reader = XmlReader.Create(new StringReader(xml));
            DataSet dataSet = new DataSet();
            dataSet.ReadXml(reader, XmlReadMode.InferTypedSchema);
            var rowTable = dataSet.Tables["ROW"];
            var newRow = rowTable.NewRow();
            newRow["CompanyID"] = "APPL";
            newRow["Description"] = "Apple";
            rowTable.Rows.Add(newRow);
            Console.WriteLine(dataSet.GetXml());


            XDocument doc = XDocument.Parse(xml);

            DataTable rowTable2 = new DataTable("Table1");
            DataRow newRow2 = null;
            foreach (XElement field in doc.Descendants("FIELD"))
            {
                string t = (string)field.Attribute("fieldtype");
                Type _type = null;
                switch (t)
                {
                    case "string" :
                        _type = typeof(string);
                        break;
                }

                rowTable2.Columns.Add((string)field.Attribute("attrname"), _type);
            }
            foreach (XElement row in doc.Descendants("ROW"))
            {
                newRow = rowTable2.Rows.Add();
                foreach (XAttribute attribute in row.Attributes())
                {
                    newRow[attribute.Name.LocalName] = (string)attribute;
                }
            }
            newRow = rowTable2.Rows.Add();
            newRow["CompanyID"] = "APPL";
            newRow["Description"] = "Apple";
            DataSet ds = new DataSet();
            ds.Tables.Add(rowTable2);
            Console.WriteLine(ds.GetXml());
        }
    }
}

Racil Hilan · Answer 3 · 2019-09-19T12:47:50.307

Where did you get that XML from? It is formatted in a way not supported by the DataSet. When nesting tables, you must define a parent-child relationship between the tables and you must set the Nested property of the child table to true. In your XML, the DataSet has no idea which parent the new child row belongs to, so it appends it to the end.

You can read in the MSDN about Nesting DataRelations.

Having said that, your XML doesn't actually have parent and child tables. It has METADATA and ROWDATA. Like I said, that format is not supported by DataSet, you will have to move your meta data to a Schema (XSD). You can read in the MSDN about Deriving DataSet Relational Structure from XML Schema.

Here is an example of how you car represent your data in question with XSD and XML:

using System;
using System.Data;
using System.IO;
using System.Xml;

public class Program
{
    public static void Main()
    {
        string xml = @"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>
<MyDataSet>
    <Companies>
        <CompanyID>CC</CompanyID>
        <Description>Contoso</Description>
    </Companies>
</MyDataSet>
";
        string xsd = @"<?xml version=""1.0"" encoding=""utf-8""?>
            <xs:schema id=""SomeID""
            xmlns=""""
            xmlns:xs=""http://www.w3.org/2001/XMLSchema""
            xmlns:msdata=""urn:schemas-microsoft-com:xml-msdata"">
   <xs:element name=""MyDataSet"" msdata:IsDataSet=""true"">
     <xs:complexType>
       <xs:choice minOccurs=""0"" maxOccurs=""unbounded"">
         <xs:element name=""Companies"">
           <xs:complexType >
             <xs:sequence>
               <xs:element name=""CompanyID"" type=""xs:string"" minOccurs=""0"" />
               <xs:element name=""Description"" type=""xs:string"" minOccurs=""0"" />  
             </xs:sequence>
           </xs:complexType>
          </xs:element>
       </xs:choice>
     </xs:complexType>
   </xs:element>
 </xs:schema>
";
        DataSet dataSet = new DataSet();
        StringReader sr = new StringReader(xsd);
        dataSet.ReadXmlSchema(sr);
        sr = new StringReader(xml);
        dataSet.ReadXml(sr, XmlReadMode.InferTypedSchema);
        var rowTable = dataSet.Tables["Companies"];
        var newRow = rowTable.NewRow();
        newRow["CompanyID"] = "APPL";
        newRow["Description"] = "Apple";
        rowTable.Rows.Add(newRow);
        Console.WriteLine(dataSet.GetXml());
    }
}

In this case, you don't really need a Schema because you only have one table with all columns as string. So if you remove the Schema from the code above and run it again, you'll get the exact same results. However, this gives you an idea on how to define your DataSet structure using a Schema, so you can add more complex tables with relationship between them. For simple tables with no relationships, you don't need a Schema.

Unfortunately, I don't have any control over the format of the incoming XML file. So trying to modify the schema is not going to help. When using `DataSet.ReadXml()` with `XmlReadMode.InferTypedSchema` I'm getting a schema that makes sense as the result of `DataSet.GetXmlSchema()` which means it's constructing the DataSet with the right parent child relationships. See the fiddle for the output: https://dotnetfiddle.net/Widget/2az4kc — DotNetPadawan, Sep 19 '19 at 16:16
No, you're not getting a "schema that makes sense". You are not understanding the schema correctly. That's why you're surprised why the new record was added at the end. What you're getting is 6 meaningless tables. Change the last line to `Console.WriteLine(dataSet.Tables.Count);` and see for yourself. Now does that make sense to you? Those 6 tables are nested in each other with no defined relationship. This XML is not valid for `DataSet`, so if you have no control over it, you will have to use `XDocument` and parse it manually line by line. — Racil Hilan, Sep 19 '19 at 16:40

After importing DataSet from XML, new rows are not nested properly

3 Answers3