使用 XQUERY 更新 SQL 服务器 xml 列?

2021-10-01 00:00:00 xml xquery sql-server-2008 sql-server

我在 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

相关文章