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