CODEWITHSUNDEEP
xmlsql-server-2005t-sqlxquery
Darth Continent
Darth Continent

Reputation: 2319

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

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?

Upvotes: 1

Views: 1360

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300489

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!)

Upvotes: 2

Related Questions