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

Ciro

Answer 1

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

I am currently looking into FLWOR Xquery constructs in the query.
col.query('for $item in * return <Item> {$item} </item>') is almost there, but puts around each node, rather than around all the nodes

I was almost there with the syntax, a small tweak has given me what I needed;

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

Thankyou to everyone that helped. I have further related issues now but I'll post as separate questions

Ciro

Answer 2

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

Serried

I've experimented with this but can't see how it will work with my query. Can u give an example of how this would be used with; Select.....doc.col.query('*') FROM @xml.nodes('//Items/Item') doc(col) type query? – Ciro

The FOR XML clause is not allowed in a INSERT statement. – Ciro

OP is looking at adding a root while using .query() – Fa

Answer 3

Couldn't you just add the '' / '' as fixed texts in your select? Something like:

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)

Marc

Marlette

No sorry this won't work. I think what you mean as well would be '<Item>' + doc.col.query('') + '</Item> - the code you give here is selecting <Item> and </Item> as separate columns, whereas I need them to be part of the xml I get as a result of doc.col.query(''). – Ciro

Also, to do this you'd need to convert the xml to varchar. – Ciro

Further - Have tried converting the xml to varchar(max), but get collation conflicts- I think due to some characters in the xml (have also tried nvarchar) – Ciro

OK, I see your problem - what about breaking up the INSERT query into two steps? One to insert the doc.col.value(...) entries, and then the second to insert just the XML part of it (using the FOR XML AUTO, ROOT('Item') technique) ? – Marlette

I guess you mean to update the rows inserted with the xml; yes I would be happy to do this but the FOR XML AUTO, ROOT('Item') technique doesn't help; this appears to be for selecting into an xml structure rather than working with existing xml – Ciro

For anyone following this, I an currently looking into FLWOR Xquery constructs in the query. col.query('for $item in * return <Item> {$item} </item>') is almost there, but puts <Item></Item> around each node, rather than around all the nodes. – Ciro