Another option is to shred and reconstruct the xml, here is a quick example with some test data
😎
USE TEEST;
GO
SET NOCOUNT ON;
--/*
-- SAMPLE DATA SET
IF OBJECT_ID(N'dbo.TBL_PRODUCT005') IS NOT NULL DROP TABLE dbo.TBL_PRODUCT005;
CREATE TABLE dbo.TBL_PRODUCT005
(
PRODUCT_ID INT NOT NULL CONSTRAINT PK_DBO_TBL_PRODUCT005_PRODUCT_ID PRIMARY KEY CLUSTERED
,PRODUCT_NAME VARCHAR(100) NOT NULL
);
IF OBJECT_ID(N'dbo.TBL_PRODUCT_XML') IS NOT NULL DROP TABLE dbo.TBL_PRODUCT_XML;
CREATE TABLE dbo.TBL_PRODUCT_XML
(
PRODUCT_XML_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_PRODUCT_XML_PRODUCT_XML_ID PRIMARY KEY CLUSTERED
,PRODUCT_XML XML NOT NULL
);
DECLARE @SAMPLE_SIZE INT = 100000;
;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
INSERT INTO dbo.TBL_PRODUCT005(PRODUCT_ID,PRODUCT_NAME)
SELECT
NM.N
,'Product' + CONVERT(VARCHAR(12),NM.N,0)
FROM NUMS NM;
-- XML TEMPLATE
DECLARE @TXT_XML VARCHAR(8000) = '<tokens>
<token>
<typeid>{{@TYPEID}}</typeid>
</token>
<token>
<typeid>{{@TYPEID}}</typeid>
<op>*</op>
<p1id>{{@P1ID}}</p1id>
<cur>USD</cur>
<curid>{{@CURID}}</curid>
</token>
<token>
<typeid>{{@TYPEID}}</typeid>
<op>-</op>
</token>
<token>
<typeid>{{@TYPEID}}</typeid>
<p1id>{{@P1ID}}</p1id>
<cur>USD</cur>
<curid>{{@CURID}}</curid>
</token>
<token>
<typeid>{{@TYPEID}}</typeid>
<op>*</op>
<p1>{{@P1ID}}</p1>
</token>
</tokens>';
;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
INSERT INTO dbo.TBL_PRODUCT_XML(PRODUCT_XML)
SELECT CONVERT(XML,
REPLACE(
REPLACE(
REPLACE(@TXT_XML,'{{@P1ID}}',CONVERT(VARCHAR(12),NM.N,0))
,'{{@TYPEID}}',CONVERT(VARCHAR(12),ABS(CHECKSUM(NEWID())) % 1000,0))
,'{{@CURID}}',CONVERT(VARCHAR(12),ABS(CHECKSUM(NEWID())) % 10,0))
,0)
FROM NUMS NM;
-- */
;WITH BASE_DATA AS
(
SELECT
TXP.PRODUCT_XML_ID
,(SELECT
PROD.DATA.value('(typeid/text())[1]','INT' ) AS typeid
,PROD.DATA.value('(op/text())[1]' ,'VARCHAR(10)' ) AS op
,TP.PRODUCT_NAME AS p1id
,PROD.DATA.value('(p1/text())[1]' ,'INT' ) AS p1
,PROD.DATA.value('(curid/text())[1]' ,'INT' ) AS curid
,PROD.DATA.value('(cur/text())[1]' ,'VARCHAR(10)' ) AS cur
FROM dbo.TBL_PRODUCT_XML PX
CROSS APPLY PX.PRODUCT_XML.nodes('tokens/token') AS PROD(DATA)
LEFT OUTER JOIN dbo.TBL_PRODUCT005 TP
ON TP.PRODUCT_ID = PROD.DATA.value('(p1id/text())[1]','INT')
WHERE PX.PRODUCT_XML_ID = TXP.PRODUCT_XML_ID
FOR XML PATH('token'), ROOT('tokens'), TYPE) AS AFTER_XML
FROM dbo.TBL_PRODUCT_XML TXP
GROUP BY TXP.PRODUCT_XML_ID
)
/* -- UPDATE STATEMENT
UPDATE PXML
SET PXML.PRODUCT_XML = BD.AFTER_XML
FROM BASE_DATA BD
INNER JOIN dbo.TBL_PRODUCT_XML PXML
ON BD.PRODUCT_XML_ID = PXML.PRODUCT_XML_ID;
-- */
-- SELECT WITH BEFORE AND AFTER
SELECT
PXML.PRODUCT_XML_ID
,PXML.PRODUCT_XML AS BEFORE_XML
,BD.AFTER_XML
FROM BASE_DATA BD
INNER JOIN dbo.TBL_PRODUCT_XML PXML
ON BD.PRODUCT_XML_ID = PXML.PRODUCT_XML_ID;