修改 SQL server 中的 XML 以添加根节点

2021-10-01 00:00:00 xml xpath sql-server shred

为了首先给出这个问题的一些背景，我正在重写一些当前循环通过一些 xml 的代码，在每个循环的末尾插入一个表 - 替换为一个接受 xml 参数的 sp 并执行一次性插入，将xml切碎"到一张表中.

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.

主shred已经成功完成，但目前其中一列用于存储整个节点.我已经能够(几乎)计算出为此所需的查询，但它错过了节点的根部分.我得出的结论是，我的查询尽我所能，我正在寻找一种方法，然后执行更新语句以将根节点恢复到那里.

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.

所以我的 xml 是这样的；

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>

所以基本的粉碎将值从 node1 放入 column1，将 node2 放入 column2 等等.插入语句看起来像；

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)

在整个节点列中结束的 XML 是以下形式；

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

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

但我需要它的形式

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

现有代码(很多)依赖于此列中的 xml 格式是否正确.

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

那么有人可以看到如何修改 doc.col.query('*') 以获得所需的结果吗?

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 * ')

如果我能做到这一点

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

那没问题，但一次做 1 个不是一种选择，因为 XML 无效

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

推荐答案

在这里回答我自己的问题！- 这是对我所说的其他尝试答案之一的评论:

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

我目前正在研究 FLWOR查询中的 Xquery 构造.
col.query('for $item in * return {$item} ') 差不多在那里，但周围每个节点，而不是围绕所有节点

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

相关文章