Wildcard find and replace XML: Cannot specify replacement value



In the following, XML schema which exemplifies that production data that I wish to modify, I'm simply trying to find ANY value of "Billy" and replace it with "Peter". The reason for the wildcard is we have to do this with a lot of values and a lot of tables with XML columns, and once I get this working, I can easily wrap it up in a cursor.



DECLARE @tbXML TABLE ( ID INT , ParameterValue XML )
declare @oldval nvarchar(max) = 'Billy'
declare @newval nvarchar(max) = 'Peter'

INSERT INTO @tbXML VALUES ( 1, '<USER>Billy</USER>' )
INSERT INTO @tbXML VALUES ( 2, '<USER>John</USER>' )
INSERT INTO @tbXML VALUES ( 3, '<USER>David</USER>' )
INSERT INTO @tbXML VALUES ( 4, '<USER>Nick</USER>' )



SELECT 'before', *
FROM @tbXML

WHILE EXISTS ( SELECT 1 FROM @tbXML WHERE ParameterValue.exist('/User[(text()[1])eq sql:variable("@oldval")]')=1)
BEGIN

UPDATE @tbXML
SET ParameterValue.modify('replace value of (/User[(text()[1]) eq sql:variable("@oldval")] with sql:variable("@newval")')
WHERE ParameterValue.exist('/User[(text()[1])eq sql:variable("@oldval")]')=1
END

SELECT *
FROM @tbXML


But what I get is:



XQuery [@tbXML.ParameterValue.modify()]: ")" was expected.


Either I'm stupidly missing a ")" somewhere (Tried lots of permutations, same error), or there is something more wrong with my approach. Would appreciate a nudge in the right direction, thanks!


No comments:

Post a Comment