CODEWITHSUNDEEP
sql-serversql-server-2005t-sqlopenxml
Royi Namir
Royi Namir

Reputation: 148744

SQL server OPENXML doesn't work right?

DECLARE @str NVARCHAR(MAX)
SET @str = 
    '<bands >
   <title>my bands</title>

   <band>
      <fullName>beatles</fullName>
      <members>
                 <member age="12">john </member>
                 <member age="13">paul </member>
                 <member age="14">george </member>
                 <member age="15">ringo </member>
           </members>
   </band>


   <band>
      <fullName>doors</fullName>
      <members>
                 <member age="20">jim </member>
                 <member age="50">ray </member>
                 <member age="40">john </member>
                 <member age="30">robby </member>
           </members>
   </band>

</bands>'

query : 

 DECLARE @idoc int
 EXEC sp_xml_preparedocument @idoc OUTPUT, @str

SELECT    *
FROM       OPENXML (@idoc, '/bands/band/members',2)
            WITH (member  NVARCHAR(MAX)      )

my results are :

enter image description here

why is that ?

I expected to see the whole members .... the XPATH /bands/band/members is referencing a node which contains SEVERAL childs.

What am i doing wrong ?

Upvotes: 0

Views: 235

Answers (1)

Eugene S.
Eugene S.

Reputation: 3265

This should give you all of the members

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @str

SELECT    *
FROM
    OPENXML (@idoc, '/bands/band/members/member', 2 )
    WITH 
    (
        member  NVARCHAR(20) '.',
        age int '@age',
        fullName NVARCHAR(20) '../../fullName'
    )

Upvotes: 3

Related Questions