InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 19:34:30 GMT20http://www.sqlservercentral.com/Forums/Topic920046-391-1.aspxHere's a stored procedure that you can use to parse XML. The first example inside the procedure is the XML posted by the original OP. I've included a more comprehensive example so one can see how nodes, properties, and data are parsed into a table.This procedure was designed to run basically one XML input at a time so it may not be optimally efficient when used to parse large batches with large amounts of data. In such case I'd recommend using XQUERY. There's lots of articles on XQUERY on this site. But for a basic XML parser, try this:[code="sql"]CREATE PROCEDURE dbo.ParseXML @strXML NVARCHAR(MAX) ,@schemanode NVARCHAR(255) ,@rootnode NVARCHAR(255)ASBEGIN /* SAMPLE PARAMETERS EXEC dbo.ParseXML N'<somaResponse xmlns:ns="http://sum.com"><ns:return>3</ns:return></somaResponse>' ,'root' ,'/somaResponse' EXEC dbo.ParseXML N'<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <soapenv:Body> <Reports xmlns="http://www.sample.com/schemas"> <ReportWrapper type="report"> <TransactionID>123456789</TransactionID> <ReportHeader> <ReportName /> <DistrictContacts> <District ContactName="North">George Washington</District> <District ContactName="South">John Adams</District> <District ContactName="East">Thomas Jefferson</District> <District ContactName="West">James Madison</District> </DistrictContacts> </ReportHeader> <Management> <ManagerName type="subject"> <Prefix>Mr</Prefix> <FirstName>John</FirstName> <SurName>Dilinger</SurName> <Suffix /> </ManagerName> <DemographicDetail> <GovernmentId issuingAuthority="SSN">111-11-1111</GovernmentId> <DateOfBirth>02/03/1984</DateOfBirth> <Gender>M</Gender> </DemographicDetail> </Management> </ReportWrapper> </Reports> </soapenv:Body> </soapenv:Envelope>' ,'Reports' ,'/ReportWrapper' */ SET NOCOUNT ON DECLARE @idoc INT ,@id INT ,@parentid INT ,@SoapEnvOpen NVARCHAR(MAX) ,@SoapEnvClose NVARCHAR(MAX) ,@SoapBodyOpen NVARCHAR(MAX) ,@SoapBodyClose NVARCHAR(MAX) ,@SchemaNodeOpen NVARCHAR(MAX) ,@SchemaNodeClose NVARCHAR(MAX) ,@checkSoap INT ,@checkSchema INT ,@checkXMLHeader INT ,@XMLHeader NVARCHAR(MAX) 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), UNIQUE (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), UNIQUE (RowNum)) SET @id = 1 SET @parentid = NULL /* Check to see if any XML exists */ SET @strXML = NULLIF(@strXML,'') IF @strXML IS NULL BEGIN SELECT NULL AS RowNum ,NULL AS id ,NULL AS parentid ,NULL AS nodepath ,NULL AS nodetype ,NULL AS nodename ,NULL AS property ,NULL AS value ,NULL AS nodecontents ,'No XML to process' AS XMLStatus RETURN END ELSE BEGIN -- Get rid of tabs, extra spaces, and other extraneous characters IF CHARINDEX('<',@strXML,1) > 0 BEGIN SET @strXML = REPLACE(@strXML,'<','<') SET @strXML = REPLACE(@strXML,'>','>') END SET @strXML = REPLACE(@strXML,CHAR(9),' ') -- convert tabs to spaces SET @strXML = REPLACE( REPLACE( REPLACE( @strXML ,' ',' '+CHAR(7)) ,CHAR(7)+' ','') ,CHAR(7),'') SET @strXML = REPLACE(@strXML,'> <','><') END /* Check to see if the XML has a header */ SET @checkXMLHeader = CHARINDEX('<?xml version',@strXML,0) /* If the XML has a header then remove it */ IF @checkXMLHeader > 0 BEGIN SET @XMLHeader = SUBSTRING(@strXML,CHARINDEX('<?xml version',@strXML,0),CHARINDEX('>',@strXML,0)) SET @strXML = REPLACE(@strXML,@XMLHeader,'') END /* Check to see if the XML has a SOAP wrapper */ SET @checkSoap = CHARINDEX('<soapenv:Envelope',@strXML,0) /* If the XML has a SOAP wrapper then remove it */ IF @checkSoap > 0 BEGIN SET @SoapEnvOpen = SUBSTRING(@strXML,CHARINDEX('<soapenv:Envelope',@strXML,0),CHARINDEX('>',@strXML,0)) SET @strXML = REPLACE(@strXML,@SoapEnvOpen,'') SET @SoapBodyOpen = SUBSTRING(@strXML,CHARINDEX('<soapenv:Body',@strXML,0),CHARINDEX('>',@strXML,0)) SET @strXML = REPLACE(@strXML,@SoapBodyOpen,'') SET @SoapEnvClose = SUBSTRING(@strXML,CHARINDEX('</soapenv:Envelope>',@strXML,0),LEN('</soapenv:Envelope>')) SET @strXML = REPLACE(@strXML,@SoapEnvClose,'') SET @SoapBodyClose = SUBSTRING(@strXML,CHARINDEX('</soapenv:Body>',@strXML,0),LEN('</soapenv:Body>')) SET @strXML = REPLACE(@strXML,@SoapBodyClose,'') END /* Check to see if the XML has a schema definition node */ SET @checkSchema = CHARINDEX('<'+@schemanode,@strXML,0) /* If a schema definition node exists remove it */ IF @checkSchema > 0 BEGIN SET @SchemaNodeOpen = SUBSTRING(@strXML,CHARINDEX('<'+@schemanode,@strXML,0),CHARINDEX('>',@strXML,0)) SET @strXML = REPLACE(@strXML,@SchemaNodeOpen,'') SET @SchemaNodeClose = SUBSTRING(@strXML,CHARINDEX('</'+@schemanode+'>',@strXML,0),LEN('</'+@schemanode+'>')) SET @strXML = REPLACE(@strXML,@SchemaNodeClose,'') END BEGIN TRY EXEC sp_xml_preparedocument @idoc OUTPUT, @strXML END TRY BEGIN CATCH BEGIN SELECT NULL AS RowNum ,NULL AS id ,NULL AS parentid ,NULL AS nodepath ,NULL AS nodetype ,NULL AS nodename ,NULL AS property ,NULL AS value ,NULL AS nodecontents ,'Invalid XML' AS XMLStatus RETURN END END CATCH /* Parse the XML to get the nodes */ ;WITH cte 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 JOIN cte ON CAST(cte.id AS INT) = CAST(p2.ParentID AS INT) WHERE CAST(p2.parentid AS INT) = @parentid ) INSERT INTO #ChildList SELECT * FROM cte /* Parse the nodes to get the data */ 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 formatted results */ ;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 ,'OK' AS XMLStatus 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 ResultEND[/code]Mon, 15 Oct 2012 14:49:18 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic920046-391-1.aspxHi.How can i parse this xml (I invoke a webservice and i got this return):<ns:somaResponse xmlns:ns="http://sum.com"><ns:return>3</ns:return></ns:somaResponse>I used this example (from this post), but doesn't work.Anyone can help me ?ThanksSun, 14 Oct 2012 18:56:35 GMTmob.zanottihttp://www.sqlservercentral.com/Forums/Topic920046-391-1.aspxSorry, I found out...[code="sql"]if object_id('tempdb.dbo.#Temp') is not null drop table dbo.#Temp CREATE TABLE #Temp (col xml)INSERT #Temp (col)select '<?xml version="1.0"?><soap:Envelopexmlns:soap="http://www.w3.org/2001/12/soap-envelope"soap:encodingStyle="http://www.w3.org/2001/12/soap-encoding"> <soap:Body xmlns:m="http://www.example.org/stock"> <m:GetStockPrice> <m:StockName>0.018</m:StockName> </m:GetStockPrice> </soap:Body></soap:Envelope>';WITH XMLNAMESPACES ( 'http://www.w3.org/2001/12/soap-envelope' as "soap", 'http://www.example.org/stock' as "m")select T.c.value('m:StockName[1]','decimal(8,3)')from #Tempcross apply col.nodes('soap:Envelope/soap:Body/m:GetStockPrice') AS T(c)[/code]Tue, 11 May 2010 15:34:46 GMTfayilthttp://www.sqlservercentral.com/Forums/Topic920046-391-1.aspxCan you guys help me with this XML?[code="sql"]if object_id('tempdb.dbo.#Temp') is not null drop table dbo.#Temp CREATE TABLE #Temp (col xml)INSERT #Temp (col)select '<?xml version="1.0"?><soap:Envelopexmlns:soap="http://www.w3.org/2001/12/soap-envelope"soap:encodingStyle="http://www.w3.org/2001/12/soap-encoding"> <soap:Body xmlns:m="http://www.example.org/stock"> <m:GetStockPrice> <m:StockName>0.018</m:StockName> </m:GetStockPrice> </soap:Body></soap:Envelope>'select T.c.value('m:StockName[1]','decimal(8,3)')from #Tempcross apply col.nodes('soap:Envelope/soap:Body/m:GetStockPrice') AS T(c)[/code]I keep getting "The name "soap" does not denote a namespace." error.How do I access nodes with namespaces?ThanksTue, 11 May 2010 15:16:34 GMTfayilt