title: SQL: How to select multiple xml nodes of the same name into a comma separated list
body: <p>Suppose you had an XML field in a database table that looked like this: </p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>&lt;PEOPLE&gt;
    &lt;PERSON&gt;
      &lt;FIRST_NAME&gt;John&lt;/FIRST_NAME&gt;
      &lt;LAST_NAME&gt;Doe&lt;/LAST_NAME&gt;
    &lt;/PERSON&gt;
    &lt;PERSON&gt;
      &lt;FIRST_NAME&gt;Jane&lt;/FIRST_NAME&gt;
      &lt;LAST_NAME&gt;Doe&lt;/LAST_NAME&gt;
    &lt;/PERSON&gt;
  &lt;/PEOPLE&gt;</code></pre>
  </div>
  </div>
  </p>
  
  <p>And you wanted to get the names of all the people into a comma-separated list (e.g. "John Doe, Jane Doe"). Right now, my SQL query looks like this:</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>SELECT STUFF((
    SELECT ', ' + FIRST_NAME + ' ' + LAST_NAME
    FROM(
      SELECT
        p.value('(./FIRST_NAME)[1]', 'nvarchar(max)') as FIRST_NAME,
        p.value('(./LAST_NAME)[1]', nvarchar(max)') as LAST_NAME
      FROM [TABLE]
      CROSS APPLY [FIELD].nodes('PEOPLE/PERSON') t(p)
    ) PEOPLE_ROWS
  FOR XML PATH('')), 1, 1, '') COMMA_SEPARATED_LIST</code></pre>
  </div>
  </div>
  </p>
  
  <p>Right now, this works just fine, and I get a nice comma separated list, but I thought there was an easier way to do this. Is there some xpath magic that yields simlar results?  </p>