問題描述
我在 XML 列中有一個(gè)簡(jiǎn)單的 xml
I have a simple xml in a XML column
<Bands>
<Band>
<Name>beatles</Name>
<Num>4</Num>
<Score>5</Score>
</Band>
<Band>
<Name>doors</Name>
<Num>4</Num>
<Score>3</Score>
</Band>
</Bands>
我已設(shè)法用以下內(nèi)容更新該列:
I have managed to update the column with :
-----just update the name to the id)----
UPDATE tbl1
SET [myXml].modify('replace value of (/Bands/Band/Name/text())[1]
with sql:column("id")')
一切正常.
問題 #1
如何使用此查詢將值更新為 id+lalala":
How can I use this query to udpate the value to id+"lalala":
UPDATE tbl1
SET [myXml].modify('replace value of (/Bands/Band/Name/text())[1]
with sql:column("id") + "lalala"')
Error = XQuery [tbl1.myXml.modify()]: '+' 的參數(shù)必須是單個(gè)數(shù)字原始類型
問題 #1
假設(shè)我不想更新第一條記錄 ([1]) ，但我想udpate(與上面相同的更新)只在 得分>4.
Let's say I Don't want to update first record ([1]) , But I want to udpate (the same update as above) only where score>4.
我當(dāng)然可以在 xpath 中寫:
I can write ofcourse in the xpath :
替換(/Bands/Band[Score>4]/Name/text())[1]的值
但我不想在 Xpath 中這樣做.是否有使用 Where 子句執(zhí)行此操作的正常方法?
But I dont want to do it in the Xpath. Isn't there a Normal way of doing this with a Where clause ?
類似:
UPDATE tbl1
SET [myXml].modify('replace value of (/Bands/Band/Name/text())[1]
with sql:column("id") where [...score>4...]')
這里是在線 sql
推薦答案
如果你想連接字符串，你應(yīng)該使用 concat 并且如果 id 在您的情況下是一個(gè)整數(shù)，您需要將其轉(zhuǎn)換為 concat 函數(shù)中的字符串.
If you want to concatenate strings you should use concat and if id in your case is an integer you need to cast it to a string in the concat function.
在 where 子句中可以過(guò)濾表中要更新的行，不能在 XML 中指定要更新的節(jié)點(diǎn).這必須在 xquery 表達(dá)式中完成.但是，您可以在 where 子句中使用 exist 來(lái)過(guò)濾掉真正需要更新的行.
In the where clause you can filter rows of the table to update, you can not specify what nodes to update in the XML. That has to be done in the xquery expression. You can however use exist in the where clause to filter out the rows that really needs the update.
update tbl1
set myXml.modify('replace value of (/Bands/Band[Score > 4]/Name/text())[1]
with concat(string(sql:column("id")), "lalalala")')
where myXml.exist('/Bands/Band[Score > 4]') = 1
這篇關(guān)于使用 XQUERY 更新 SQL 服務(wù)器 xml 列?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！