TSQL xml.modify replace value for node with value

Question

I need to update a XML node in a SQL Server column.

I see many examples of using an xpath and identifying the node using the attribute.

The XML I am working with is auto generated and does not come with attributes on the nodes I need. How would I construct the update statement to use another value in the node to find it?

I'm hoping to be able to write something like this:

declare @xml xml ='
<Content>
<ContentItems>
    <ContentItem>
        <ContentKey>abc</ContentKey>
        <Body>TextToUpdate</Body>
    </ContentItem>
    <ContentItem>
        <ContentKey>efg</ContentKey>
        <Body>Other</Body>
    </ContentItem>
</ContentItems>
</Content>'

select @xml

set @xml.modify(
'
replace value of 
(/content/contentitems[ContentKey="abc"]/body/text())[1]  
with ("Success")')

select @xml

The body text of the ContentItem with the ContentKey of 'ABC' is replaced with "Success". I can't figure out how to "select" the node using the node value since there are no attributes in the xml nodes. — twifosp
– twifosp, Commented Dec 1, 2015 at 18:56

har07 · Accepted Answer · 2015-12-05 03:57:41Z

1

You're in the right direction. Only wrong character-casing, as mentioned in the other answer, and missing /ContentItem in the XPath that prevent the original query to work. Fix that and you're good to go :

set @xml.modify('
replace value of 
(/Content/ContentItems/ContentItem[ContentKey="abc"]/Body/text())[1]  
with "Success"
')

answered Dec 5, 2015 at 3:57

har07

89.4k12 gold badges86 silver badges143 bronze badges

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

Comments

Matt · Accepted Answer · 2015-12-01 20:23:57Z

0

First: XML is case sensitive. So you'll want to make sure you're calling out the tags correctly in your XPath.

Second: Have a look at this example...

set @xml.modify
(
'
    replace value of (/Content/ContentItems/ContentItem/Body/text())[1]  
    with    (
            if (/Content/ContentItems/ContentItem/ContentKey/text() = "abc") then "Success"
            else ""
            )
')

select @xml

answered Dec 1, 2015 at 20:23

Matt

1,4411 gold badge15 silver badges29 bronze badges

1 Comment

Matt Over a year ago

Doing some reading on this here I am actually not sure how scalable this solution is. It works nicely for your XML, but say you were to add another ContentItem with a ContentKey of "abc". This would only update the first occurrence leaving the rest unchanged. mssqltips.com/sqlservertip/2738/…

Collectives™ on Stack Overflow

TSQL xml.modify replace value for node with value

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related