I need to export data from a table in the following XML format:
So far what I have is:
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:
I included 5 sample records, but my output above only shows the first student.
My code so far for the loop is:
Any help you could provide is very much appreciated.
asked Nov 21, 2016 at 06:48 PM in Default
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:
The above script produces the following result (shown for just 2 first students):
Hope this help,
answered Nov 22, 2016 at 04:05 PM
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:
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!