I am trying to build an xml file using sql queries. I have it almost to where I need it but can't get this last step
This is my initial sql
select COURSE.CRSE_ID, COURSE_DATA.STRM, COURSE_DATA.CLASS_NBR, COURSE_DATA.CLASS_STAT, COURSE_DATA.ENRL_TOT
from PS_CLASS_TBL COURSE
,PS_CLASS_TBL COURSE_DATA
WHERE COURSE_DATA.CRSE_ID = COURSE.CRSE_ID
AND COURSE_DATA.CRSE_OFFER_NBR = COURSE.CRSE_OFFER_NBR
AND COURSE_DATA.STRM = COURSE.STRM
AND COURSE_DATA.SESSION_CODE = COURSE.SESSION_CODE
AND COURSE_DATA.CLASS_SECTION = COURSE.CLASS_SECTION
order by 1 FOR XML AUTO ,root ('Root'), elements
This creates the following
<?xml version="1.0" encoding="UTF-8"?>
<root>
<Course>
<CourseID>1234</CourseID>
<CourseData>
<STRM>1211</STRM>
<ClassNbr>ABCD</ClassNbr>
<Status>A</Status>
<Enrolled>23</Enrolled>
</CourseData>
<CourseData>
<STRM>1211</STRM>
<ClassNbr>EFGJ</ClassNbr>
<Status>A</Status>
<Enrolled>5</Enrolled>
</CourseData>
</Course>
<Course>
<CourseID>5678</CourseID>
<CourseData>
<STRM>1211</STRM>
<ClassNbr>ABCD</ClassNbr>
<Status>A</Status>
<Enrolled>10</Enrolled>
</CourseData>
</Course>
</root>
Now I want to add the following SQL because I need to also have my run control information in the file. The run control information will only generate a single row
SELECT OPRID,RUN_CNTL_ID,ACAD_CAREER,STRM
FROM PS_N_CM3018_RUNCTL RunControl
In order to get this
<?xml version="1.0" encoding="UTF-8"?> <root>
<RunControl>
<RunControlID>DH</RunControlID>
<RunDate>Dec 5, 2021</RunDate>
<Career>APPR</Career>
<Term>1211</Term>
</RunControl>
<ClassData>
<Course>
<CourseID>1234</CourseID>
<CourseData>
<STRM>1211</STRM>
<ClassNbr>ABCD</ClassNbr>
<Status>A</Status>
<Enrolled>23</Enrolled>
</CourseData>
<CourseData>
<STRM>1211</STRM>
<ClassNbr>EFGJ</ClassNbr>
<Status>A</Status>
<Enrolled>5</Enrolled>
</CourseData>
</Course>
<Course>
<CourseID>5678</CourseID>
<CourseData>
<STRM>1211</STRM>
<ClassNbr>ABCD</ClassNbr>
<Status>A</Status>
<Enrolled>10</Enrolled>
</CourseData>
</Course>
</ClassData>
</root>
I've tried a few different things but I cannot figure out how to combine the 2 sql statements in order to generate the single xml file.
Any help would be greatly appreciated!