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

score 7 · Answer 1 · answered Mar 08 '09 at 21:29

7

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

answered Mar 08 '09 at 21:29

John Sansom

41,005
9
72
84

1

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? – DannykPowell Mar 09 '09 at 07:53
The FOR XML clause is not allowed in a INSERT statement. – DannykPowell Mar 09 '09 at 12:37
OP is looking at adding a root while using .query() – Mike M Nov 03 '19 at 21:23

score 7 · Accepted Answer · edited Jul 05 '10 at 14:41

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

score 0 · Answer 3 · edited Jul 13 '10 at 14:29

0

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

edited Jul 13 '10 at 14:29

Tom H

46,766
14
87
128

answered Mar 08 '09 at 21:23

marc_s

732,580
175
1,330
1,459

No sorry this won't work. I think what you mean as well would be '' + doc.col.query('*') + ' - the code you give here is selecting and as separate columns, whereas I need them to be part of the xml I get as a result of doc.col.query('*'). – DannykPowell Mar 09 '09 at 07:47
Also, to do this you'd need to convert the xml to varchar. – DannykPowell Mar 09 '09 at 07:49
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) – DannykPowell Mar 09 '09 at 10:05
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) ? – marc_s Mar 09 '09 at 10:55
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 – DannykPowell Mar 09 '09 at 12:23
For anyone following this, I an currently looking into FLWOR Xquery constructs in the query. col.query('for $item in * return {$item} ') is almost there, but puts around each node, rather than around all the nodes. – DannykPowell Mar 09 '09 at 12:28

Modify XML in SQL server to add a root node

3 Answers3

Linked