Parsing XML into multiple tables with foreign keys



I am attempting to write a SQL stored procedure to parse an XML field into data for two tables with a foreign key relationship between them.


My XML looks like the following:



<objects>
<object>
<name>Customer</name>
<objectType>Primary</objectType>
<fields>
<field>
<name>LastName</name>
<type>STRING</type>
</field>
<field>
...
</field>
</fields>
</object>
<object>
<name>Company</name>
<objectType>Primary</objectType>
<fields>
<field>
<name>CompanyName</name>
<type>STRING</type>
</field>
<field>
...
</field>
</fields>
</object>
...
</objects>


My end goal is to insert this data into a table structure like the following:



CREATE TABLE ObjectData{
ObjectID int NOT NULL IDENTITY(1,1),
ObjectName varchar(100),
ObjectType varchar(100)
}


CREATE TABLE FieldData{
FieldID int NOT NULL IDENTITY(1,1),
FieldName varchar(100),
FieldType varchar(100),
ObjectID int NOT NULL FOREIGN KEY FK_FieldData_ObjectID REFERENCES ObjectData(ObjectID)
}


I'm having a hard time wrapping my head around how to get this done. Anyone have any experience doing something like this?


No comments:

Post a Comment