SQL Server Update Value XML Node

Question

I have 2 tables in SQL Server

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>

Now I am inserting a GUID column into Table1

Table1

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

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

So now the XML would be

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

This will happen for all rows in Table2.

Please help me with the query for this.

Answer 1

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.

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 Fiddle