Update xml node value from secondary table using join based current node value

Question

Table dbo.table_a contains a XML column foo. Every row has a XML-tree in the a XML-column. Secondary table dbo.table_b contains old value old_val and new value new_val

The XML-tree looks like this

<root>
  <Customer>
    <Name>ACME</Name>
  </Customer>
  <Def>
    <Enabled>true</Enabled>
    <CIds>
      <Id>ABC</Id>
      <Id>DEF</Id>
    </CIds>
  </Def>
</root>

Please Note that /root/Def/CIds can contain zero or several child nodes called Id.

dbo.table_b looks like this

+---------+---------+
| old_val | new_val |
+---------+---------+
| ABC     |     123 |
| DEF     |     456 |
+---------+---------+

I need to replace the value for every Id by joining the current xml node value with dbo.table_b on old_val and replacing the xml node value with new_val. ABC should be replace with 123 and DEF with 456

Answer 1

There is no easy way to achieve this, due to the fact that even now, in 2018, the replace value of XML method still does not support modification of multiple nodes.

The simplest approach is the insert+delete tactic, as in the following example:

declare @TableA table (
    Id int identity(1,1) primary key,
    XData xml not null
);

declare @TableB table (
    OldValue varchar(50),
    NewValue int
);

insert into @TableA (XData)
select N'<root>
  <Customer>
    <Name>ACME</Name>
  </Customer>
  <Def>
    <Enabled>true</Enabled>
    <CIds>
      <Id>ABC</Id>
      <Id>DEF</Id>
    </CIds>
  </Def>
</root>';

insert into @TableB (OldValue, NewValue)
values
    ('ABC', 123),
    ('DEF', 456);

-- Before
select * from @TableA;

update a set XData.modify('insert sql:column("sq.NewData") after /root[1]/Def[1]/CIds[1]')
from @TableA a
    cross apply (
        select b.NewValue as [Id]
        from @TableB b
        where a.XData.exist('/root/Def/CIds/Id[text() = sql:column("b.OldValue")]') = 1
        for xml path(''), type, root('CIds')
    ) sq(NewData);

update a set XData.modify('delete /root[1]/Def[1]/CIds[1]')
from @TableA a;

-- After
select * from @TableA;

Its weakness is that it reconstructs the entire /CIds node, so if you have any additional data in it, like attributes, it might be too cumbersome to recreate. In this case you might have better success with a FLWOR update, but they tend to be quite slow, compared to other options.

Alternatively, you might run a series of atomic updates in a loop. As unpleasant as it may sound, it will actually work, especially if your actual XML is much more complex than the provided example.

Answer 2

I ended up with looping and updating. Although @Roger Wolf's code snippet worked I couldn't make it work in my environment.

declare @I int
declare @old varchar(50)
declare @new varchar(50)

select @I = max([foo].value('count(/root/Def/CustomerIds/CIds)', 'int'))
FROM dbo.table_a where [foo].exist('(count(/root/Def/CustomerIds/CIds/Id)') = 1

while @I > 0 
begin

select @old = [foo].value('(/count(/root/Def/CustomerIds/CIds/Id)[sql:variable("@I")][1]', 'varchar(50)') 
from dbo.table_a
where [foo].exist('(/count(/root/Def/CustomerIds/CIds/Id)[sql:variable("@I")]') = 1

set @new = dbo.ConvertOldNew(@old)


  update dbo.table_a
  set [foo].modify
    ('replace value of ((/count(/root/Def/CustomerIds/CIds/Id)[sql:variable("@I")]/text())[1]
      with (sql:variable("@new"))')
  where [foo].exist('(/count(/root/Def/CustomerIds/CIds/Id)[sql:variable("@I")]') = 1
  set @I = @I - 1
end

Please note that dbo.ConvertOldNew is a function returning the new value based on the old value.