我正在尝试使用 SQL Server 2005 中的 XQuery 来更新保存在列中的 xml。这是我需要更新的数据示例。
<Length>3</Length>
<Width>5</Width>
<Depth>6</Depth>
<Area xsi:nil="true" />
<Volume xsi:nil="true" />
我需要将面积和体积设置为不同表中的值。我正在为更新创建 CTE。我省略了其他逻辑，但我已经验证 CTE 包含用于更新的正确数据：
;with Volume (DocumentID, Volume) As
(
Select DocumentID, Volume from tbl
)
并且我正在使用以下 XQuery SQL 语句来尝试更新表。
UPDATE tbl_Archive
SET XML.modify(' declare namespace x="http://www.redacted.com";
replace value of (/x:Document/x:Volume/text())[1]
with sql:column("Volume.Volume")')
From Volume where volume.documentID = tbl_Archive.DocumentID
我有 1 行受到影响，但当我查看 XML 时，它没有改变，而且我不知道需要修复什么才能使其正常工作。如果有什么区别的话，该节点是无类型的。
I am trying to use XQuery in SQL Server 2005 to update xml saved in a column. Here is a sample of the data I need to update.
<Length>3</Length>
<Width>5</Width>
<Depth>6</Depth>
<Area xsi:nil="true" />
<Volume xsi:nil="true" />
I need to set the area and volume to values from a different table. I am creating a CTE for the update. There is other logic that I have omitted, but I have verified that the CTE contains the correct data for the update:
;with Volume (DocumentID, Volume) As
(
Select DocumentID, Volume from tbl
)
and I am using the following XQuery SQL statement to try to update the table.
UPDATE tbl_Archive
SET XML.modify(' declare namespace x="http://www.redacted.com";
replace value of (/x:Document/x:Volume/text())[1]
with sql:column("Volume.Volume")')
From Volume where volume.documentID = tbl_Archive.DocumentID
I get 1 row affected, but when I look at the XML it hasn't changed, and I can't figure out what needs to be fixed to make it work. The node is untyped, if that makes any difference.
发布评论
评论（1）
如果没有要替换的文本，更新将不起作用。XPath
/x:Document/x:Volume/text())[1]将返回一个空集。尝试插入.....
然后您需要删除
nil="true"属性..也许是这样的..
Update wont work if there's no text to replace.. the XPath
/x:Document/x:Volume/text())[1]will return an empty set.Try insert...
..you'll then need to remove the
nil="true"attribute..Something like this maybe..