CODEWITHSUNDEEP
sql-serverxmlt-sql
usertfwr
usertfwr

Reputation: 319

Tsql Create Xml from table

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

Answers (1)

Mikael Eriksson
Mikael Eriksson

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

Related Questions