Modify the XML on the SQL server to add a root node

Question

First of all, in order to give some idea of ​​this problem, I rewrite the code that is currently going through some xml, inserting into the table at the end of each cycle - replacing with one sp, which takes the xml parameter, and does the insert in one times "chopping" xml into a table.

The main shredding was done successfully, but currently one of the columns is used to store the entire node. I was able to work out the request (almost) necessary for this, but it missed the root of the node. I came to the conclusion that my request is as good as I can get it, and I am looking for a way to make an update expression to return the root directory of the node.

So my xml has a 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 basic chopping puts the value from node1 to column1, node2 to column2, etc. The insert statement looks something like this:

 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 falls into the whole collection takes the form;

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

but I need him to look

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

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

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

In any case, I refused to change the request and tried to come up with other ways to achieve the final result. What I'm looking at right now is an update after insertion - something like;

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

If I could do it with

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

that would be nice, but doing 1 at a time is not an option, since XML is then invalid

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

and to do it together, I don’t know if this is possible, but I tried a different syntax and I can’t work.

Any other approaches to the problem were also gratefully received.

Answer 1

Answering my own question here! - this follows from the comments on one of the other attempts at answers, where I said:

I am currently viewing FLWOR Xquery Constructors in a query.
col.query('for $item in * return <Item> {$item} </item>') almost there, but puts around every node, not around all nodes

I was almost there with the syntax, a little tweak gave me what I needed;

 doc.col.query('<Item> { for $item in * return $item } </item>' 

Thanks to everyone who helped. I have additional problems, but I will post as separate issues.

Answer 2

I believe that you can specify the name of the Node root using the FOR clause.

For instance:

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

Take a look at the books online for more details:

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

Answer 3

Could you just add `` / '' as fixed texts to your selection? Sort of:

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

Mark