C# DataSet XML loading and saving

Question

I've got XML that describes certain data (a template) that I want to be able to edit. I load XML into DataSet (see fig. 1 below), plug DataSet tables into DataGridView (switch between them using a separate comboBox), make changes and then save XML (simple DataSet.WriteXML directive). The XML I read looks very nice and humanly readable (see fig. 2 below), however, the written XML is nowhere near the original (see fig. 3 below).

My goal is to allow editing of XML document and preserve it in the same form on save.

What am I doing wrong? Code/XML blocks are below.

fig.1 - Reading XML into DataSet:

using (XmlReader xrMeta = XmlReader.Create(new StreamReader(ofdOpenXML.FileName)))
     {
       while (!xrMeta.EOF)
         {
           xrMeta.ReadToFollowing("record");
           if (xrMeta.NodeType == XmlNodeType.Element)
           {
             xrMeta.ReadToFollowing("fields");

             xrSub = xrMeta.ReadSubtree();
             dt = new DataTable();

             ds = new DataSet();

             ds.ReadXml(xrSub);
             dt = ds.Tables[0].Copy();

             dt.TableName = "recordTypeId " + iTableNumber.ToString().PadLeft(2, '0');
             MetaXML.Tables.Add(dt);
             iTableNumber++;
            }

         }

         dgvMetaXML.DataSource = MetaXML.Tables[0];

fig.2 - Input XML:

<?xml version='1.0'?>
<records>
  <record>
    <recordTypeId>01</recordTypeId>
    <fields>
      <field>
        <fieldNID>entityID</fieldNID>
        <fieldID>1</fieldID>
        <fieldName>Entity ID</fieldName>
        <fieldStartPos>1</fieldStartPos>
        <fieldEndPos>6</fieldEndPos>
        <fieldLength>6</fieldLength>
        <fieldType>Alpha</fieldType>
        <fieldRequired>Y</fieldRequired>
        <fieldDefaultValue></fieldDefaultValue>
      </field>
      <field>
        <fieldNID>reserved0101</fieldNID>
        <fieldID>2</fieldID>
        <fieldName>Reserved</fieldName>
        <fieldStartPos>7</fieldStartPos>
        <fieldEndPos>8</fieldEndPos>
        <fieldLength>2</fieldLength>
        <fieldType>Alpha</fieldType>
        <fieldRequired>Y</fieldRequired>
        <fieldDefaultValue>  </fieldDefaultValue>
      </field>
      <field>
        <fieldNID>deviceID</fieldNID>
        <fieldID>3</fieldID>
        <fieldName>Device ID</fieldName>
        <fieldStartPos>9</fieldStartPos>
        <fieldEndPos>23</fieldEndPos>
        <fieldLength>15</fieldLength>
        <fieldType>Alpha</fieldType>
        <fieldRequired>Y</fieldRequired>
        <fieldDefaultValue></fieldDefaultValue>
      </field>
    </fields>
  </record>
  <record>
    <recordTypeId>02</recordTypeId>
    <fields>
      <field>
        <fieldNID>userID</fieldNID>
        <fieldID>1</fieldID>
        <fieldName>User ID</fieldName>
        <fieldStartPos>1</fieldStartPos>
        <fieldEndPos>6</fieldEndPos>
        <fieldLength>6</fieldLength>
        <fieldType>Alpha</fieldType>
        <fieldRequired>Y</fieldRequired>
        <fieldDefaultValue></fieldDefaultValue>
      </field>
      <field>
        <fieldNID>reserved0201</fieldNID>
        <fieldID>2</fieldID>
        <fieldName>Reserved</fieldName>
        <fieldStartPos>7</fieldStartPos>
        <fieldEndPos>8</fieldEndPos>
        <fieldLength>2</fieldLength>
        <fieldType>Alpha</fieldType>
        <fieldRequired>Y</fieldRequired>
        <fieldDefaultValue>  </fieldDefaultValue>
      </field>
      <field>
        <fieldNID>testField</fieldNID>
        <fieldID>3</fieldID>
        <fieldName>Test Sequence</fieldName>
        <fieldStartPos>9</fieldStartPos>
        <fieldEndPos>23</fieldEndPos>
        <fieldLength>15</fieldLength>
        <fieldType>Alpha</fieldType>
        <fieldRequired>Y</fieldRequired>
        <fieldDefaultValue></fieldDefaultValue>
      </field>
    </fields>
  </record>
</records>

fig.3 - output XML:

<records>
  <recordTypeId_x0020_01>
    <fieldNID>entityID</fieldNID>
    <fieldID>1</fieldID>
    <fieldName>Entity ID</fieldName>
    <fieldStartPos>1</fieldStartPos>
    <fieldEndPos>6</fieldEndPos>
    <fieldLength>6</fieldLength>
    <fieldType>Alpha</fieldType>
    <fieldRequired>Y</fieldRequired>
    <fieldDefaultValue />
  </recordTypeId_x0020_01>
  <recordTypeId_x0020_01>
    <fieldNID>reserved0101</fieldNID>
    <fieldID>2</fieldID>
    <fieldName>Reserved</fieldName>
    <fieldStartPos>7</fieldStartPos>
    <fieldEndPos>8</fieldEndPos>
    <fieldLength>2</fieldLength>
    <fieldType>Alpha</fieldType>
    <fieldRequired>Y</fieldRequired>
    <fieldDefaultValue />
  </recordTypeId_x0020_01>
  <recordTypeId_x0020_01>
    <fieldNID>deviceID</fieldNID>
    <fieldID>3</fieldID>
    <fieldName>Device ID</fieldName>
    <fieldStartPos>9</fieldStartPos>
    <fieldEndPos>23</fieldEndPos>
    <fieldLength>15</fieldLength>
    <fieldType>Alpha</fieldType>
    <fieldRequired>Y</fieldRequired>
    <fieldDefaultValue />
  </recordTypeId_x0020_01>
  <recordTypeId_x0020_02>
    <fieldNID>userID</fieldNID>
    <fieldID>1</fieldID>
    <fieldName>User ID</fieldName>
    <fieldStartPos>1</fieldStartPos>
    <fieldEndPos>6</fieldEndPos>
    <fieldLength>6</fieldLength>
    <fieldType>Alpha</fieldType>
    <fieldRequired>Y</fieldRequired>
    <fieldDefaultValue />
  </recordTypeId_x0020_02>
  <recordTypeId_x0020_02>
    <fieldNID>reserved0201</fieldNID>
    <fieldID>2</fieldID>
    <fieldName>Reserved</fieldName>
    <fieldStartPos>7</fieldStartPos>
    <fieldEndPos>8</fieldEndPos>
    <fieldLength>2</fieldLength>
    <fieldType>Alpha</fieldType>
    <fieldRequired>Y</fieldRequired>
    <fieldDefaultValue />
  </recordTypeId_x0020_02>
  <recordTypeId_x0020_02>
    <fieldNID>testField</fieldNID>
    <fieldID>3</fieldID>
    <fieldName>Test Sequence</fieldName>
    <fieldStartPos>9</fieldStartPos>
    <fieldEndPos>23</fieldEndPos>
    <fieldLength>15</fieldLength>
    <fieldType>Alpha</fieldType>
    <fieldRequired>Y</fieldRequired>
    <fieldDefaultValue />
  </recordTypeId_x0020_02>
</records>

Answer 1

 > The XML I read looks very nice and humanly readable (see fig. 2 below), however, 
 > the written XML is nowhere near the original (see fig. 3 below).
 > What am I doing wrong? 

the dotnet dataset can only write the xml-format of its internal representation. this representation is similar to

  <datasetName>
     <dataTableName OtherFieldName='value'>
         <FieldName>value</FieldName>
     </dataTableName>
  </datasetName>

So fields are elements or attributs. Your xml structre is more complex. The dataset tries to interprete your data and puts the data into its internal structure, if possible. In your example the information recordTypeId is lost.

I had a similar problem and created a my own xml-post-processer that reformats the xml-output to my own xml-format that dataset can read but not write.

Answer 2

Ended up going with k3b's approach (sorry, can't upvote - need more reputation).

Here's the updated code to read the XML into DataSet (keep in mind, it's just a mock code to make things work for the first time. You should revise it to be more efficient and ultimately make more sense):

int iTableNumber = 1;

// Read input XML
using (XmlReader xrMeta = XmlReader.Create(new StreamReader(ofdOpenXML.FileName)))
{
   while (!xrMeta.EOF)
   {
     // Advance to next <record>
     xrMeta.ReadToFollowing("record");
     if (xrMeta.NodeType == XmlNodeType.Element)
     {
        // Advance to the next <fields>
        xrMeta.ReadToFollowing("fields");

        // Read underlying XML - it will be a set of flat tables
        xrSub = xrMeta.ReadSubtree();
        dt = new DataTable();

        ds = new DataSet("fields");

        ds.ReadXml(xrSub);
        dt = ds.Tables[0].Copy();

        dt.TableName = "field_" + iTableNumber.ToString().PadLeft(2, '0');
        MetaXML.Tables.Add(dt);
        iTableNumber++;
      }
   }
 }

 // Populate comboBox to switch between tables in DataSet
 for (int i = 0; i < MetaXML.Tables.Count; i++)
 {
    cbShowTable.Items.Add(MetaXML.Tables[i].TableName);
 }

 // Populate DataGridView with first read table
 dataGridViewMetaXML.DataSource = MetaXML.Tables[0];

Saving XML now looks like this:

        // This is our output XML file
        // Technically, it should have been the same name as the input one
        // but for the purposes of testing it isn't
        StreamWriter srFile = new StreamWriter((@"testingOutputXML.xml"));
        StringWriter stWriter;
        StringBuilder sbXML = new StringBuilder();

        // Headers to play nice
        sbXML.AppendLine("<?xml version='1.0'?>");
        sbXML.AppendLine("<records>");
        DataTable dt;
        for (int i = 0; i < MetaXML.Tables.Count; i++)
        {
            // This is where we have to recreate the structure manually
            sbXML.AppendLine("<record>");
            sbXML.Append("<recordTypeId>");
            sbXML.Append((1+ i).ToString().PadLeft(2,'0'));
            sbXML.AppendLine("</recordTypeId>");
            dt = new DataTable();
            dt = MetaXML.Tables[i].Copy();
            dt.TableName = "field";

            stWriter = new StringWriter();
            dt.WriteXml(stWriter, false);              
            stWriter.WriteLine();

            sbXML.Append(stWriter.GetStringBuilder());

            // Need to clean up because DataTable's WriteXML() method
            // wraps the data in <DocumentElement> and </DocumentElement> tags
            sbXML.Replace("DocumentElement", "fields");
            sbXML.AppendLine("</record>");
        }
        sbXML.AppendLine("</records>");
        srFile.Write(sbXML.ToString());
        srFile.Flush();
        srFile.Close();

        MessageBox.Show("Done!");

Thanks everyone who chipped in with the answers, it steered me to the right track.

Answer 3

Your code is reading to the next fields entry with each iteration using

xrMeta.ReadToFollowing("fields");

You're then renaming the base table from fields to recordTypeId XX with

dt.TableName = "recordTypeId " + iTableNumber.ToString().PadLeft(2, '0');`

and the space is being encoded to _x0020_ to avoid breaking up the tag.

You then add this renamed instance of fields back to the root with

MetaXML.Tables.Add(dt);

The output is a result of this.

What different result were you trying to achieve?