.value('.','varchar(whatever)') 

.value('(./text())[1]','varchar(whatever)')

CREATE PROCEDURE dbo.ParseXML

     @strXML AS XML
    ,@rootnode NVARCHAR(255)

AS
BEGIN

	/*

	EXEC dbo.ParseXML
		'<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.ParseXML
		'<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'

	*/
    
    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),
        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


    /* 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 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
            
    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 */
                
    ;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
    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)
            
END

RowNum    id    parentid    nodepath    nodetype    nodename    property    value    nodecontents
1    0    NULL    /items    Root    items    NULL    NULL    NULL
2    3    2    /items/item/id    Property    item    id    0001    NULL
3    4    2    /items/item/type    Property    item    type    Donut    NULL
4    5    2    /items/item/name    Node    name    NULL    NULL    Cake
5    6    2    /items/item/ppu    Node    ppu    NULL    NULL    0.55
6    8    7    /items/item/batter/id    Property    batter    id    1001    Regular
7    11    10    /items/item/batter/id    Property    batter    id    1002    Chocolate
8    14    13    /items/item/batter/id    Property    batter    id    1003    Blueberry
9    17    16    /items/item/topping/id    Property    topping    id    5001    None
10    20    19    /items/item/topping/id    Property    topping    id    5002    Glazed
11    23    22    /items/item/topping/id    Property    topping    id    5005    Sugar
12    26    25    /items/item/topping/id    Property    topping    id    5006    Sprinkles
13    29    28    /items/item/topping/id    Property    topping    id    5003    Chocolate
14    32    31    /items/item/topping/id    Property    topping    id    5004    Maple