How to use XML.modify 'replace value' with a WHERE clause

Question

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").

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?

Answer 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:

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