Update XML Node in SQL Server

Question

I went through all the various question pertaining to this topic, and I cant seem to find an answer in what is already provided.

What I am trying to do is insert a new node in the xml structure. When I run the below code, it doesnt shoot an error, but the XML doesn't not contain the newly inserted "material" sub section.

Would any of you mind pointing out where I am messing up here?

 DECLARE @t TABLE (rowID int IDENTITY PRIMARY KEY, XmlData XML)

 INSERT INTO @t (XmlData)
VALUES ('
<Root>
<ProductDescription ProductID="01" ProductName="Widget">
    <Features>
        <Warranty>1 year</Warranty>
        <Maintenance>Monthly</Maintenance>
    </Features>
</ProductDescription>
</Root>
')

UPDATE @t
SET XmlData.modify('insert <Material /> as first into (/Features)[1]')

SELECT 'before' s, DATALENGTH(XmlData) dl, XmlData
FROM @t
WHERE rowId = 1

marc_s · Accepted Answer · 2015-11-09 19:29:20Z

2

You need to specify the complete XPath to the location where you want to insert your new XML fragment:

UPDATE @t
SET XmlData.modify('insert <Material /> as first into (/Root/ProductDescription/Features)[1]')

You need to use

/Root/ProductDescription/Features)[1]

not just

/Features)[1]

then it'll work

answered Nov 9, 2015 at 19:29

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

1 Comment

Doug Coats Over a year ago

You rock. Thanks. I was like "Why isnt this working "

Collectives™ on Stack Overflow

Update XML Node in SQL Server

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