How to use XML.modify "replace value" with WHERE clause

Question

I have an XML column that contains foreign key identifier values ​​stored in XML. I need to update the XML when the ID value changes (for example, change all instances of "26" to "999").

From my readings here, I tried to adapt the code, but actually it is not updated:

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 logic modifyto achieve this, please?

Answer 1

XPath g , <g>26</g>, 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"))');