title: Parsing OpenXML with multiple elements of the same name
body: <p>I have a data structure as such:</p>
  
  <pre><code>&lt;rootnode&gt;
    &lt;group&gt;
      &lt;id&gt;1&lt;/id&gt;
       &lt;anothernode&gt;first string&lt;/anothernode&gt;
       &lt;anothernode&gt;second string&lt;/anothernode&gt;
    &lt;/group&gt;
   &lt;group&gt;
     &lt;id&gt;2&lt;/id&gt;
       &lt;anothernode&gt;third string&lt;/anothernode&gt;
       &lt;anothernode&gt;fourth string&lt;/anothernode&gt;
    &lt;/group&gt;
  &lt;/rootnode&gt;
  </code></pre>
  
  <p>And the following code:</p>
  
  <pre><code>EXEC sp_xml_preparedocument @index OUTPUT, @XMLdoc
  
  SELECT *
  FROM OPENXML (@index, 'rootnode/group')
  WITH 
  (
    id int 'id',
    anothernode varchar(30) 'anothernode'
  )
  </code></pre>
  
  <p>which gives me the result</p>
  
  <pre><code>id | anothernode
  ————————————————
  1  | first string
  2  | third string
  </code></pre>
  
  <p>How can I get it to show this result instead where all four strings are showing instead?</p>
  
  <pre><code>id | anothernode
  ————————————————
  1  | first string
  1  | second string
  2  | third string
  2  | fourth string
  </code></pre>