May 1, 2016 at 8:45 pm
Hi All,
I want to read the below xml and wherever i find p1id tag in the xml (as highlighted below) i need to replace it with respective product name from a lookup table and then generate the same xml with same tag this time with product name.
Sample XML:
<tokens>
<token>
<typeid>8</typeid>
</token>
<token>
<typeid>2</typeid>
<op>*</op>
<p1id>229</p1id>
<cur>USD</cur>
<curid>1</curid>
</token>
<token>
<typeid>8</typeid>
<op>-</op>
</token>
<token>
<typeid>2</typeid>
<p1id>474</p1id>
<cur>USD</cur>
<curid>1</curid>
</token>
<token>
<typeid>1</typeid>
<op>*</op>
<p1>3</p1>
</token>
</tokens>
Output:
<tokens>
<token>
<typeid>8</typeid>
</token>
<token>
<typeid>2</typeid>
<op>*</op>
<p1id>Product1</p1id>
<cur>USD</cur>
<curid>1</curid>
</token>
<token>
<typeid>8</typeid>
<op>-</op>
</token>
<token>
<typeid>2</typeid>
<p1id>Product2</p1id>
<cur>USD</cur>
<curid>1</curid>
</token>
<token>
<typeid>1</typeid>
<op>*</op>
<p1>3</p1>
</token>
</tokens>
Please let me know how efficiently i can handle it in tsql.
Thanks
Sam
May 1, 2016 at 10:18 pm
What format is the XML in? Is it in a table? An external file?
If you can handle it as a variable this will do the trick.
-- your lookup table
DECLARE @products TABLE(productID int, product varchar(100));
INSERT @products VALUES (229, 'Product1'),(474, 'Product2');
-- sample XML formatted as a variable
DECLARE @xml XML =
'<tokens>
<token>
<typeid>8</typeid>
</token>
<token>
<typeid>2</typeid>
<op>*</op>
<p1id>229</p1id>
<cur>USD</cur>
<curid>1</curid>
</token>
<token>
<typeid>8</typeid>
<op>-</op>
</token>
<token>
<typeid>2</typeid>
<p1id>474</p1id>
<cur>USD</cur>
<curid>1</curid>
</token>
<token>
<typeid>1</typeid>
<op>*</op>
<p1>3</p1>
</token>
</tokens>';
-- how to update the XML
WITH
parseXML AS
(
SELECT
productID = x2.x.value('(p1id/text())[1]','int')
--NodePosition = ROW_NUMBER() OVER (ORDER BY x2.x.value('(../token)[1]','bit'))
FROM (VALUES (@xml)) x1(x)
CROSS APPLY x1.x.nodes('tokens/token') x2(x)
),
Lkup AS
(
SELECT
rTxt = '<p1id>'+CAST(p.productID AS varchar(3))+'</p1id>',
wTxt = '<p1id>'+product+'</p1id>'
FROM parseXML x
JOIN @products p ON p.productID=x.productID
)
SELECT @xml = REPLACE(CAST(@xml AS varchar(max)),rTxt,wTxt) FROM Lkup
-- results
SELECT @xml;
-- Itzik Ben-Gan 2001
May 1, 2016 at 11:01 pm
XML is stored in a table in a different database. Will try your approach thank you so much.
May 2, 2016 at 4:17 am
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;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply