更新时间:2021-08-25 23:09:19
你必须循环进行,一次一个节点.
You have to do it in a loop, one node at a time.
declare @MyTable table(Id int, MyXMLField xml)
insert into @MyTable values
(1, '<Parent1>
<Parent2>
<Child>test 1</Child>
</Parent2>
<Parent2>
<Child>test 2</Child>
</Parent2>
</Parent1>')
declare @replacement nvarchar(4000) = 'something 1'
declare @Parent2Count int
select @Parent2Count = MyXMLField.value('count(/Parent1/Parent2)', 'int')
from @MyTable
where Id = 1
declare @Node int
set @Node = 1
while @Node <= @Parent2Count
begin
update @MyTable set
MyXMLField.modify('replace value of
(/Parent1/Parent2[sql:variable("@Node")]/Child/text())[1]
with sql:variable("@replacement")')
where Id = 1
set @Node = @Node + 1
end