CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlsql-server-2012
KeyboardFriendly
KeyboardFriendly

Reputation: 1798

Update XML stored in a XML column in SQL Server

I have a sample table in SQL Server 2012. I am running some queries against but the .modify() XQuery method is executing but not updating.

Here is the table

Xml Table

For this just trying to update settings to 'NewTest'

Xml Snippet

This will execute but nothing is updating! Thanks for any help!

Not Update Snippet

Upvotes: 8

Views: 56308

Answers (2)

praveen
praveen

Reputation: 12271

You should declare a namespace in your update syntax .Try the below syntax

Declare @Sample table
(xmlCol xml)

Insert into @Sample
values
('<dev:Doc xmlns:dev="http://www.w3.org/2001/XMLSchema" 
                       SchemaVersion="0.1" Settings="Testing" Ttile="Ordering">
        <Person id="1">
            <FirstName>Name</FirstName>
        </Person>
      </dev:Doc>')
 Select * from @Sample
 Update @Sample
 SET xmlCol.modify(
                  'declare namespace ns="http://www.w3.org/2001/XMLSchema";
                   replace value of (/ns:Doc/@Settings)[1]
                   with "NewTest"')

 Select * from @Sample

Upvotes: 3

marc_s
marc_s

Reputation: 754240

Since there is a XML namespace (xmlns:dev="http://www.w3.org/2001/XMLSchema") in your XML document, you must inlcude that in your UPDATE statement!

Try this:

;WITH XMLNAMESPACES(DEFAULT 'http://www.w3.org/2001/XMLSchema')
UPDATE XmlTable
SET XmlDocument.modify('replace value of (/Doc/@Settings)[1] with "NewTest"')
WHERE XmlId = 1

Upvotes: 10

Related Questions