I need to parse XML from a file into tables while retaining their relationships through custom-generated ID values.
For example, 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, 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.
How are the ID values generated?
That's kind of part of the question. For now, they aren't. The idea is that each
CONSTRUCTION element would be assigned an
ID increasing from 1. Then all the children of
CONSTRUCTION would then refer to the same
ID assigned to the parent, and so on. Basically, it's just about dividing the data in the XML into multiple tables while keeping the referential integrity intact.
I would have no problem using an identity column as a primary key, so long as the relations aren't mixed up as a result. I don't know how to go about doing this, so I assumed one would have to somehow manually define the logic for creating the ID's, as opposed to identity?