SQL Server 更新值 XML 节点

2021-10-01 00:00:00 xml sql sql-server sqlxml

我在 SQL Server 中有 2 个表

I have 2 tables in SQL Server

表 1

   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

Now I am inserting a GUID column into Table1

表 1

   ID   - Name  - Phone  - GUID
   1      HK      999      HJHHKHJHJHKJH8788 
   2      RK      888      OONMNy7878HJHJHSD
   3      SK      777      POMSDHBSNB775SD87
   4      PK      666      HRBMASJMN76448NDN

在 Table2 XML 列中，我想用新的 GUID 值更新 ID 节点而不更改元素名称.

In Table2 XML column, I want to update the ID node with the new GUID value without changing the element name.

所以现在 XML 将是

So now the XML would be

   <Root>
    <Data1>
     <ID>HJHHKHJHJHKJH8788</ID>
     <Name>HK</Name> 
     </Data1>
    <Data1>
     <ID>OONMNy7878HJHJHSD</ID>
     <Name>RK</Name>
     </Data1>
    </Root>

Table2 中的所有行都会发生这种情况.

This will happen for all rows in Table2.

请帮我查询一下.

推荐答案

不可能一次在多个地方更新 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

SQL 小提琴

相关文章