当XML中某个节点值符合条件时, 更新XML字段中的任意一个节点的值
CREATE TABLE XmlTable (
intcolumn int null,
xmlcolumn xml null
)
GO
insert XmlTable
values (1, '<root>
<node>
<code>01</code>
<content>original_1_1</content>
</node>
<node>
<code>02</code>
<content>original_1_2</content>
</node>
<node>
<code>03</code>
<content>original_1_3</content>
</node>
</root>')
insert XmlTable
values (2, '<root>
<node>
<code>01</code>
<content>original_2_1</content>
</node>
<node>
<code>02</code>
<content>original_2_2</content>
</node>
<node>
<code>03</code>
<content>original_2_3</content>
</node>
</root>')
GO
declare @code varchar(8)
declare @content varchar(128)
set @code = '01'
set @content = 'target_0_1'
update XmlTable set xmlcolumn
.modify('replace value of (/root/node[(code/text()[contains(.,sql:variable("@code"))])]/content/text())[1] with sql:variable("@content")')
where xmlcolumn.exist('/root/node/code/text()[contains(.,sql:variable("@code"))]') = 1
GO
select * from XmlTable
GO
参考:
Microsoft SQL Server 2005 中的 XML 支持