Geek Answers Handbook

Updating XML stored in an XML column in SQL Server

I have an example table in SQL Server 2012. I am running some queries, but the .modify() method is .modify() but not updating.

Here is the table

Xml table

To do this, just need to update the settings to "NewTest"

Xml snippet

This will be done, but nothing will be updated! Thanks for any help!

Not update snippet

+7
source share
2 answers

Since there is an XML namespace in the XML document ( xmlns:dev="http://www.w3.org/2001/XMLSchema" ), you must add this to your UPDATE !

Try the following:

 ;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
+7
source

You must declare the namespace in the update syntax. Try the syntax below

 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
+3
source

More articles:


All Articles