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.
首先介绍一下这个问题的背景知识，我正在重写一些当前循环遍历某些xml的代码，在每个循环结束时插入一个表 - 用一个带有xml参数的sp替换并在一个中插入一个go，'将xml'粉碎成表。
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.
主Shred已成功完成，但目前其中一列用于存储整个节点。我已经能够计算出这个（几乎）所需的查询，但它错过了节点的根部分。我得出结论，我的查询和我能得到的一样好，我正在寻找一种方法，然后做一个更新语句，让根节点回到那里。
So my xml is of the form;
所以我的xml是形式的;
<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;
因此，基本的碎化将node1中的值放入column1，node2放入column2等.insert语句看起来像;
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;
最终在整个字节列中的XML是形式的;
<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.
现有代码（很多代码）依赖于此列中的xml具有正确的形式。
So can someone maybe see how to modify the doc.col.query('*') to get the desired result?
那么有人可能会看到如何修改doc.col.query（'*'）以获得所需的结果？
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
没关系，但一次做1个不是一个选项，因为XML无效
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
对此问题的任何其他方法也非常感激
3 个解决方案
#1
8
I beleive you can specifiy the Root Node name by using the FOR clause.
我相信您可以使用FOR子句指定根节点名称。
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
http://msdn.microsoft.com/en-us/library/ms190922.aspx
#2
6
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我目前正在查询查询中的FLWOR Xquery构造。 col.query（'for item in * return> Item> {$ item} '）几乎就在那里，但是绕过每个节点，而不是围绕所有节点
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
谢谢所有帮助过的人。我现在还有其他相关问题，但我会将其作为单独的问题发布
#3
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
渣子
#1
8
I beleive you can specifiy the Root Node name by using the FOR clause.
我相信您可以使用FOR子句指定根节点名称。
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
http://msdn.microsoft.com/en-us/library/ms190922.aspx
#2
6
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我目前正在查询查询中的FLWOR Xquery构造。 col.query（'for item in * return> Item> {$ item} '）几乎就在那里，但是绕过每个节点，而不是围绕所有节点
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
谢谢所有帮助过的人。我现在还有其他相关问题，但我会将其作为单独的问题发布
#3
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
渣子