更新存储在SQL Server中的XML列中的XML

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.

我在SQL Server 2012中有一个示例表。我正在运行一些查询但是.modify（）XQuery方法正在执行但没有更新。

Here is the table

这是表格

For this just trying to update settings to 'NewTest'

为此，只是尝试将设置更新为“NewTest”

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

这将执行但没有更新！谢谢你的帮助！

2 个解决方案

#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!

由于XML文档中有XML命名空间（xmlns：dev =“http://www.w3.org/2001/XMLSchema”），因此必须在UPDATE语句中包含该命名空间！

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

#2

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