I need to parse XML from a file into tables while retaining their relationships through custom-generated ID values.
IE, if I had the following XML:
<root> <construction> <constructionName>randomname1</constructionName> <project> <projectname>another randomname</projectname> <businesspartners> <partnername>bilbo bagginses</partnername> </businesspartners> <employees> <employee> <empname>frodo</empname> <empaddress>etc...</empaddress> </employee> </employees> </project> <info> <randElement></randElement> </info> <constructionType>houses</constructiontype> </construction> <construction> <...(etc, same as above, times n^10)/> </construction> </root>
From that XML, I need to generate data to the following tables:
CONSTRUCTION (CONSTRUCTION_ID INT PRIMARY KEY , CONSTRUCTIONNAME VARCHAR.. , CONSTRUCTIONTYPE VARCHAR.. ) PROJECT (PROJECT_ID INT PRIMARY KEY , CONSTRUCTION_ID INT FOREIGN KEY REFERENCES CONSTRUCTION , PROJECTNAME VARCHAR.., ) BUSINESSPARTNERS (BUSINESSPARTNERS_ID INT PRIMARY KEY , PROJECT_ID INT FOREIGN KEY REFERENCES PROJECT , PARTNERNAME VARCHAR..) etc...
Basically, the idea is to construct tables with their references intact to represent the XML. The table structures already exist and cannot be changed to accommodate this script. This is simply a matter of being able to do the same kind of XML parsing and then adding the data to the tables while generating the correct referencing ID values, as we did before with integration. Only doing this exclusively with SQL Server, and without using SSIS.
Now, the actual scenario and the file in question is rather massive, so I do by no means expect a full answer. Just tips on where to start looking. I'm quite inexperienced with dealing with XML.
For now, my preferred solution would be just importing the data with OPENROWSET, and then using dynamic SQL with OPENXML to parse the document into the tables by basically looping the elements and their sub-elements one at a time. But that seems a lot more trouble than some other, smarter way. I'm researching this as I go, but I thought I'd drop this here just in case someone had a great idea already in mind. :)