This follows on from this question:
http://stackoverflow.com/questions/624206/modify-xml-in-sql-server-to-add-a-root-node/
In addition to the problem posed and now fixed in that question, I am also splitting the existing table into 2, where the 2nd table is a sub-table of the first, with a foreign key back to the first.
So currently
table1(ID, col1, col2, col3....coln)
Is becoming
table1(ID, col1, col2, col3)
and
table2(PK, FK_table1, col4, col5, ...coln)
At the moment I haven't removed anything from the existing table, just created the second, and I have discovered the OUTPUT construct so that I can insert into both tables at once, including putting the PK of the 1st table into the 2nd as FK in the 1 insert - so something like;
INSERT INTO table1 col1, col2,...etc...
OUTPUT inserted.ID, col1, col2, ....
INTO table2(FK_table1, col1, col2, .... )
Select col.value('node1[1]', 'int') col1,
col.value('node2[1]', 'varchar(50)') col2,
....etc......
FROM @xml.nodes('//Items/Item') doc(col)
This successfully inserts into both tables at once BUT relies on the fact I haven't removed the columns from table1 yet, *AND results in a 1:1 relationship between the tables, when what I need is for table1 to just have rows for distinct col1, col2, col3 , with table2 holding the more detailed info
I'm wondering if I am going about this the right way? Am I going to be able to do this in 1 query or am I going to have to break it down? I'm thinking maybe I could shred the xml first into table1, then shred again separately into table2, joining on table1 to get the FK for the insert? Anyone done this sort of thing before?