September 16, 2003 at 5:20 pm
I've seen many examples of how to insert XML into SQL tables but I've got a specific problem that I've never seen addressed.
I have a table hierarchy that I am trying to insert into that is four or five tables deep. I have an XML document that has the data that I want to insert. The issue is that the tables that I want to insert into use identity columns as the primary keys. Those identity columns are also the foreign keys that tie the tables together.
Obviously I can't have the ID values in the XML because they haven't been generated yet, but the data is all tied together correctly through the XML structure. How can I quickly get the data in the XML document inserted into my SQL server tables and tied together correctly with the new identity column values? If someone has an elegant answer I would be forever indebted.
September 19, 2003 at 8:00 am
This was removed by the editor as SPAM
September 21, 2003 at 5:47 pm
Interesting question. I don't see any easy way offhand. Guess one way would be to insert everything into temp tables that had a key for the id's, then as you inserted you could bring back the keys (assuming you had some other unique way to identify the records or you did one row insert at a time). Don't think that counts as elegant, but I'd like to see a good solution!
Not any easy change, but this is one of those situations where being able to generate your key on the client is nice - uniqueidentifiers fit the bill pretty well. Then you can just insert everything.
Andy
September 24, 2003 at 2:13 am
elegant; no. answer; yes:
use @mp:id and @mp:parentid on your openxml with clause to preserve the structure of your xml and then update each table in turn adding the @mp:id info to the table you are updating. Then update your temp tables back with the generated ids. Repeat for each level
Keith Henry
DBA/Developer/BI Manager
Keith Henry
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply