問題描述
為了首先給出這個問題的一些背景，我正在重寫一些當前循環通過一些 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 差不多在那里，但周圍每個節點，而不是圍繞所有節點
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
這篇關于修改 SQL server 中的 XML 以添加根節點的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！