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 For this just trying t

Solution 1:

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

Solution 2:

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

Declare @Sampletable
(xmlCol xml)

Insertinto @Samplevalues
('<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 @SampleUpdate @SampleSETxmlCol.modify(
                  'declare namespace ns="http://www.w3.org/2001/XMLSchema";
                   replace value of (/ns:Doc/@Settings)[1]
                   with "NewTest"')

 Select * from @Sample