Breaking SQL FOR XML query into specifically named parent elements

What I'm trying to do I think is quite easy so if you're familiar with FOR XML in SQL Server I'd recommend skipping to the bottom and reading the bold text part :)

I'm trying to use the FOR XML statement in SQL Server 2005 to achieve the results I need. Currently I have this...

SELECT  
    txtReasonTypeI开发者_JAVA技巧D AS [ReasonTypeID]
  ,
    (SELECT 
    [Reason].intReasonID, 
    [Reason].txtReason
    FROM    CST_lnkProfileReason INNER JOIN 
    CST_tblReason AS [Reason] ON CST_lnkProfileReason.intReasonID = [Reason].intReasonID 
    WHERE   CST_lnkProfileReason.intProfileID = @intProfileID
    AND   CST_lnkProfileReason.txtReasonTypeID = [Response].txtReasonTypeID
    ORDER BY Reason.txtReason
    FOR XML AUTO, TYPE
    )
  ,
    (SELECT 
    [PulledSupportReason].intReasonID, 
    [PulledSupportReason].txtReason
    FROM    CST_lnkPulledSupportReason INNER JOIN 
    CST_tblReason AS [PulledSupportReason] ON CST_lnkPulledSupportReason.intReasonID = [PulledSupportReason].intReasonID 
    WHERE   CST_lnkPulledSupportReason.intProfileID = @intProfileID
    AND   CST_lnkPulledSupportReason.txtReasonTypeID = [Response].txtReasonTypeID
    ORDER BY [PulledSupportReason].txtReason
    FOR XML AUTO, TYPE
    )
FROM    CST_tblReasonTypes AS [Response]
FOR XML AUTO, ROOT('ResponseProfile')

which is returning the following XML...

<ResponseProfile>
  <Response ReasonTypeID="ExampleType">
    <Reason intReasonID="106" txtReason="Call Back - 1"/>
    <Reason intReasonID="147" txtReason="Call Back - 2"/>
    <PulledSupportReason intReasonID="892" txtReason="PS Reason a"/>
    <PulledSupportReason intReasonID="893" txtReason="PS Reason b"/>
  </Response>
   ...more <Response>s
</ResponseProfile>

As you can see, Reason elements and PulledSupportReason elements are coming from the same table although they are separate elements in this query. (Probably a case of bad design) But - what I want is simple enough, to put a parent element around the Reason and PulledSupportReason elements eg...

<ResponseProfile>
  <Response ReasonTypeID="ExampleType">
     <Reasons>
        <Reason intReasonID="106" txtReason="Call Back - 1"/>
        <Reason intReasonID="147" txtReason="Call Back - 2"/>
     </Reasons>
     <PulledSupportReasons>  
        <PulledSupportReason intReasonID="892" txtReason="PS Reason a"/>
        <PulledSupportReason intReasonID="893" txtReason="PS Reason b"/>
     </PulledSupportReasons>
  </Response>
   ...more <Response>s
</ResponseProfile>

I think I can achieve this using XML PATH or XML EXPLICIT? Thanks for any assistance :)

Try to use FOR XML PATH('....'), ROOT('....') - with it, you should be able to achieve what you're looking for.

I'm illustrating this for the second subselect - adapt accordingly for the first one:

(SELECT 
    [PulledSupportReason].intReasonID, 
    [PulledSupportReason].txtReason
 FROM    
     CST_lnkPulledSupportReason 
 INNER JOIN 
    CST_tblReason AS [PulledSupportReason] ON CST_lnkPulledSupportReason.intReasonID = [PulledSupportReason].intReasonID 
 WHERE   
     CST_lnkPulledSupportReason.intProfileID = @intProfileID
     AND CST_lnkPulledSupportReason.txtReasonTypeID = [Response].txtReasonTypeID
 ORDER BY 
     [PulledSupportReason].txtReason
 FOR XML PATH('PulledSupportReason'), TYPE
) AS 'PulledSupportReasons'

This should work, I hope ! (can't really test since I don't have your table at hand to see)

The inner FOR XML PATH(PulledSupportReason') defines the innermost XML tag to use, adding an alias to the whole sub select (AS 'PulledSupportReasons') gives that subselect a wrapping XML tag equal to that alias defined.