Defining nested relationships in datatables wiith dataset.readxml



I want to read my xml into a dataset using dataset.readXML(filename)


The idea is to get the 2 tables loaded, and the use LINQ to join them and dump the query result into a sql database.


I have the following xml



<Report>
<Parameters>
<Code>ABC</Code>
<Expiries>
<date>2000-01-01</date>
<time>08:00:00</time>
<reason>The guy liked cake</reason>
</Expiries>
<Expiries>
<date>2002-01-01</date>
<time>08:00:00</time>
<reason>The guy still liked cake</reason>
</Expiries>
</Parameters>
<Parameters>
<Code>BCA</Code>
<Expiries>
<date>2000-01-01</date>
<time>08:00:00</time>
<reason>The guy liked cake</reason>
</Expiries>
<Expiries>
<date>2002-01-01</date>
<time>08:00:00</time>
<reason>The guy still liked cake</reason>
</Expiries>
</Parameters>
</Report>


I define the datatable as so: (addcolumn is a convenience method to add columns)



public DataTable Parameters = new DataTable("Parameters")
.addColumn<string>("Code")

public DataTable Expiries = new DataTable("Parameters")
.addColumn<string>("date")
.addColumn<string>("time")
.addColumn<string>("reason")


and I import it into the tables using the following:



DataSet tds = new DataSet()
tds.Tables.add(Parameters);
tds.Tables.add(Expiries);

tds.ReadXML(file.FullName)
//now a record on tds.tables["Parameters"] will have Code="ABC"


This all works brilliantly, but it throws away the relationship between my expiries and my parameters. If I do it without using the defined columns, it will create a relationship between the 2 tables automatically eg



DataSet tds = new DataSet()

tds.ReadXML(file.FullName)
//now a record on tds.tables["Parameters"] will have Code="ABC", Parameter_Id=0


How do I define this relationship in the datatable definition, so that it mimics what the auto generated schema has?


No comments:

Post a Comment