SQLServer "For XML" Help

Question

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!

Answer 1

As Yithzak says without table definition and test data, we cannot test. But try this:

select (SELECT OPRID,RUN_CNTL_ID,ACAD_CAREER,STRM
        FROM PS_N_CM3018_RUNCTL RunControl 
        FOR XML AUTO, ELEMENTS, TYPE) AS "*",
       (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, ELEMENTS, TYPE) AS "*"
FOR XML PATH, ROOT('Root'), TYPE

Answer 2

Hey thank you so much ErlandSommarskog this worked perfectly! Sorry for the complex query and data, next time I will set things up a bit better so that others can test it.

I really appreciate everyones input

Answer 3

Hi @DarrenH
Check this query based on your description:

 SELECT RunControl.OPRID,RunControl.RUN_CNTL_ID,RunControl.ACAD_CAREER,RunControl.STRM,  
        COURSE.CRSE_ID, COURSE_DATA.STRM, COURSE_DATA.CLASS_NBR, COURSE_DATA.CLASS_STAT, COURSE_DATA.ENRL_TOT   
 FROM PS_CLASS_TBL COURSE   
 JOIN PS_CLASS_TBL COURSE_DATA  ON 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   
 JOIN PS_N_CM3018_RUNCTL RunControl  ON RunControl.STRM=COURSE.STRM   
 order by 1 FOR XML AUTO ,root ('Root'), elements   

If the answer is not working or helpful, please comment and provide more details about your issue.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.