TSQL Format Query Output

Question

I have a stored procedure that is generating an XML output for me.

The SP looks like this:

ALTER PROCEDURE [dbo].[InternFetchProf]
@positionID INT=''
AS
BEGIN
SET NOCOUNT ON;
BEGIN
    SELECT   A.[id],
             A.[positionTitle],
             (SELECT   B.[id],
                       B.[question],
                       B.[order],
                       (SELECT   D.[optionName],
                                 D.[order]
                        FROM     internshipProfOptions AS D
                        WHERE    questionID = B.[id]
                        ORDER BY D.[order] ASC
                        FOR      XML PATH ('optionsBlock'), TYPE, ELEMENTS, ROOT ('options'))
              FROM     internshipProfQuestions AS B
              WHERE    positionID = A.[id]
              ORDER BY B.[order] ASC
              FOR      XML PATH ('questionBlock'), TYPE, ELEMENTS, ROOT ('questions'))
    FROM     internships AS a
    WHERE    A.[id] = @positionID
    ORDER BY closeDate ASC
    FOR      XML PATH ('positions'), TYPE, ELEMENTS, ROOT ('root');
END
END

This gives me the XML output of

                      <root>
              <positions>
                <id>1</id>
                <positionTitle>APS Team</positionTitle>
                <questions>
                  <questionBlock>
                    <id>1</id>
                    <question>Whats your fav color</question>
                    <order>1</order>
                    <options>
                      <optionsBlock>
                        <optionName>VBA</optionName>
                        <order>1</order>
                      </optionsBlock>
                      <optionsBlock>
                        <optionName>JavaScript</optionName>
                        <order>2</order>
                      </optionsBlock>
                      <optionsBlock>
                        <optionName>HTML</optionName>
                        <order>3</order>
                      </optionsBlock>
                    </options>
                  </questionBlock>
                  <questionBlock>
                    <id>2</id>
                    <question>Whos your daddy?</question>
                    <order>2</order>
                    <options>
                      <optionsBlock>
                        <optionName>PHP</optionName>
                        <order>1</order>
                      </optionsBlock>
                      <optionsBlock>
                        <optionName>Perl</optionName>
                        <order>2</order>
                      </optionsBlock>
                    </options>
                  </questionBlock>
                </questions>
              </positions>
            </root>

I have an additional SELECT statement here:

SELECT C.[groupName],
                             C.[order],
                       FROM   internshipProfGroups AS C
                      WHERE  questionID = B.[id]
                      FOR    XML PATH ('groupBlock'), TYPE, ELEMENTS, ROOT ('groups')

Which I am trying to get to generate the XML in the root of "QuestionBlock" like so:

            <questionBlock>
            ...

            <groups>
               <groupBlock>
                  <groupName>Testing</groupName>
               </groupBlock>
               <groupBlock>
                  <groupName>Another Test</groupName>
               </groupBlock>
            </groups>

            ...
            </questionBlock>

I am struggling to get the statement in the right area to produce this. Any help with what I may be missing?

Adminiculo · Accepted Answer · 2013-12-20 22:36:09Z

IF I understood well you need to insert the second query in the same level that "SELECT D.[OptionName]" (also I think you have some mistakes in the code):

SELECT  
   A.[id],
   A.[positionTitle],
   (SELECT   B.[id],
             B.[question],
             B.[ord],
            (SELECT   D.[optionName],
                      D.[ord]
             FROM     internshipProfOptions AS D
             WHERE    questionID = B.[id]
             ORDER BY D.[ord] ASC
             FOR      XML PATH ('optionsBlock'), TYPE, ELEMENTS, ROOT ('options')),
            (SELECT C.[groupName],
                       C.[ord]
             FROM   internshipProfGroups AS C
             WHERE  questionID = B.[id]
             FOR    XML PATH ('groupBlock'), TYPE, ELEMENTS, ROOT ('groups'))
    FROM     internshipProfQuestions AS B
    WHERE    positionID = A.[id]
    ORDER BY B.[ord] ASC
    FOR      XML PATH ('questionBlock'), TYPE, ELEMENTS, ROOT ('questions'))
FROM internships A
WHERE    A.[id] = 3
ORDER BY closeDate ASC
FOR      XML PATH ('positions'), TYPE, ELEMENTS, ROOT ('root');

WORKING SAMPLE

That gives this result:

<?xml version="1.0"?>
<root>
    <positions>
        <id>3</id>
        <positionTitle>Trimepost</positionTitle>
        <questions>
            <questionBlock>
                <id>2</id>
                <question>What?</question>
                <ord>1</ord>
                <options>
                    <optionsBlock>
                        <optionName>Opt 2</optionName>
                        <ord>3</ord>
                    </optionsBlock>
                </options>
                <groups>
                    <groupBlock>
                        <groupName>ALPHA</groupName>
                        <ord>1</ord>
                    </groupBlock>
                    <groupBlock>
                        <groupName>BETA</groupName>
                        <ord>2</ord>
                    </groupBlock>
                </groups>
            </questionBlock>
        </questions>
    </positions>
</root>

Collectives™ on Stack Overflow

TSQL Format Query Output

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related