Read and parse XML -- tsql
-
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
-
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 tableDECLARE @products TABLE(productID int, product varchar(100));INSERT @products VALUES (229, 'Product1'),(474, 'Product2');-- sample XML formatted as a variableDECLARE @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 XMLWITHparseXML AS(SELECTproductID = 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(SELECTrTxt = '<p1id>'+CAST(p.productID AS varchar(3))+'</p1id>',wTxt = '<p1id>'+product+'</p1id>'FROM parseXML xJOIN @products p ON p.productID=x.productID)SELECT @xml = REPLACE(CAST(@xml AS varchar(max)),rTxt,wTxt) FROM Lkup-- resultsSELECT @xml;"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
XML is stored in a table in a different database. Will try your approach thank you so much.
-
Another option is to shred and reconstruct the xml, here is a quick example with some test data
USE TEEST;GOSET NOCOUNT ON;--/*-- SAMPLE DATA SETIF 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)SELECTNM.N,'Product' + CONVERT(VARCHAR(12),NM.N,0)FROM NUMS NM;-- XML TEMPLATEDECLARE @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(SELECTTXP.PRODUCT_XML_ID,(SELECTPROD.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 curFROM dbo.TBL_PRODUCT_XML PXCROSS APPLY PX.PRODUCT_XML.nodes('tokens/token') AS PROD(DATA)LEFT OUTER JOIN dbo.TBL_PRODUCT005 TPON TP.PRODUCT_ID = PROD.DATA.value('(p1id/text())[1]','INT')WHERE PX.PRODUCT_XML_ID = TXP.PRODUCT_XML_IDFOR XML PATH('token'), ROOT('tokens'), TYPE) AS AFTER_XMLFROM dbo.TBL_PRODUCT_XML TXPGROUP BY TXP.PRODUCT_XML_ID)/* -- UPDATE STATEMENTUPDATE PXMLSET PXML.PRODUCT_XML = BD.AFTER_XMLFROM BASE_DATA BDINNER JOIN dbo.TBL_PRODUCT_XML PXMLON BD.PRODUCT_XML_ID = PXML.PRODUCT_XML_ID;-- */-- SELECT WITH BEFORE AND AFTERSELECTPXML.PRODUCT_XML_ID,PXML.PRODUCT_XML AS BEFORE_XML,BD.AFTER_XMLFROM BASE_DATA BDINNER JOIN dbo.TBL_PRODUCT_XML PXMLON 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