How to replace Strings in XML with values stored in different table in SQL Server?

Question

Suppose there are two tables

TableA

Id XML

1 <A x="$Y" B="$Z" />

TableB

Id Key Value

1  $Y   100

1  $Z   200

Id columns in TableA and TableB can be joined.

The expected output is

TableA

Id XML

1 <A x="100" B="200" />

Answer 1

The link supplied by Tab Alleman does show you the basic idea here. You need to use REPLACE and a Recursive Common Table Expression (rCTE):

CREATE TABLE TableA (Id int,
                     [XML] xml);
CREATE TABLE TableB (Id int,
                     [Key] varchar(5),
                     [Value] varchar(20));
INSERT INTO dbo.TableA (Id,
                        [XML])
VALUES(1,'<A x="$Y" B="$Z" />');

INSERT INTO dbo.TableB (Id,
                        [Key],
                        [Value])
VALUES (1,'$Y','100'),
       (1,'$Z','200');
GO


WITH RNs AS(
    SELECT B.Id,
           B.[Key],
           B.[Value],
           ROW_NUMBER() OVER (PARTITION BY B.Id ORDER BY [Key]) AS RN
    FROM dbo.TableB B),
rCTE AS(
    SELECT A.Id,
           REPLACE(CONVERT(varchar(MAX),A.[XML]),RN.[Key], RN.[Value]) AS [XML],
           RN.RN
    FROM dbo.TableA A
         JOIN RNs RN ON A.Id = RN.Id
    WHERE RN.RN = 1
    UNION ALL
    SELECT r.Id,
           REPLACE(r.[XML],RN.[Key], RN.[Value]) AS [XML],
           RN.RN
    FROM rCTE r
         JOIN RNs RN ON r.Id = RN.Id
                    AND RN.RN = r.RN + 1)
SELECT Id,
       CONVERT(xml,[xml]) AS [XML]
FROM rCTE r
WHERE RN = (SELECT MAX(RN) FROM rCTE E);

GO

DROP TABLE dbo.TableA;
DROP TABLE dbo.TableB;

As an UPDATE statement, this would like this:

WITH RNs AS(
    SELECT B.Id,
           B.[Key],
           B.[Value],
           ROW_NUMBER() OVER (PARTITION BY B.Id ORDER BY [Key]) AS RN
    FROM dbo.TableB B),
rCTE AS(
    SELECT A.Id,
           REPLACE(CONVERT(varchar(MAX),A.[XML]),RN.[Key], RN.[Value]) AS [XML],
           RN.RN
    FROM dbo.TableA A
         JOIN RNs RN ON A.Id = RN.Id
    WHERE RN.RN = 1
    UNION ALL
    SELECT r.Id,
           REPLACE(r.[XML],RN.[Key], RN.[Value]) AS [XML],
           RN.RN
    FROM rCTE r
         JOIN RNs RN ON r.Id = RN.Id
                    AND RN.RN = r.RN + 1)
UPDATE A
SET [XML] = r.[XML]
FROM TableA A
     JOIN rCTE r oN A.Id = r.Id
WHERE RN = (SELECT MAX(RN) FROM rCTE E);