Regarding FOR XML Output In SQL Server 2005

[sanndeb]

Hi All,

I Have A Query that returns rows like the picture below...

Now I want an xml like this

<Employees>
  <Group GroupId="8">
    <Employee>Manju</Employee>
    <Employee>Sudip</Employee>
  </Group>
  <Group GroupId="12">
    <Employee>Raja</Employee>
    <Employee>Shambo</Employee>
    ......
  </Group>
  .......
</Employees>

from the query.

How do i do it? Or If Its Really Possible In SQL Server? 'Coz I read somewhere you can't use group by in select when using FOR XML Command.

Pls help. Thanks :)

[Brad Orders]

Hi sanndeb

There are a few possible ways of doing this. Here is what I would do:
1. Get the unique Group IDs into a temporary table
2. Use this temporary table with the original table to generate the XML.

Here is the code I wrote to create the XML:

-- Create a temp table with the Group Ids
SELECT DISTINCT GroupId
INTO #tmpGroups
FROM #EmployeeTable

-- Generate the XML
SELECT #tmpGroups.GroupId as '@GroupId',
(SELECT EmpName
FROM #EmployeeTable
WHERE #tmpGroups.GroupId = #EmployeeTable.GroupId
FOR XML PATH(''), TYPE)
FROM #tmpGroups
FOR XML PATH('Group'), ROOT('Employees')

DROP TABLE #tmpGroups


That should do the trick!

Hope this helps.


Brad Orders

[sanndeb]

@Brad Orders
That Was Perfect :)

Many Many Thanks... But Can It Be Done Without The Temp Table.

[ck9663]

Sorry, used a sample that I used on the other post :) But the concept should be the same...

Try this:

 
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
 
;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
 
 

Happy Coding!!!

~~ CK

[sanndeb]

@ck9663
Yep got the concept, will try it on my db and let you know. thanks for your effort. :)