Search code examples
c#datasetwritexml

DataSource WriteXML printing child rows for associate parent

In my database, I have a parent and child tables. For example, parent table contains last name and address, and child table contains last and first name. There is a foreign key, so that a row in child table must have corresponding last-name in parent table.

When I read those two tables using 2 DataAdapter's, and add those DataTable's to DataSet, I'd like to print XML, that looks like this:

<parent_table>
  <last_name>Smith</last_name>
  <address>111 Hi Street, Bye city</address>
  <child_table>
    <last_name>Smith</last_name>
    <first_name>Ann</first_name>
  </child_table>
  <child_table>
    <last_name>Smith</last_name>
    <first_name>Bob</first_name>
  </child_table>
</parent_table>

However, at present I'm getting two tables printed separately:

<parent_table>
  <last_name>Smith</last_name>
  <address>111 Hi Street, Bye city</address>
</parent_table>
<child_table>
  <last_name>Smith</last_name>
  <first_name>Ann</first_name>
</child_table>
<child_table>
  <last_name>Smith</last_name>
  <first_name>Bob</first_name>
</child_table>

Is there a way to achieve (hopefully using DataSet.WriteXML()) my desired output?

I tried adding ForeignKeyConstraint, and tried adding DataRelation, but neither changed the output.

Disclaimer: the above was hand-written, so please excuse if there's an error in XML. The actual tables contain better foreign key than 'last-name'.

Solution

  • From Writing DataSet Contents as XML Data (ADO.NET):

    When writing an XML representation of a DataSet that contains DataRelation objects, you will most likely want the resulting XML to have the child rows of each relation nested within their related parent elements. To accomplish this, set the Nested property of the DataRelation to true when you add the DataRelation to the DataSet. For more information, see Nesting DataRelations (ADO.NET).