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" />
1 Answer
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);
8 Comments
userx
I am just trying to run update dbo.TableA set a.[XML] = REPLACE(convert (varchar(max),a.[XML]), b.[key], b.[value]) as [xml] from dbo.tablea a inner join dbo.tableb b on a.id = b.id but it says invalid syntax near 'as', isn't it intending to do the same operation ?
Thom A
You don't alias a column in an
UPDATE statement, @GsabThom A
I've added an
UPDATE version for you, @Gsabuserx
@Larnu- fixed the statement like update dbo.TableA set [XML] = REPLACE(convert (varchar(max),a.[XML]), b.[key], b.[value]) from dbo.tablea a inner join dbo.tableb b on a.id = b.id , but what i have observed is that only the first key got replaced with its value and not the second
Thom A
It works fine for me, @Gsab: db<>fiddle
|
REPLACE?