更新时间:2022-01-10 07:21:03
不可能一次在多个地方更新 XML,因此您必须在某种循环中执行此操作.我能想到的***方法是从 Table2
中的 XML 中提取 ID 并与 Table1.ID
连接以生成一个包含 Table2.ID 的临时表
XML 中 Data1
节点的序号位置 (OrdPos
) 和新的 GUID
值.
It is not possible to update the XML in more than one place at a time so you have to do this in a loop of some kind. The best I could come up with was to extract the ID's from the XML in Table2
and join against Table1.ID
to produce a temp table that holds Table2.ID
ordinal position of the Data1
node in the XML (OrdPos
) and the new GUID
value.
然后您可以遍历 XML 列中存在的最大节点数并进行更新.
Then you can loop over the max number of nodes present in the XML column and do the update.
-- Variable used to loop over nodes
declare @I int
-- Temp table to hold the work that needs to be done.
create table #T
(
ID int, -- ID from table2
OrdPos int, -- Ordinal position of node Data1 in root
GUID uniqueidentifier, -- New ID
primary key (OrdPos, ID)
)
-- Shred the XML in Table2, join to Table1 to get GUID
insert into #T(ID, OrdPos, GUID)
select T2.ID,
row_number() over(partition by T2.ID order by D.N) as OrdPos,
T1.GUID
from Table2 as T2
cross apply T2.XMLCol.nodes('Root[1]/Data1') as D(N)
inner join Table1 as T1
on T1.ID = D.N.value('(ID/text())[1]', 'int')
-- Get the max number of nodes in one row that needs to be updated
set @I =
(
select top(1) count(*)
from #T
group by ID
order by 1 desc
)
-- Do the updates in a loop, one level at a time
while @I > 0
begin
update T2
set XMLCol.modify('replace value of (/Root[1]/Data1[sql:variable("@I")]/ID/text())[1]
with sql:column("T.GUID")')
from Table2 as T2
inner join #T as T
on T2.ID = T.ID
where T.OrdPos = @I
set @I = @I - 1
end
drop table #T