Looping of data and XML output

Question

I need to export data from a table in the following XML format:

<studentid="000011111">
    <academic_goal type="official">
      <program_group>
        <program type="catalog">2014-16</program>
        <program type="degree">BS</program>
        <program type="major">PS</program>
        <program type="concentration">PCC</program>
      </program_group>
      <program_group>
        <program type="catalog">2014-16</program>
        <program type="degree">BS</program>
        <program type="minor">HI</program>
      </program_group>
    </academic_goal>
</studentid>

So far what I have is:

<studentid="000011111">
    <academic_goal type="official">
      <program_group>
        <program type="catalog">2014-16</program>
        <program type="degree">BS</program>
        <program type="major">PS</program>
        <program type="minor">HI</program>
        <program type="concentration">PCC</program>
      </program_group>
    </academic_goal>
</studentid>

How can I loop through this information so that the minor is within its own program_group tag (along with catalog and degree)?

Here's the table structure:

CREATE TABLE [dbo].[StudentProgramData](
    [StudentID] [nvarchar](10) NULL,
    [Catalog] [nvarchar](10) NULL,
    [Degree] [nvarchar](10) NULL,
    [Major] [nvarchar](50) NULL,
    [Minor] [nvarchar](50) NULL,
    [Concentration] [nvarchar](50) NULL)

Sample data:

insert into StudentProgramData
values 
('000011111', '2014-16', 'BS', 'PS', 'HI', 'PCC'),
('000022222', '2012-14', 'BA', 'MK', 'BI', 'ESO'),
('000033333', '2012-14', 'BS', 'MB', NULL, 'AUE'),
('000044444', '2014-16', 'ME', 'PS', 'HI', NULL),
('000055555', '2010-12', 'MD', 'PS', NULL, 'PCC')

I included 5 sample records, but my output above only shows the first student.

My code so far for the loop is:

(select
    ltrim(rtrim(StudentProgramData.catalog)) as [program/@catalog],
    ltrim(rtrim(StudentProgramData.degree)) as [program/@degree],
    ltrim(rtrim(StudentProgramData.major)) as [program/@major],
    ltrim(rtrim(StudentProgramData.minor)) as [program/@minor],
    ltrim(rtrim(StudentProgramData.concentration)) as [program/@concentration]

    from StudentProgramData

for xml path('program'), type).query('
   <academic_goal type="official">
   {
     for $program in /program
         return
            <program_group>
               {$program/Name}
                <program type="catalog">{data($program/program/@year)}</program>
                <program type="degree">{data($program/program/@degree)}</program>
                <program type="major">{data($program/program/@major)}</program>
                <program type="minor">{data($program/program/@minor)}</program>
                <program type="concentration">{data($program/program/@concentration)}</program>
            </program_group>
   }
   </academic_goal>')

Any help you could provide is very much appreciated.

As someone has already pointed out in their answer but not spelled it out, is not valid xml. Your node cannot have an autonomous identifier without an attribute. is valid. — djangojazz, Nov 21 '16 at 22:03
Is this question solved? Do you need further help? Please allow me one hint: If this question is solved, it would be very kind of you, to tick the acceptance check below the (best) answer's vote counter. This will 1) mark this issue as solved 2) make it easier for followers to find the best solution 3) pay points to the answerer and 4) pay points to you. Since you've crossed the 15 points border yourself, you are - additionally - asked to vote on contributions. This is the SO-way to say thank you. Happy Coding! — Shnugo, Nov 24 '16 at 07:54

score 2 · Answer 1 · answered Nov 22 '16 at 09:36

Another way is to create a derived table/unpivot for each program group.

select SPD.StudentID as '@id',
       (
       select 'official' as '@type',
              (
              select V.Type as '@type',
                     V.Value as '*'
              from (values('catalog', SPD.Catalog),
                          ('degree', SPD.Degree),
                          ('major', SPD.Major),
                          ('concentration', SPD.Concentration)) as V(Type, Value)
              for xml path('program'), root('program_group'), type
              ),
              (
              select V.Type as '@type',
                     V.Value as '*'
              from (values('catalog', SPD.Catalog),
                          ('degree', SPD.Degree),
                          ('minor', SPD.Minor)) as V(Type, Value)
              for xml path('program'), root('program_group'), type
              )
        for xml path('academic_goal'), type
       )
from dbo.StudentProgramData as SPD
for xml path('student');

Great! Upvote from my side... Finally it's a question of readability and maintainability... — Shnugo, Nov 22 '16 at 10:07

TT. · Answer 2 · 2016-11-21T21:27:07.263

I'm pretty sure someone will slap me on the wrist for coming up with something so circuitous (@Shnugo, where are you?), but this is what I got. It's likely not the best and someone will give you a better answer no doubt. But this is something you can work with in the meantime.

SELECT
    studentId AS [student/@id],
    'official' AS [student/academic_goal/@type],
    (
        SELECT
            (
                SELECT
                    'catalog' AS [program/@type],
                    ltrim(rtrim(catalog)) AS [program]
                FOR XML
                    PATH(''), TYPE
            ),
            (
                SELECT
                    'degree' AS [program/@type],
                    ltrim(rtrim(degree)) AS [program]
                FOR XML
                    PATH(''), TYPE
            ),
            (
                SELECT
                    'major' AS [program/@type],
                    ltrim(rtrim(major)) AS [program]
                FOR XML
                    PATH(''), TYPE
            ),
            (
                SELECT
                    'concentration' AS [program/@type],
                    ltrim(rtrim(concentration)) AS [program]
                FOR XML
                    PATH(''), TYPE
            )
        FOR XML 
            PATH('program_group'), TYPE
    ) AS [student/academic_goal],
    (
        SELECT
            (
                SELECT
                    'catalog' AS [program/@type],
                    ltrim(rtrim(catalog)) AS [program]
                FOR XML
                    PATH(''), TYPE
            ),
            (
                SELECT
                    'degree' AS [program/@type],
                    ltrim(rtrim(degree)) AS [program]
                FOR XML
                    PATH(''), TYPE
            ),
            (
                SELECT
                    'minor' AS [program/@type],
                    ltrim(rtrim(minor)) AS [program]
                FOR XML
                    PATH(''), TYPE
            )
        FOR XML 
            PATH('program_group'), TYPE
    ) AS [student/academic_goal]
FROM
    StudentProgramData
FOR XML 
    PATH(''), ROOT('students');

Produces:

<students>
  <student id="000011111">
    <academic_goal type="official">
      <program_group>
        <program type="catalog">2014-16</program>
        <program type="degree">BS</program>
        <program type="major">PS</program>
        <program type="concentration">PCC</program>
      </program_group>
      <program_group>
        <program type="catalog">2014-16</program>
        <program type="degree">BS</program>
        <program type="minor">HI</program>
      </program_group>
    </academic_goal>
  </student>
  <student id="000022222">
    <academic_goal type="official">
      <program_group>
        <program type="catalog">2012-14</program>
        <program type="degree">BA</program>
        <program type="major">MK</program>
        <program type="concentration">ESO</program>
      </program_group>
      <program_group>
        <program type="catalog">2012-14</program>
        <program type="degree">BA</program>
        <program type="minor">BI</program>
      </program_group>
    </academic_goal>
  </student>
  <student id="000033333">
    <academic_goal type="official">
      <program_group>
        <program type="catalog">2012-14</program>
        <program type="degree">BS</program>
        <program type="major">MB</program>
        <program type="concentration">AUE</program>
      </program_group>
      <program_group>
        <program type="catalog">2012-14</program>
        <program type="degree">BS</program>
        <program type="minor" />
      </program_group>
    </academic_goal>
  </student>
  <student id="000044444">
    <academic_goal type="official">
      <program_group>
        <program type="catalog">2014-16</program>
        <program type="degree">ME</program>
        <program type="major">PS</program>
        <program type="concentration" />
      </program_group>
      <program_group>
        <program type="catalog">2014-16</program>
        <program type="degree">ME</program>
        <program type="minor">HI</program>
      </program_group>
    </academic_goal>
  </student>
  <student id="000055555">
    <academic_goal type="official">
      <program_group>
        <program type="catalog">2010-12</program>
        <program type="degree">MD</program>
        <program type="major">PS</program>
        <program type="concentration">PCC</program>
      </program_group>
      <program_group>
        <program type="catalog">2010-12</program>
        <program type="degree">MD</program>
        <program type="minor" />
      </program_group>
    </academic_goal>
  </student>
</students>

I need to nest this into a larger query I have. When I run this as is, it works well, but when I run it inside of my larger query, I get an error: "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS." — RAR, Nov 21 '16 at 21:53
@RAR Without seeing the query you have I'm merely guessing, but I'm assuming you have a subquery that is intended to produce XML without a `FOR XML PATH(...` clause. — TT., Nov 21 '16 at 22:08
@Shnugo My answer wasn't all that bad except for many subqueries. Had I known about that empty element trick it could have been shorter, that's for sure :) — TT., Nov 22 '16 at 08:12
That's why I plused it :-) There is one situation, where you'd really hate your many sub-queries: `WITH XMLNAMESPACES` :-) — Shnugo, Nov 22 '16 at 08:15

score 1 · Answer 3 · edited Nov 22 '16 at 08:10

The tricky thing with your format is the usage of many nodes with the same name <program>. As the engine needs to build the attributes first, you cannot add a value and then add attributes to the same node again. The engine at least thinks so.

The trick: Add one empty element in between. This will tell the engine, that the last one is to close. Try it like this:

SELECT StudentID AS [@id]
      ,(
        SELECT 'official' AS [@type]
              ,(
                    SELECT 'catalog' AS [program/@type] 
                          ,spd.[Catalog] AS [program]
                          ,''
                          ,'degree' AS [program/@type] 
                          ,spd.[Degree] AS [program]
                          ,''
                          ,'major' AS [program/@type] 
                          ,spd.[Major] AS [program]
                          ,''
                          ,'concentration' AS [program/@type] 
                          ,spd.[Concentration] AS [program]
                    FOR XML PATH('program_group'),TYPE
               )
              ,(
                    SELECT 'catalog' AS [program/@type] 
                          ,spd.[Catalog] AS [program]
                          ,''
                          ,'degree' AS [program/@type] 
                          ,spd.[Degree] AS [program]
                          ,''
                          ,'minor' AS [program/@type] 
                          ,spd.[Minor] AS [program]
                    FOR XML PATH('program_group'),TYPE
               )
        FROM StudentProgramData AS spd
        WHERE s.StudentID=spd.StudentID
        FOR XML PATH('academic_goal'),TYPE
       )
FROM StudentProgramData AS s
FOR XML PATH('student')--,ROOT('root') --Do you need a root node? Probably yes, if more than 1 student...

I was struggling with that as well, didn't know that empty element trick. Good to know :). Bit dirty though... I'm not too fond of how SQL Server implemented to XML selection. That trick, is that documented somewhere? — TT., Nov 22 '16 at 08:10
@TT I tried to explain what is going on [here](http://stackoverflow.com/a/29234102/569436). Not sure if this trick is explicitly documented by Microsoft but as I see it, this behaviour it is the only way it could be with the rules of how XML is created using `for xml`. — Mikael Eriksson, Nov 22 '16 at 09:51

Looping of data and XML output

3 Answers3