Modify XML in SQL server to add a root node

Question

To give some background to this problem first, I am rewriting some code that currently loops through some xml, doing an insert to a table at the end of each loop - replacing with a single sp that takes an xml parameter and does the insert in one go, 'shredding' the xml into a table.

The main shred has been done successfully,but currently one of the columns is used to store the entire node. I have been able to work out the query necessary for this (almost), but it misses out the root part of the node. I have come to the conclusion that my query is as good as I can get it, and I am looking at a way to then do an update statement to get the root node back in there.

So my xml is of the form;

<xml>
<Items>
<Item>
    <node1>...</node1><node2>..<node2>.....<noden>...<noden>
<Item>
<Item>
    <node1>...</node1><node2>..<node2>.....<noden>...<noden>
<Item>
<Item>
    <node1>...</node1><node2>..<node2>.....<noden>...<noden>
<Item>
......
<Items>
</xml>

So the basic shredding puts the value from node1 into column1, node2 into column2 etc. The insert statement looks something like;

INSERT INTO mytable col1, col2,...etc.....,wholenodecolumn
Select  
doc.col.value('node1[1]', 'int') column1,
doc.col.value('node2[1]', 'varchar(50)') column2,
....etc......,
doc.col.query('*')--this is the query for getting the whole node
FROM @xml.nodes('//Items/Item') doc(col)

The XML that ends up in wholenodecolumn is of the form;

<node1>...</node1><node2>..<node2>.....<noden>...<noden>

but I need it to be of the form

<Item><node1>...</node1><node2>..<node2>.....<noden>...<noden></Item>

There is existing code (a lot of it) that depends on the xml in this column being of the correct form.

So can someone maybe see how to modify the doc.col.query('*') to get the desired result?

Anyway, I gave up on modifying the query, and tried to think of other ways to accomplish the end result. What I am now looking at is an Update after the insert- something like;

update mytable set wholenodecolumn.modify('insert <Item> as first before * ')

If I could do this along with

 .modify('insert </Item> as last after * ')  

that would be fine, but doing 1 at a time isn't an option as the XML is then invalid

XQuery [mytable.wholenodecolumn.modify()]: Expected end tag 'Item'  

and doing both together I don't know if it's possible but I've tried various syntax and can't get to work.

Any other approaches to the problem also gratefully received

Answer 1

I beleive you can specifiy the Root Node name by using the FOR clause.

For example:

select top 1 *
from HumanResources.Department
for XML AUTO, ROOT('RootNodeName')

Take a looks at books online for more details:

http://msdn.microsoft.com/en-us/library/ms190922.aspx