Embedded Tables in XML

Recently, I've been working on the design of new project that will be making extensive use of XML to store data. The project is also going to have to be translated into Chinese and Thai to begin with.

What I like about XML is of course the ability to embed data within data within data ad infinum. Yesterday I started researching the ability of .NET to read and write XML, and I found out that the DataSet object has XMLWrite and XMLRead methods. This made me quite interested. Having never used the DataSet object I read Microsoft's documentation about it which was inconsistent and unclear but understandable.

At first I had a little trouble while trying to get tables to embed properly. I was setting up relationships using parent ID columns and it all looked fine displayed in a DataGrid, but when I saved it to XML, it did not embed the data (which in the end was only one extra line of code to do).

Anyhow, I came across the solution this morning, which was very simple: when creating the DataRelation object, simply set the Nested property to true. Anyway, I thought seeing as I wasted several hours trying to find the answer to this (cursing Google everytime it gave me irrelavent sites), I decided that it would be worthwhile to publish it on this blog. So here is the code (with comments):

      //Declarations
      DataSet myDataSet = new DataSet("root");
      DataTable Ptable = new DataTable("object");
      DataTable Ctable = new DataTable("item");

      //Set up parent table (object)
      Ptable.Columns.Add("ID");
      Ptable.Columns[0].ColumnMapping = MappingType.Hidden; //hidden because I do
      //not want the Parent and Child ID's to show up in the XML, making it messy
      Ptable.Columns.Add("objectdata1",typeof(string));
      Ptable.Columns.Add("objectdata2",typeof(string));

      //Set up child table (item)
      Ctable.Columns.Add("parentID");
      Ctable.Columns[0].ColumnMapping = MappingType.Hidden;
      Ctable.Columns.Add("Age",typeof(int));

      myDataSet.Tables.Add(Ptable);
      myDataSet.Tables.Add(Ctable);

      // set up relation (Relation Name, Parent Column, Child Column)
      DataRelation myRelation = new DataRelation("objectitem",Ptable.Columns[0],Ctable.Columns[0]);
      myRelation.Nested = true; //this makes the tables embedded when written to XML
      myDataSet.Relations.Add(myRelation);

      //Add some meaningless data
      for (int i = 0; i < 4; i++)
      {
        DataRow parentTableRow = Ptable.NewRow();
        parentTableRow[0] = i; // ID to be referenced by the Child
        parentTableRow[1] = "some data " + i.ToString();
        parentTableRow[2] = "more data " + i.ToString();
        Ptable.Rows.Add(parentTableRow);

        for (int j = 0; j < 7; j++)
        {
          DataRow childTableRow = Ctable.NewRow();
          childTableRow[0] = parentTableRow[0]; //ID of the Parent row
          // I like using indexes because sometimes I change column names, but I
          // never change the column index.
          childTableRow[1] = j;  
          Ctable.Rows.Add(childTableRow);
        }
      }
      
      myDataSet.WriteXml("d:\\documents and settings\\vincent\\desktop\\childparent.xml");

The output is the following:

<?xml version="1.0" standalone="yes"?>
<root>
  <object>
    <objectdata1>some data 0</objectdata1>
    <objectdata2>more data 0</objectdata2>
    <item>
      <Age>0</Age>
    </item>
    <item>
      <Age>1</Age>
    </item>
    <item>
      <Age>2</Age>
    </item>
    <item>
      <Age>3</Age>
    </item>
    <item>
      <Age>4</Age>
    </item>
    <item>
      <Age>5</Age>
    </item>
    <item>
      <Age>6</Age>
    </item>
  </object>
  <object>
    <objectdata1>some data 1</objectdata1>
    <objectdata2>more data 1</objectdata2>
    <item>
      <Age>0</Age>
    </item>
    <item>
      <Age>1</Age>
    </item>
    <item>
      <Age>2</Age>
    </item>
    <item>
      <Age>3</Age>
    </item>
    <item>
      <Age>4</Age>
    </item>
    <item>
      <Age>5</Age>
    </item>
    <item>
      <Age>6</Age>
    </item>
  </object>
  <object>
    <objectdata1>some data 2</objectdata1>
    <objectdata2>more data 2</objectdata2>
    <item>
      <Age>0</Age>
    </item>
    <item>
      <Age>1</Age>
    </item>
    <item>
      <Age>2</Age>
    </item>
    <item>
      <Age>3</Age>
    </item>
    <item>
      <Age>4</Age>
    </item>
    <item>
      <Age>5</Age>
    </item>
    <item>
      <Age>6</Age>
    </item>
  </object>
  <object>
    <objectdata1>some data 3</objectdata1>
    <objectdata2>more data 3</objectdata2>
    <item>
      <Age>0</Age>
    </item>
    <item>
      <Age>1</Age>
    </item>
    <item>
      <Age>2</Age>
    </item>
    <item>
      <Age>3</Age>
    </item>
    <item>
      <Age>4</Age>
    </item>
    <item>
      <Age>5</Age>
    </item>
    <item>
      <Age>6</Age>
    </item>
  </object>
</root>

, which is exactly what I intended. This file can then be read directly into a DataSet, and it will set up the two tables as before, without the need for a schema or for the parent and child id's to be saved. It also makes the XML look a whole lot prettier.

In order to leave the child and/or parent ID's in the XML, set the ColumnMapping properties to MappingType.Element. When that is done, it is possible to seperate the items into a separate area within the XML by changing the Nested attribute on myRelation to false. For this to load correctly back into a DataSet object, the schema will need to be saved by adding the argument "XmlWriteMode.WriteSchema" to "myDataSet.WriteXML".