更新具有xml名称空间的sql xml字段

Question

我有一个xml字符串，可以用它读取ID的值

;WITH XMLNAMESPACES
       ('http://collaborate.com/svn/capabilities/tdp/ManageNetworkAndServiceDiagnosticsV4/' AS P, 
'https://collaborate.com/svn/edm/tdp/Test' as p2)
Select @id = feed.xx.value('.','VARCHAR(50)')  
From   @smxml.nodes('/p:initiateTest/p2:Test/p2:id') feed(xx)

这设置了@ID变量，我对其进行了修改，然后将其放回表中。我试过了

;WITH XMLNAMESPACES
('http://collaborate.com/svn/capabilities/tdp/ManageNetworkAndServiceDiagnosticsV4/' AS p, 
'https://collaborate.com/svn/edm/tdp/Test' as p2)
UPDATE CT_GTCS_Temp_XML
SET xmlStartString.modify('replace value of (/p:initiateTest/p2:Test/p2:id with @New_Id)')

BUt I得到了XQuery CT_GTCS_Temp_XML.xmlStartString.modify()：")“的错误。

Answer 1

正如错误信息所建议的，您没有将结束括号放在正确的位置。将其放在XQuery之后，而不是with子句之后：

.....
SET xmlStartString.modify('replace value of 
                                (/p:initiateTest/p2:Test/p2:id/text())[1] 
                           with sql:variable("@New_Id")')