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?

Mitch Wheat · Accepted Answer · 2009-06-13 05:42:44Z

2

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

edited Jun 13, 2009 at 5:42

answered Jun 13, 2009 at 5:33

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

paulwhit Over a year ago

what is the workaround in the edge case you mention (empty text)

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related