title: Parsing OpenXML with multiple elements of the same name based on attribute
body: <p>Similar to this <a href="https://stackoverflow.com/questions/8013036/parsing-openxml-with-multiple-elements-of-the-same-name">question</a> but I need to parse based on specific attribute. How do you select only one of the elements with the same name based on the attribute value?</p>
  
  <pre><code>DECLARE @doc int
  EXEC sp_xml_preparedocument @doc OUTPUT, N'
  &lt;rootnode&gt;
    &lt;group&gt;
      &lt;id&gt;1&lt;/id&gt;
       &lt;anothernode lang="de"&gt;first string&lt;/anothernode&gt;
       &lt;anothernode lang="en"&gt;second string&lt;/anothernode&gt;
    &lt;/group&gt;
    &lt;group&gt;
      &lt;id&gt;1&lt;/id&gt;
       &lt;anothernode lang="en"&gt;I&lt;/anothernode&gt;
       &lt;anothernode lang="de"&gt;Ich&lt;/anothernode&gt;
    &lt;/group&gt;
  &lt;/rootnode&gt;'
  
  SELECT *
  FROM OPENXML (@doc, 'rootnode/group')
  WITH
  (
      id int 'id',
      anothernode  varchar(30) 'anothernode'
  )
  
  EXEC sp_xml_removedocument @doc
  </code></pre>
  
  <p>Result is:</p>
  
  <pre><code>1   first string
  2   Ich
  </code></pre>
  
  <p>And I need:</p>
  
  <pre><code>1   second string
  2   I
  </code></pre>