![](/wp-content/uploads/legacy/9f173408675304407eeacbcac0802ec837c6b5f1/19214.jpg)
Now parsed into tabular form the data can be queried/joined like any other set of data!
Now parsed into tabular form the data can be queried/joined like any other set of data!
CREATE PROCEDURE dbo.ParseXMLtoTable @strXML AS XML ,@rootnode NVARCHAR(255) AS BEGIN SET NOCOUNT ON DECLARE @strText AS NVARCHAR(MAX) ,@idoc INT ,@id INT ,@parentid INT IF OBJECT_ID('tempdb..#ChildList') IS NOT NULL DROP TABLE #ChildList CREATE TABLE #ChildList ( [RowNum] INT IDENTITY(1,1) NOT NULL, [parentid] INT NULL, [id] INT NULL, PRIMARY KEY (RowNum)) IF OBJECT_ID('tempdb..#NodeList') IS NOT NULL DROP TABLE #NodeList CREATE TABLE #NodeList ( [RowNum] INT NOT NULL, [id] INT NULL, [parentid] INT NULL, [nodetype] INT NULL, [localname] NVARCHAR(MAX) NULL, [text] NVARCHAR(MAX) NULL, PRIMARY KEY (RowNum)) SET @id = 1 SET @parentid = NULL /* Get rid of tabs and extra spaces */ SET @strText = CAST(@strXML AS NVARCHAR(MAX)) SET @strText = REPLACE( REPLACE( REPLACE( REPLACE( @strText ,' ',' '+CHAR(7)) ,CHAR(7)+' ','') ,CHAR(7),'') ,CHAR(9),' ') SET @strXML = CONVERT(XML,@strText) /* Validate the XML */ EXEC sp_xml_preparedocument @idoc OUTPUT, @strXML /* Parse the XML data */ ;WITH cteChildren (parentid, id) AS ( SELECT CAST(p1.parentid AS INT) AS parentid ,CAST(p1.id AS INT) AS id FROM OPENXML (@idoc,@rootnode,2) AS p1 UNION ALL SELECT CAST(p2.parentid AS INT) AS parentid ,CAST(p2.id AS INT) AS id FROM OPENXML (@idoc,@rootnode,2) AS p2 INNER JOIN cteChildren AS cte ON cte.id = p2.ParentID WHERE p2.parentid = @parentid ) INSERT INTO #ChildList SELECT parentid ,id FROM cteChildren INSERT INTO #NodeList SELECT #ChildList.RowNum ,xmllist.id ,xmllist.parentid ,xmllist.nodetype ,xmllist.localname ,CAST(xmllist.[text] AS NVARCHAR(MAX)) AS [text] FROM #ChildList INNER JOIN OPENXML (@idoc,@rootnode,2) AS xmllist ON #ChildList.id = xmllist.id WHERE #ChildList.RowNum > 0 /* Display the results */ IF OBJECT_ID('dbo.XML_Nodes') IS NOT NULL DROP TABLE dbo.XML_Nodes ;WITH RecursiveNodes(RowNum,id,parentid,nodepath,localname,[text],nodetype) AS ( SELECT #NodeList.RowNum ,#NodeList.id ,#NodeList.parentid ,CAST('/' + REPLACE(REPLACE(REPLACE(REPLACE(#NodeList.localname,'&',''),'?',''),' ',''),'.','') AS NVARCHAR(255)) AS nodepath ,#NodeList.localname ,CAST(#NodeList.[text] AS NVARCHAR(MAX)) AS [text] ,0 AS nodetype FROM #ChildList INNER JOIN #NodeList ON #ChildList.id = #NodeList.id WHERE #NodeList.parentid IS NULL AND #ChildList.RowNum > 0 AND #NodeList.RowNum > 0 UNION ALL SELECT n.RowNum ,n.id ,n.parentid ,CAST(r.nodepath + '/'+ REPLACE(REPLACE(REPLACE(REPLACE(n.localname,'&',''),'?',''),' ',''),'.','') AS NVARCHAR(255)) AS nodepath ,n.localname ,n.[text] ,n.nodetype FROM #NodeList AS n INNER JOIN RecursiveNodes AS r ON n.parentid = r.id WHERE n.RowNum > 0 AND r.RowNum > 0 AND n.parentid >= 0 ) SELECT ROW_NUMBER() OVER (ORDER BY Result.RowNum) AS RowNum ,Result.id ,Result.parentid ,Result.nodepath ,Result.nodetype ,Result.nodename ,Result.property ,Result.value ,Result.nodecontents INTO dbo.XML_Nodes FROM ( SELECT rn.RowNum ,rn.id ,rn.parentid ,rn.nodepath ,(CASE WHEN rn.nodetype = 0 THEN 'Root' WHEN rn.nodetype = 1 THEN 'Node' WHEN rn.nodetype = 2 THEN 'Property' ELSE 'Data' END) AS nodetype ,(CASE WHEN rn.nodetype = 0 THEN rn.localname WHEN rn.nodetype = 1 THEN rn.localname WHEN rn.nodetype = 2 THEN (SELECT TOP(1) localname FROM RecursiveNodes WHERE id = rn.parentid) ELSE NULL END) AS nodename ,(CASE WHEN rn.nodetype = 2 THEN rn.localname ELSE NULL END) AS property ,(CASE WHEN rn.nodetype = 2 THEN (SELECT TOP(1) [text] FROM RecursiveNodes WHERE parentid = rn.id) ELSE NULL END) AS value ,(CASE WHEN rn.nodetype = 1 THEN (SELECT TOP(1) [text] FROM RecursiveNodes WHERE parentid = rn.id) WHEN rn.nodetype = 2 THEN (SELECT TOP(1) [text] FROM RecursiveNodes WHERE parentid = rn.parentid and [text] is not null) ELSE NULL END) AS nodecontents FROM RecursiveNodes AS rn WHERE rn.localname <> '#text' ) AS Result WHERE Result.id >= 0 AND (Result.id = 0 OR property IS NOT NULL OR value IS NOT NULL OR nodecontents IS NOT NULL) SELECT RowNum ,id ,parentid ,nodepath ,nodetype ,nodename ,property ,value ,nodecontents FROM dbo.XML_Nodes END /***** Usage examples *****/ EXEC dbo.ParseXMLtoTable '<AccountDetailsRsp AccountNum="1" AccountStatus="AccountStatus1"> <PlayerInfo PlayerID="1" FirstName="FirstName1" LastName="LastName1"> <AddressList> <PlayerAddress> <Address AddressType="primary" City="City1" State="State1" Zip="Zip1"/> <FutureUse>Example Text1</FutureUse> <Phone PhoneNumber="PhoneNumber1" PhoneType="Type1" /> <Phone PhoneNumber="PhoneNumber2" PhoneType="Type2" /> </PlayerAddress> <PlayerAddress> <Address AddressType="billing" City="City1" State="State1" Zip="Zip1"/> <FutureUse>Example Text2</FutureUse> <Phone PhoneNumber="PhoneNumber1" PhoneType="Type1" /> <Phone PhoneNumber="PhoneNumber2" PhoneType="Type2" /> </PlayerAddress> </AddressList> </PlayerInfo> </AccountDetailsRsp>' ,'AccountDetailsRsp' EXEC dbo.ParseXMLtoTable '<items> <item id="0001" type="Donut"> <name>Cake</name> <ppu>0.55</ppu> <batter id="1001">Regular</batter> <batter id="1002">Chocolate</batter> <batter id="1003">Blueberry</batter> <topping id="5001">None</topping> <topping id="5002">Glazed</topping> <topping id="5005">Sugar</topping> <topping id="5006">Sprinkles</topping> <topping id="5003">Chocolate</topping> <topping id="5004">Maple</topping> </item> </items>' ,'items'