How do I replace XML attribute values using @xml.modify(…) with a new value stored in a @variable?



I am trying to replace the values of all attributes Items/Item/@attr inside an XML-typed variable (@xml) with a dynamically built value (@attr):



DECLARE @xml XML = '<Items>
<Item attr="…" />
<Item attr="…" />
<Item attr="…" />
</Items>';

DECLARE @newValue NVARCHAR(MAX) = …;

SET @xml.modify('replace value of (/Items/Item/@attr)[1] with "' + @newValue + '"');
-- ^^^^^^^^^^^^^^^^^
-- this triggers below error


However, this gives me the following error message:



Msg 8172, Level 16, State 1, Line …

The argument 1 of the xml data type method "modify" must be a string literal.



What am I doing wrong?


No comments:

Post a Comment