問題描述

我在 SQL Server 中有 2 個(gè)表

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>

現(xiàn)在我將一個(gè) 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 節(jié)點(diǎn)而不更改元素名稱.

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

所以現(xiàn)在 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 中的所有行都會(huì)發(fā)生這種情況.

This will happen for all rows in Table2.

請(qǐng)幫我查詢一下.

推薦答案

不可能一次在多個(gè)地方更新 XML，因此您必須在某種循環(huán)中執(zhí)行此操作.我能想到的最好方法是從 Table2 中的 XML 中提取 ID 并與 Table1.ID 連接以生成一個(gè)包含 Table2.ID 的臨時(shí)表 XML 中 Data1 節(jié)點(diǎn)的序號(hào)位置 (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 列中存在的最大節(jié)點(diǎn)數(shù)并進(jìn)行更新.

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 小提琴

這篇關(guān)于SQL Server 更新值 XML 節(jié)點(diǎn)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！