Preserve relationships after using DataSet.ReadXml to read XML into a DataSet

[EDN Admin]

Basically, I have an XML file Im reading into a DataSet using the ReadXml and ReadXmlSchema methods. The XML contains several nested (or parent-child) type relationships which I would like to preserve. The ultimate goal is to take the XML and
store it in multiple tables within a SQL database. Unfortunately, I have yet to find an approach that properly relates the foreign keys of the child tables.
Assume I have the following as my XML:
<ROOT><br/>
<employee><br/>
<name>John Doe</name><br/>
<documents><br/>
<document name=somefile.xls/><br/>
<document name=someotherfile.doc/><br/>
</documents><br/>
</employee><br/>
</ROOT>
After reading this XML into the DataSet using .ReadXml and .ReadXmlSchema I find 3 DataTables (See pic below). The three tables appear to have the appropriate relationships but when you look at the data I notice the child tables are not pulling the
ID from the parent table.
<img alt="" src="http://social.msdn.microsoft.com/Forums/getfile/210618" width="800
Is there a way to get the DataSet to map these relationships automatically when using the ReadXml() method or when inserting the DataSet records into SQL Server? (Desired result shown below).
<img alt="" src="http://social.msdn.microsoft.com/Forums/getfile/210621" width="800
SSIS is not a valid option so Ill have to utilize c#.
Im open to hearing alternative ideas but Im dealing with a rather large XML schema with several levels of nested xml nodes or parent-child relationships. The Dataset.ReadXml approach works perfectly with the exception of the foreign keys not populating
correctly.
Thanks.

View the full article