Loading XML straight into the DB
Posted on 2006-11-03
I have been tasked with loading some relational data into the database. I want to utilise the XML-based power of Ado.net.
I can write out data expressed in two tables to XML easily enough when there is a PK-FK relationship between the two source tables.
The XML could be an object serialised to file, or it could be a dataset from someone else's DB.
The tables look like this:
Parent_id (PK, uniqueidentifier)
Parent_Z (has a default value)
Child_ID (PK, uniqueidentifier)
Child_Parent_Id (FK, uniqueidentifier)
Child_Z (has a defualt value)
The select staement to use is of course
select * from Parent inner join child on parent.parent_id = child.child_parent_id
But what about loading data from XML into the DB when the XML source data is nested but there are no IDs?
<A> I am A </A>
<B> I am B </B>
<X>I am x </Y>
<Y>I am y </Y>
<X>I am another x </Y>
<Y>I am another y </Y>
There can be 1+ children per parent.
Please advise how to load this XML into the relational database, taking care to note that:
a) source tag names are not necissarily the same as the target column names
b) source tag names for child and parent are not necessarily the same as the target table names in the DB
c) IDs used for PK and FK in the resulting tables are uniqueidentifiers.
d) there are additional columns in the tables which do not have nodes in the XML (but these columns have default values)
ps. SQL 2000, VS2003, .net 1.1, windows server 2003.