title: Use SQL Server to get all the data from XML nodes named the same
body: <p>I have an XML file where the nodes that I need the data from are all named the same.  I understand how to access the first (or second record) so the following query only gives me the second author (the <code>&lt;a1&gt;</code> tag).  How do I get all the authors as a single column ? </p>
  
  <pre><code>      DECLARE @MyXML XML
        SET @MyXML = '&lt;refworks&gt;
              &lt;reference&gt;
                 &lt;rt&gt;Journal Article&lt;/rt&gt; 
                 &lt;sr&gt;Print(0)&lt;/sr&gt; 
                 &lt;id&gt;869&lt;/id&gt; 
                 &lt;a1&gt;Aabye,Martine G.&lt;/a1&gt; 
                 &lt;a1&gt;Hermansen,Thomas Stig&lt;/a1&gt; 
                 &lt;a1&gt;Ruhwald,Morten&lt;/a1&gt; 
                 &lt;a1&gt;PrayGod,George&lt;/a1&gt; 
                 &lt;a1&gt;Faurholt-Jepsen,Daniel&lt;/a1&gt; 
                 &lt;a1&gt;Jeremiah,Kidola&lt;/a1&gt; 
                 &lt;a1&gt;Faurholt-Jepsen,Maria&lt;/a1&gt; 
                 &lt;a1&gt;Range,Nyagosya&lt;/a1&gt; 
             &lt;/reference&gt;
           &lt;/refworks&gt;'
  
        SELECT 
            author.value('(a1)[2]', 'varchar(MAX)') AS 'Author'
        FROM @MyXML.nodes('/refworks/reference') AS ref(author)
  </code></pre>