我在 SQL Server 中有 2 个表
Table1
ID - Name - Phone
1 HK 999
2 RK 888
3 SK 777
4 PK 666
Table2
ID - XMLCol
1 XMLVal1
XMLVal1
<Root>
<Data1>
<ID>1</ID>
<Name>HK</Name>
</Data1>
<Data1>
<ID>2</ID>
<Name>RK</Name>
</Data1>
</Root>
现在我将 GUID 列插入到Table1
Table1
ID - Name - Phone - GUID
1 HK 999 HJHHKHJHJHKJH8788
2 RK 888 OONMNy7878HJHJHSD
3 SK 777 POMSDHBSNB775SD87
4 PK 666 HRBMASJMN76448NDN
In Table2XML 列，我想更新ID节点具有新的 GUID 值而不更改元素名称。
所以现在 XML 是
<Root>
<Data1>
<ID>HJHHKHJHJHKJH8788</ID>
<Name>HK</Name>
</Data1>
<Data1>
<ID>OONMNy7878HJHJHSD</ID>
<Name>RK</Name>
</Data1>
</Root>
这将发生在所有行中Table2.
请帮我解决这个问题。
一次不可能在多个位置更新 XML，因此您必须在某种循环中执行此操作。我能想到的最好办法是从 XML 中提取 IDTable2并加入反对Table1.ID生成一个临时表来保存Table2.ID的顺序位置Data1XML 中的节点 (OrdPos）和新的GUID value.
然后，您可以循环遍历 XML 列中存在的最大节点数并进行更新。
-- 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
SQL小提琴 http://sqlfiddle.com/#!3/987e0/2
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
