A Sample Table-to-XML conversion.

A question was posted on the forum that I am a member of to convert a table into an XML data. The content of table look like this:

STUDENT_ID   grade
---------- ---------
 abc123     0.2
 abc123     0.4
 abc123     0.7
 def123     0.1
 def123     0.4
 def123     0.5

The request is to convert the above data into:

<Students StudentID="abc123">
  <StudentGrade Grades="0.2" />
  <StudentGrade Grades="0.4" />
  <StudentGrade Grades="0.7" />
</Students>
<Students StudentID="def123">
  <StudentGrade Grades="0.1" />
  <StudentGrade Grades="0.4" />
  <StudentGrade Grades="0.5" />
</Students>

Here's the code I posted as a reply:

declare @TestData Table
(
STUDENT_ID VARCHAR(6),
grade Decimal(2,1)
)

insert into @TestData (student_id, grade)
select 'abc123', 0.2 UNION
select 'abc123', 0.7 UNION
select 'abc123', 0.4 UNION
select 'def123', 0.1 UNION
select 'def123', 0.5 UNION
select 'def123', 0.4

select * from @TestData

;with tag1
as
(
  select distinct student_id
  from @testdata
)
select
  1 as tag,
  0 as parent,
  student_id as [Students!1!StudentID],
  null as [StudentGrade!2!Grades]
from tag1
union all
select
  2 as tag,
  1 as parent,
  t1.student_id as [Students!1!StudentID],
  grade as [StudentGrade!2!Grades]
from tag1 t1
  left join @testdata t2 on t1.student_id = t2.student_id
order by [Students!1!StudentID], [StudentGrade!2!Grades]
for xml explicit

As always, comments are welcome...


~~CK