title: Split FOR XML PATH results into separate rows
body: <p>I have the following query:</p>
  
  <pre><code>SELECT
      101 AS TeacherID
      ,s.ID
      ,s.First
      ,s.Last
  FROM dbo.Students s
  FOR XML PATH('Student'), ROOT('Students')
  </code></pre>
  
  <p>which generates output:</p>
  
  <pre><code>&lt;Students&gt;
    &lt;Student&gt;
      &lt;TeacherID&gt;101&lt;/TeacherID&gt;
      &lt;ID&gt;14&lt;/ID&gt;
      &lt;First&gt;Mark&lt;/First&gt;
      &lt;Last&gt;Smith&lt;/Last&gt;
    &lt;/Student&gt;
    &lt;Student&gt;
      &lt;TeacherID&gt;101&lt;/TeacherID&gt;
      &lt;ID&gt;15&lt;/ID&gt;
      &lt;First&gt;Josephina&lt;/First&gt;
      &lt;Last&gt;Hewitt&lt;/Last&gt;
    &lt;/Student&gt;
  &lt;/Students&gt;
  </code></pre>
  
  <p>I want to break each  group into its own rows (into a single XMLFormat column):</p>
  
  <pre><code>AdministratorID   AdministratorName   XMLFormat
  ------------------------------------------------------------------------------------------------------------------------------------
  125               Bertha              &lt;Student&gt;&lt;TeacherID&gt;101&lt;/TeacherID&gt;&lt;ID&gt;14&lt;/ID&gt;&lt;First&gt;Mark&lt;/First&gt;&lt;Last&gt;Smith&lt;/Last&gt;&lt;/Student&gt;
  125               Bertha              &lt;Student&gt;&lt;TeacherID&gt;101&lt;/TeacherID&gt;&lt;ID&gt;15&lt;/ID&gt;&lt;First&gt;Josephina&lt;/First&gt;&lt;Last&gt;Hewitt&lt;/Last&gt;&lt;/Student&gt;
  </code></pre>
  
  <p>I tried using the Table and Column Alias method:</p>
  
  <pre><code>SELECT  125 AS AdministratorID,
          'Bertha' AS AdministratorName,
          t.c AS XMLFormat
  FROM 
  (   
      SELECT
          101 AS TeacherID
          ,s.ID
          ,s.First
          ,s.Last
      FROM dbo.Students s
      FOR XML PATH('Student'), ROOT('Students')
  ) t(c)
  </code></pre>
  
  <p>but this query generates a single row of data, with the entire XML in the single XMLFormat field.</p>
  
  <p>I thought I may need to use the .nodes() method, but when I add .nodes('/Students/Student') to the t(c) alias, I get 'Incorrect syntax near '.'.</p>
  
  <p>I want to avoid putting the data into an XML variable before using the .nodes() method, because the text could be very long.</p>