使用 T-SQL 更新多个 XML 节点 [英] Updating multiple XML nodes using T-SQL
问题描述
可能的重复:
TSQL 2005,XML DML - 一次更新两个值?一个>
假设我有一个包含 XML 列的数据库表.
Say I have a database table with an XML column.
对于每一行,XML 的结构相同(或相似),但标签的内容不同.
For each row, the XML is structured the same (or similarly) but the content of the tags is different.
结构看起来像这样:
<Parent1>
<Parent2>
<Child>
test 1
</Child>
</Parent2>
<Parent2>
<Child>
test 2
</Child>
</Parent2>
</Parent1>
我想对结构中的每个子"节点进行一些处理,并用结果更新它们.
I want to do some processing on each 'Child' node in the structure and update them with the results.
例如,我想用something"这个词替换test"这个词.
For example, I want to replace the word 'test' with the word 'something'.
所以我不希望两个子节点最终都包含 'something 1'.
So I don't want both child nodes to end up containing 'something 1'.
相反,我希望第一个子节点包含something 1",第二个子节点包含something 2".
Rather, I want the first child node to contain 'something 1' and the second child node to contain 'something 2'.
我可以编写以下内容,一次定位一个单个节点:
I could write the following, to target a single node at a time:
DECLARE @replacement NVARCHAR(4000) = 'something 1'
UPDATE MyTable
SET MyXMLField.modify('replace value of (Parent1[1]/Parent2[1]/Child[1]/text())[1] with sql:variable("@replacement")')
WHERE Id = 1
但是是否可以编写一个 T-SQL 语句来处理 多个 节点,与 XPath 匹配,并根据函数的结果单独更新每个节点?
But is it possible to write a T-SQL statement that will process multiple nodes, matched by an XPath, and update each of them individually, according to, say, the result of a function?
推荐答案
你必须循环进行,一次一个节点.
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
这篇关于使用 T-SQL 更新多个 XML 节点的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!