Update XML stored in a XML column in SQL Server

Question

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

marc_s · Accepted Answer · 2013-05-13 04:24:57Z

10

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

answered May 13, 2013 at 4:24

marc_s

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

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

Comments

praveen · Accepted Answer · 2013-05-13 04:39:30Z

3

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

answered May 13, 2013 at 4:39

praveen

12.3k1 gold badge44 silver badges50 bronze badges

Collectives™ on Stack Overflow

Update XML stored in a XML column in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related