SQL Server 2005 xml字段修改

当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 支持