問題描述
我有一個(gè)表(我們稱她為 t)，其中包含字段 id(int) 和 XmlField(xml).
I'm having a table (let's call her t) that contains the fields id(int) and XmlField(xml).
我嘗試在一個(gè)查詢中添加多個(gè)節(jié)點(diǎn)，但無論我嘗試什么，總是出現(xiàn)錯(cuò)誤.
I try to add multiple node in one query but no matter what I tried I keep getting errors.
查詢是:
update t
set XmlField.modify('insert <f1>value here</f1><f2>value there</f2> into (/xmldoc)')
我收到錯(cuò)誤:
XQuery [t.XmlField.modify()]:'' 附近的語法錯(cuò)誤，預(yù)期為 'as'、'into'、'before' 或 'after'.
XQuery [t.XmlField.modify()]: Syntax error near '', expected 'as', 'into', 'before' or 'after'.
當(dāng)我嘗試僅添加一個(gè) xml 節(jié)點(diǎn)時(shí)，它正在工作(示例):
When I trying to add only one xml node it's working (example):
update t set XmlField.modify('insert <f1>value here</f1> into (/xmldoc)')
當(dāng)我嘗試添加這樣的嵌套節(jié)點(diǎn)時(shí)它也能正常工作:
it's also working when I try to add nested nodes like this:
update t set XmlField.modify('insert <f><f1>value here</f1><f2>value there</f2></f> into (/xmldoc)')
有什么辦法可以實(shí)現(xiàn)嗎?
Is there any way to make it happen?
推薦答案
SQL Server 文檔確實(shí)很清楚地說明 insert 語句可以處理多個(gè)節(jié)點(diǎn).所以我的猜測(cè)是你的問題只是一個(gè)語法錯(cuò)誤.(Microsoft 語法與 XQuery Update Facility 規(guī)范，但很相似.)
The SQL Server documentation does say pretty clearly that the insert statement can handle multiple nodes. So my guess is that your problem is just a syntax error. (The Microsoft syntax varies slightly from that defined in the XQuery Update Facility spec, but it's recognizably similar.)
我會(huì)嘗試將元素 f1 和 f2 組合成一個(gè)序列并將它們括在括號(hào)中(規(guī)范在這里需要一個(gè) ExprSingle，這意味著不允許使用頂級(jí)逗號(hào)):
I'd try making the elements f1 and f2 into a sequence and wrapping them in parentheses (the spec requires an ExprSingle here, which means no top-level commas are allowed):
update t
set XmlField.modify(
'insert (<f1>value here</f1>, <f2>value there</f2>) into (/xmldoc)')
(未針對(duì) SQL Server 進(jìn)行測(cè)試.)
(Not tested against SQL Server.)
這篇關(guān)于在單個(gè)查詢中將多個(gè)節(jié)點(diǎn)插入到 xml 字段的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！