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, <studentid="000011111"> is not valid xml. Your node cannot have an autonomous identifier without an attribute. <student id="000011111"> is valid. — djangojazz
– djangojazz, Commented Nov 21, 2016 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! — Gottfried Lesigang
– Gottfried Lesigang, Commented Nov 24, 2016 at 7:54

Mikael Eriksson · Accepted Answer · 2016-11-22 09:36:30Z

2

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

answered Nov 22, 2016 at 9:36

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gottfried Lesigang Over a year ago

Great! Upvote from my side... Finally it's a question of readability and maintainability...

TT. · Accepted Answer · 2016-11-21 21:27:07Z

1

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>

edited Nov 21, 2016 at 21:27

answered Nov 21, 2016 at 19:31

TT.

16.2k6 gold badges53 silver badges95 bronze badges

7 Comments

RAR Over a year ago

Thank you! Will try it out.

RAR Over a year ago

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."

TT. Over a year ago

@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.

Gottfried Lesigang Over a year ago

(@Shnugo, where are you?) Here I am for rescue :-)

TT. Over a year ago

@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. · Accepted Answer · 2016-11-22 08:10:01Z

1

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

edited Nov 22, 2016 at 8:10

TT.

16.2k6 gold badges53 silver badges95 bronze badges

answered Nov 22, 2016 at 8:02

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

2 Comments

TT. Over a year ago

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?

Mikael Eriksson Over a year ago

@TT I tried to explain what is going on here. 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.

Collectives™ on Stack Overflow

Looping of data and XML output

3 Answers 3

1 Comment

7 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related