CODEWITHSUNDEEP
sql-serverxml
Doug Coats
Doug Coats

Reputation: 7107

Update XML Node in SQL Server

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

Upvotes: 2

Views: 31

Answers (1)

marc_s
marc_s

Reputation: 754230

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

Upvotes: 2

Related Questions