Reputation: 319
I have a table in SQL server and i would like to get the data in a xml format.
My table looks like
Year | Semester | Room | Professor
-----+----------+------+-----------
2013 | 1 | 101 | Li
2013 | 1 | 102 | wang
2013 | 1 | 103 | Rob
2013 | 2 | 204 | jess
2013 | 2 | 205 | Mat
2014 | 1 | 104 | Li
2014 | 1 | 105 | wang
2014 | 1 | 106 | Rob
2014 | 2 | 206 | jess
2014 | 2 | 207 | Mat
I would like the Xml format like this:
<St>
<Year id="2013">
<Semester nb="1">
<data>
<Room>101</Room>
<professor>Li</kwh>
</data>
<data>
<Room>102</Room>
<professor>wang</kwh>
</data>
</Semester>
<Semester nb="2">
<data>
<Room>204</Room>
<professor>jess</kwh>
</data>
</Semester>
<Year>
<Year id="2014">
<Semester nb="1">
...
</Semester>
<Semester nb="2">
...
</Semester>
<Year>
</St>
For Now my code is:
SELECT
Year as "@id",
Semester as "Semester /@nb",
Room as "Semester/data/Room",
Professor as "Semester/data/Professor "
FROM
checking
FOR XML Path ('Year'), root ('St')
Which give me this xml:
<St>
<Year id="2013">
<Semester nb="1">
<data>
<Room>101</Room>
<Professor>Li</Professor>
</data>
</Semester> </Year> <Year id="2013">
<Semester nb="1">
<data>
<Room>102</Room>
<Professor>wang</Professor>
</data>
</Semester> </Year> <Year id="2013">
<Semester nb="1">
<data>
<Room>103</Room>
<Professor>rob</Professor>
</data>
</Semester>
</Year>
...
</St>
I am very close to the solution but can't find how to group it by year and semester.
I try group by and order by but none of them work.
Upvotes: 2
Views: 880
Reputation: 139010
Use group by and correlated subqueries.
declare @T table
(
Year int,
Semester int,
Room int,
Professor varchar(10)
);
insert into @T values
(2013, 1, 101, 'Li '),
(2013, 1, 102, 'wang'),
(2013, 1, 103, 'Rob '),
(2013, 2, 204, 'jess'),
(2013, 2, 205, 'Mat '),
(2014, 1, 104, 'Li '),
(2014, 1, 105, 'wang'),
(2014, 1, 106, 'Rob '),
(2014, 2, 206, 'jess'),
(2014, 2, 207, 'Mat ');
select T1.Year as [@id],
(
select T2.Semester as [@nb],
(
select T3.Room,
T3.Professor
from @T as T3
where T3.Year = T2.Year and
T3.Semester = T2.Semester
for xml path('data'), type
)
from @T as T2
where T1.Year = T2.Year
group by T2.Semester, T2.Year
for xml path('Semester'), type
)
from @T as T1
group by T1.Year
for xml path('Year'), root('St');
Result:
<St>
<Year id="2013">
<Semester nb="1">
<data>
<Room>101</Room>
<Professor>Li </Professor>
</data>
<data>
<Room>102</Room>
<Professor>wang</Professor>
</data>
<data>
<Room>103</Room>
<Professor>Rob </Professor>
</data>
</Semester>
<Semester nb="2">
<data>
<Room>204</Room>
<Professor>jess</Professor>
</data>
<data>
<Room>205</Room>
<Professor>Mat </Professor>
</data>
</Semester>
</Year>
<Year id="2014">
<Semester nb="1">
<data>
<Room>104</Room>
<Professor>Li </Professor>
</data>
<data>
<Room>105</Room>
<Professor>wang</Professor>
</data>
<data>
<Room>106</Room>
<Professor>Rob </Professor>
</data>
</Semester>
<Semester nb="2">
<data>
<Room>206</Room>
<Professor>jess</Professor>
</data>
<data>
<Room>207</Room>
<Professor>Mat </Professor>
</data>
</Semester>
</Year>
</St>
Upvotes: 3