使用XQUERY更新SQL Server的XML列？

Question

我有一个简单的XML在一个XML列中。

<Bands>
    <Band>
        <Name>beatles</Name>
        <Num>4</Num>
        <Score>5</Score>
    </Band>
    <Band>
        <Name>doors</Name>
        <Num>4</Num>
        <Score>3</Score>
    </Band>
</Bands>

我已成功更新了该列，具体操作如下：

   -----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"：

   UPDATE tbl1
   SET [myXml].modify('replace value of (/Bands/Band/Name/text())[1]
   with sql:column("id") + "lalala"')

错误 XQuery [tbl1.myXml.modify()]: '+' 的参数必须为单个数值基元类型

问题＃1

假设我不想更新第一条记录（[1]），但我想仅在 score>4 的情况下进行与上述相同的 udpate。

我可以当然在 xpath 中编写：

replace value of (/Bands/Band[Score>4]/Name/text())[1]

但我不想在 Xpath 中这样做。是否有一种使用 Where 子句正常执行此操作的方法？

类似于：

   UPDATE tbl1
   SET [myXml].modify('replace value of (/Bands/Band/Name/text())[1]
   with sql:column("id")  where [...score>4...]')

这里是在线 SQL 工具

- Royi Namir

Answer 1

如果你想要连接字符串，应该使用concat函数。如果在你的情况下id是一个整数，则需要在concat函数中将其转换为字符串。

在where子句中，你可以过滤要更新的表格行，但无法指定要在XML中更新哪些节点。这必须在xquery表达式中完成。然而，你可以在where子句中使用exist函数来过滤掉确实需要更新的行。

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

- Mikael Eriksson

---

他们为什么要做这个 [1] 的事情？难道他们没有考虑到我可能想要更新 不止一个元素 吗？ - Royi Namir

@RoyiNamir 说“Expression1必须是静态单例”的文档在这里(http://msdn.microsoft.com/en-us/library/ms190675.aspx)。对于`insert`也是一样的，但是`delete`可以删除多个节点。我不知道为什么他们选择这样做，但如果允许这样做，你可能会陷入一些奇怪的情况，其中修改后的节点会改变找到需要修改的节点的谓词。如果您需要更新多个节点，则必须在while循环中执行此操作。 - Mikael Eriksson

谢谢。我选择这个答案，因为它更简洁易读。但是非常感谢两个回答！ - Royi Namir

为什么 [Score > 4] 在 where 和 xpath 上方的语句中都出现了（例如 replace value of (/Bands/Band[Score > 4]/N...）？我的意思是，现在 where 似乎是多余的。 - Royi Namir

@RoyiNamir WHERE子句将防止您更新不需要更新的行。 您只需更新Score> 4的行，而无需更新表中的每一行。 - Mikael Eriksson

这就是为什么我一直爱你的原因。 :-) - Royi Namir

Answer 2

Q1:

;with t as (select convert(varchar(10),id) + 'lalala' id2, * from #tbl1)
   UPDATE t
   SET [myXml].modify('replace value of (/Bands/Band/Name/text())[1]
    with sql:column("id2")');

Note: 请注意，这将仅更新第一个乐队的名称，而不是所有乐队的名称。

第二个问题1：你不能这样做。特别是因为（/ Bands / Band [Score<4] / Name / text（））[1]（我更改为<）专门针对问题中XML中的Doors乐队。另一方面，WHERE子句将在XML中工作，而不是在路径中的特定级别上。例如，非常错误的解释：

;with t as (
    select a.*, n.m.value('.','int') Score
    from #tbl1 a
    cross apply myXml.nodes('/Bands/Band/Score') n(m) -- assume singular
)
   UPDATE t
   SET [myXml].modify('replace value of (/Bands/Band/Name/text())[1]
    with sql:column("id")')
   where Score < 4

由于xml中至少有一组得分小于4分，因此xml会被更新。 但是，由于xml.modify只能在第一个匹配项上生效一次，因此只有第一个乐队的名称会被更新，而不是符合得分过滤条件的乐队。

- RichardTheKiwi