Split FOR XML PATH results into separate rows

I have the following query:

SELECT
    101 AS TeacherID
    ,s.ID
    ,s.First
    ,s.Last
FROM dbo.Students s
FOR XML PATH('Student'), ROOT('Students')

which generates output:

  • tutorial on connecting c# to sql server
  • Multiple Criteria In Case Statement
  • Executing an Oracle Stored Procedure from SQL Server 2005
  • How to encrypt numeric column in Sql Server 2008?
  • Clustered Index improvemens for particular queries
  • How do I prevent a database trigger from recursing?
  • <Students>
      <Student>
        <TeacherID>101</TeacherID>
        <ID>14</ID>
        <First>Mark</First>
        <Last>Smith</Last>
      </Student>
      <Student>
        <TeacherID>101</TeacherID>
        <ID>15</ID>
        <First>Josephina</First>
        <Last>Hewitt</Last>
      </Student>
    </Students>
    

    I want to break each group into its own rows (into a single XMLFormat column):

    AdministratorID   AdministratorName   XMLFormat
    ------------------------------------------------------------------------------------------------------------------------------------
    125               Bertha              <Student><TeacherID>101</TeacherID><ID>14</ID><First>Mark</First><Last>Smith</Last></Student>
    125               Bertha              <Student><TeacherID>101</TeacherID><ID>15</ID><First>Josephina</First><Last>Hewitt</Last></Student>
    

    I tried using the Table and Column Alias method:

    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)
    

    but this query generates a single row of data, with the entire XML in the single XMLFormat field.

    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 ‘.’.

    I want to avoid putting the data into an XML variable before using the .nodes() method, because the text could be very long.

    One Solution collect form web for “Split FOR XML PATH results into separate rows”

    select
        125 as AdministratorID,
        'Bertha' as AdministratorName,
        (
            select
                101 as TeacherID, s.ID, s.First, s.Last
            for xml path('Student'), type
        ) as XMLFormat
    from dbo.Students s
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.