xquery replacing value if node exist

2
votes

I'm working with xQuery and I'm trying to replace the value of test element if the element tag Active does not exist under the User element.

(I added quotes to be able to display it here properly...)

declare @testXML xml = '
<USER>
  <ID>10</ID>
  <TEST>1</TEST>
</USER>'

set @testXML.modify('if (exists(/USER/Active)=false)
                 then replace value of /USER/TEST with "2"
                 else ()') 
select @testXML

For this I get this error:

XQuery [modify()]: Syntax error near 'value', expected 'else'.

What did I get wrong?

1
sql-serverxqueryxquery-sqlxquery-update

1 Answers

2
votes

You are not allowed to embed replace value of ... with .. in an expression.

From replace value of (XML DML)

replace value of 
      Expression1 
with
      Expression2

You can use something like this instead:

set @testXML.modify('replace value of (/USER[not(Active)]/TEST/text())[1] 
                     with "2"')

SQL Fiddle