x

Looping of data and XML output

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.

more ▼

asked Nov 21, 2016 at 06:48 PM in Default

avatar image

RAR
0 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

From looking at the desired XML structure, it appears that the UI design smells EAV (entity attribute value) pattern. Personally, I don't like it, but this is just my personal preference. In this case, the XML has the same node named program for many different columns, which unfortunately necessitates the unpivoting the source data. In the solution below, the unpivoting is done via UNION ALL. The solution looks nasty exclusively due to the EAV pattern implementation and could have been more elegant should the normal design be used instead, with column names matching the node names.

I would like to point out that the script below produces the separate program_group tag for both Major and Minor regardless whether the student is enrolled into any Minor program or not. If the second tag needs to be omitted for those students which have just the Major program, please un-comment the text reading and Minor is not null in the 3 inner selects of the bottom group sub-select.

Also, I wrapped the resulting XML into the root node named student. Without it the resulting XML is a valid fragment, but is not a completely valid XML which requires the presence of the root node. If the root node is not desired, please comment out the text reading , root('students') on the last line of the script below.

Here is the complete solution:

 select
     StudentID studentid,
     'official' [studentid/academic_goal/@type],
     (
         select
             attr_type [program/@type], Value [program]
             from (
                 select 'catalog' attr_type, [Catalog] Value 
                     from dbo.StudentProgramData where StudentID = p.StudentID
                 union all
                 select 'degree' attr_type, Degree Value 
                     from dbo.StudentProgramData where StudentID = p.StudentID
                 union all
                 select 'major' attr_type, Major Value 
                     from dbo.StudentProgramData where StudentID = p.StudentID
                 union all
                 select 'concentration' attr_type, Concentration Value 
                     from dbo.StudentProgramData where StudentID = p.StudentID
             ) t for xml path(''), root('program_group'), type
     ) [studentid/academic_goal],
     (
         select
             attr_type [program/@type], Value [program]
             from (
                 select 'catalog' attr_type, [Catalog] Value 
                     from dbo.StudentProgramData where StudentID = p.StudentID --and Minor is not null
                 union all
                 select 'degree' attr_type, Degree Value 
                     from dbo.StudentProgramData where StudentID = p.StudentID --and Minor is not null
                 union all
                 select 'minor' attr_type, Minor Value 
                     from dbo.StudentProgramData where StudentID = p.StudentID --and Minor is not null
             ) t for xml path(''), root('program_group'), type
     ) [studentid/academic_goal]
     from dbo.StudentProgramData p
     for xml path(''), type, root('students');

The above script produces the following result (shown for just 2 first students):

 <students>
   <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>
   <studentid>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>
   </studentid>
 </students>

Hope this help,

Oleg

more ▼

answered Nov 22, 2016 at 04:05 PM

avatar image

Oleg
18.9k 3 7 28

(comments are locked)
10|1200 characters needed characters left

Hi @Oleg,

Thank you so much! This works well!. Just two questions:

I had to change the union all to "union" and now my tags are displaying in alphabetical order. And I mistakenly typed "catalog" in the example above but it should have been "year". So now instead of displaying in this order - year, degree, major, concentration, it displays concentration, degree, major, year. Is there a way I can change this?

There are some cases where a student might have more than 1 record in the table StudentProgramData (if they have double majors, concentrations, or minors). When this happens, the tags are duplicated. Is there a way to not duplicate the tag if it is the same value? Here is an example:

 <program type="concentration">AUHS</program>
 <program type="concentration">AUMS</program>
 <program type="concentration">ENDO</program>
 <program type="degree">ME</program>
 <program type="major">IL</program>
 <program type="year">2014-16</program>
  


  
 <program type="concentration">AUHS</program>
 <program type="concentration">AUMS</program>
 <program type="concentration">ENDO</program>
 <program type="degree">ME</program>
 <program type="major">IL</program>
 <program type="year">2014-16</program>
  


  
 <program type="concentration">AUHS</program>
 <program type="concentration">AUMS</program>
 <program type="concentration">ENDO</program>
 <program type="degree">ME</program>
 <program type="major">IL</program>
 <program type="year">2014-16</program>
  


I used the code you previously provided and changed "catalog" to "year". This is one part or a larger query I am working on. Let me know if you need any other info from me. Thank you again for your help!

more ▼

answered Nov 22, 2016 at 11:08 PM

avatar image

RAR
0 1

@RAR Unless you have a compelling reason to change UNION ALL to UNION, please don't do it. It appears that the UNION is used to remedy the situation with duplicate program tags of the same type, but it does not work anyway, so there is no reason to use it. In the original question it was not clear that the same student can have multiple records in the StudentProgramData table. Because multiple records are possible, there has to be some column (or combination) in this table to uniquely identify the record. This could be some RecordID column or, if such column does not exists, a combination of StudentID and Catalog columns.

Please let me know what uniquely identifies the record in this table, and I will update my answer accordingly. I assume that in case of the multiple records per student, it is OK to still represent each record with the single studentid tag which has 2 program_group tags inside.

Nov 23, 2016 at 12:41 PM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x438
x202

asked: Nov 21, 2016 at 06:48 PM

Seen: 57 times

Last Updated: Nov 23, 2016 at 12:41 PM

Copyright 2017 Redgate Software. Privacy Policy