Title: Wildcard find and replace XML: Cannot specify replacement value
Tags: sql;sql-server;xml;sql-server-2008;xquery
Question: 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&gt;Billy</USER&gt;' )
INSERT INTO @tbXML VALUES ( 2, '<USER&gt;John</USER&gt;' )
INSERT INTO @tbXML VALUES ( 3, '<USER&gt;David</USER&gt;' )
INSERT INTO @tbXML VALUES ( 4, '<USER&gt;Nick</USER&gt;' )



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!


Here is the accepted answer: You don't need the WHILE or the WHERE.  Just do this:

```SELECT 'before', *
FROM @tbXML;

UPDATE  @tbXML
SET     ParameterValue.modify('replace value of (/USER[. = sql:variable("@oldval")]/text())[1]
            with sql:variable("@newval")');

SELECT  *
FROM @tbXML;
```



{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2014:00:10/02/14', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<sql><sql-server><xml><sql-server-2008><xquery>', 'Title': 'Wildcard find and replace XML: Cannot specify replacement value', 'ViewCount': 254, 'id': 26169529, 'user_AboutMe': '', 'user_AccountId': 435557.0, 'user_DisplayName': 'tpcolson', 'user_Reputation': 704.0, 'user_Views': 86.0, 'user_id': 822208.0}}