問題描述

我在 XML 列中有一個簡單的 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>

我已設法用以下內容更新該列:

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()]: '+' 的參數必須是單個數字原始類型

問題 #1

假設我不想更新第一條記錄 ([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.

我當然可以在 xpath 中寫:

I can write ofcourse in the xpath :

替換(/Bands/Band[Score>4]/Name/text())[1]的值

但我不想在 Xpath 中這樣做.是否有使用 Where 子句執行此操作的正常方法?

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

推薦答案

如果你想連接字符串，你應該使用 concat 并且如果 id 在您的情況下是一個整數，您需要將其轉換為 concat 函數中的字符串.

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 子句中可以過濾表中要更新的行，不能在 XML 中指定要更新的節點.這必須在 xquery 表達式中完成.但是，您可以在 where 子句中使用 exist 來過濾掉真正需要更新的行.

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

這篇關于使用 XQUERY 更新 SQL 服務器 xml 列?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！