Using XQuery in SQL 2005 to replace a node's value?

Question

Given the following XML variable, how can I replace "UNKNOWN" in the StateCode node with "FOO" in TSQL for MS SQL 2005?

declare @xmldata xml

set @xmldata = 

    '<Collection>
     <Plan>
      <StateCode>UNKNOWN</StateCode>
      <Type>Tubular</Type> 
     </Plan>
    </Collection>'

Unlike a similar question that I found, this is simply an XML typed variable, not a table record.

Should I simply insert the variable value into a table and go with the method presented in that question, or can I just manipulate the XML variable directly and perform the replacement?

Answer 1

You can manipulate the XML variable directly and perform the replacement:

set @xmldata.modify('replace value of (/Collection/Plan/Type/text())[1] with "new value"')

select @xml

(The XPath might not be exactly correct as I don't have access to a sql server atm)

Also, note that this won't work if UNKNOWN is actually empty text, that requires a bit more work!)