sql - Split FOR XML PATH results into separate rows -

i have following query:

select     101 teacherid     ,s.id     ,s.first     ,s.last dbo.students s xml path('student'), root('students') 

which generates output:

<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 break each group own rows (into 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 table , column alias method:

select  125 administratorid,         'bertha' administratorname,         t.c xmlformat  (        select         101 teacherid         ,s.id         ,s.first         ,s.last     dbo.students s     xml path('student'), root('students') ) t(c) 

but query generates single row of data, entire xml in single xmlformat field.

i thought may need use .nodes() method, when add .nodes('/students/student') t(c) alias, 'incorrect syntax near '.'.

i want avoid putting data xml variable before using .nodes() method, because text long.

select     125 administratorid,     'bertha' administratorname,     (         select             101 teacherid, s.id, s.first, s.last         xml path('student'), type     ) xmlformat dbo.students s