如何使用XML.modify'替换值'与WHERE子句

I have an XML column that contains foreign key ID values stored within XML. I need to be able to update the XML when the ID value changes (e.g. change all instances of "26" to "999").

我有一个XML列,其中包含存储在XML中的外键ID值。我需要能够在ID值改变时更新XML(例如,将“26”的所有实例更改为“999”)。

From my readings here I've tried to adapt the code, but it isn't actually updating:

从我的阅读中我已经尝试调整代码,但实际上并没有更新:

DECLARE @tmp TABLE (xmlCol xml);

INSERT INTO @tmp (xmlCol) SELECT 
('<search><groups><g id="25" /><g id="26" /></groups></search>') UNION ALL SELECT
('<search><groups><g id="2" /><g id="9" /></groups></search>') UNION ALL SELECT
('<search><groups><g id="7" /><g id="12" /><g id="26" /></groups></search>');

SELECT * FROM @tmp;

DECLARE @oldId int = 26;
DECLARE @newId int = 999;

UPDATE @tmp SET xmlCol.modify('replace value of 
    (/search/groups/g/text()[.=(sql:variable("@oldId"))])[1]
with 
    (sql:variable("@newId"))');

SELECT * FROM @tmp;

What is the correct modify logic to achieve this please?

请达到这个目的的正确修改逻辑是什么?

1 个解决方案

#1

The XPath you specified matches value to text of the element g and would worked for elements like <g>26</g>, in your case you have to change XPath to match id attribute of the element. Assuming groups are unique within /search/groups you may try follwoing:

您指定的XPath将值与元素g的文本匹配,并且适用于 26 等元素,在您的情况下,您必须更改XPath以匹配元素的id属性。假设组在/ search / groups中是唯一的,您可以尝试以下方法:

UPDATE @tmp
SET xmlCol.modify('replace value of 
    (/search/groups/g[@id=sql:variable("@oldId")]/@id)[1]
with 
    (sql:variable("@newId"))');