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:Envelope
xmlns: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 #Temp
cross apply col.nodes('soap:Envelope/soap:Body/m:GetStockPrice') AS T(c)

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:Envelope
xmlns: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 #Temp
cross apply col.nodes('soap:Envelope/soap:Body/m:GetStockPrice') AS T(c)

CREATE PROCEDURE dbo.ParseXML

     @strXML NVARCHAR(MAX)
    ,@schemanode NVARCHAR(255)
    ,@rootnode NVARCHAR(255)

AS
BEGIN

    /*
    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 Result

END