如何在XQuery中将值替换为节点的另一个值

Question

我在SQL Server 14.0中有一个名为Users的表。有多个用户，每个用户都有一个XML格式的列数据，称为PermissionData。以下是XML:

<Policy>
<Permissions>
<Item>
<Key>Hello</Key>
</Item>
<Item>
<Key>Bye</Key>
</Item>
</Permissions>
</Policy>

所以我想用GoodBye值替换所有的Bye。某些用户可能具有此Bye值，也可能没有。一些其他用户可以将此Bye值作为第一密钥或第二密钥或第三密钥。因此，我不能只在每个键上做[2]，因为顺序可能不同。

最终结果应该是这样的：

<Policy>
<Permissions>
<Item>
<Key>Hello</Key>
</Item>
<Item>
<Key>GoodBye</Key>
</Item>
</Permissions>
</Policy>

有人能帮我写一个XQuery来更新users表中所有用户的Key值吗？以下是我尝试编写查询的内容，但它不起作用。

UPDATE Users
SET PermissionData.modify('replace value of (/Policy/Permissions/Item/Key)
with( if((/Policy/Permissions/Item/Key/text())="Bye")
then "GoodBye"
else () 
)')

Answer 1

请尝试以下解决方案。

注意事项：

• XML区分大小写，这就是使用lower-case()函数的原因
• XQuery.modify()方法一次只更新一个节点
• 如果XML根本没有<Key>...</Key>元素，或者它有一个不同的值，则.modify()方法不会影响XML，也不会出错

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, PermissionData  XML);
INSERT INTO @tbl (PermissionData ) VALUES
(N'<Policy>
<Permissions>
<Item>
<Key>Hello</Key>
<Value>
<WorkPermissions>
<Operations>
<Item>Say1</Item>
<Item>Say2</Item>
</Operations>
</WorkPermissions>
</Value>
</Item>
<Item>
<Key>Bye</Key>
<Value>
<WorkPermissions>
<Operations>
<Item>Say1</Item>
<Item>Say2</Item>
</Operations>
</WorkPermissions>
</Value>
</Item>
</Permissions>
</Policy>');
-- DDL and sample data population, end
DECLARE @currentValue VARCHAR(30) = 'bye' -- keep it lower case
, @newValue VARCHAR(30) = 'GoodBye';
UPDATE @tbl
SET PermissionData.modify('  
replace value of (/Policy/Permissions/Item/Key[lower-case(./text()[1])=sql:variable("@currentValue")]/text())[1]  
with (sql:variable("@newValue"))  
');
-- test
SELECT * FROM @tbl;

输出XML

<Policy>
<Permissions>
<Item>
<Key>Hello</Key>
<Value>
<WorkPermissions>
<Operations>
<Item>Say1</Item>
<Item>Say2</Item>
</Operations>
</WorkPermissions>
</Value>
</Item>
<Item>
<Key>GoodBye</Key>
<Value>
<WorkPermissions>
<Operations>
<Item>Say1</Item>
<Item>Say2</Item>
</Operations>
</WorkPermissions>
</Value>
</Item>
</Permissions>
</Policy>