Parsing OpenXML with multiple elements of the same name

Question

I have a data structure as such:

<rootnode>
  <group>
    <id>1</id>
     <anothernode>first string</anothernode>
     <anothernode>second string</anothernode>
  </group>
 <group>
   <id>2</id>
     <anothernode>third string</anothernode>
     <anothernode>fourth string</anothernode>
  </group>
</rootnode>

And the following code:

EXEC sp_xml_preparedocument @index OUTPUT, @XMLdoc

SELECT *
FROM OPENXML (@index, 'rootnode/group')
WITH 
(
  id int 'id',
  anothernode varchar(30) 'anothernode'
)

which gives me the result

id | anothernode
————————————————
1  | first string
2  | third string

How can I get it to show this result instead where all four strings are showing instead?

id | anothernode
————————————————
1  | first string
1  | second string
2  | third string
2  | fourth string

Mikael Eriksson · Accepted Answer · 2014-03-20 14:59:17Z

12

SELECT *
FROM OPENXML (@index, 'rootnode/group/anothernode')
WITH 
(
  id int '../id',
  anothernode varchar(30) '.'
)

Or you can use the XML datatype instead like this:

SELECT G.N.value('(id/text())[1]', 'int') AS id,
       A.N.value('text()[1]', 'varchar(30)') AS anothernode
FROM @XMLDoc.nodes('rootnode/group') AS G(N)
  CROSS APPLY G.N.nodes('anothernode') AS A(N)

edited Mar 20, 2014 at 14:59

answered Nov 4, 2011 at 20:36

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MSpeed Over a year ago

Thank you very much! Would you suggest either one more than the other?

MSpeed Over a year ago

Hmm this seems to output one row as "1 | first string second string" is there any way I can get "first string" and "second string" on two separate rows both with the same ID?

Mikael Eriksson Over a year ago

@billynomates, I would suggest that you use the second version (XML data type).

Mikael Eriksson Over a year ago

@billynomates The query works fine for me. Test it here: data.stackexchange.com/stackoverflow/q/116679

Collectives™ on Stack Overflow

Parsing OpenXML with multiple elements of the same name

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related