SQLServer "For XML" Help

DarrenH 21 Reputation points
2022-02-25T04:26:35.597+00:00

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!

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,639 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 111.4K Reputation points MVP
    2022-02-25T22:46:20.597+00:00

    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
    
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. LiHong-MSFT 10,051 Reputation points
    2022-02-28T06:59:29.44+00:00

    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.

    0 comments No comments

  2. DarrenH 21 Reputation points
    2022-03-01T22:20:49.367+00:00

    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

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.