FOR XML EXPLICIT Over Multiple Stored Procedures

Hi,
I was wondering if the following was possible in VB/SQL?
To call one stored procedure, eg. sp_Referrals, which in turn calls
two stored procedures that each return their own XML structure. then
to combine these two results sets, before returning them as one
record/output/stream query result?
For example,
sp_CustomerSummary calls
exec sp_GetReferrals @.CustomerId
exec sp_GetReferenceData 'Titles'
which return the following XML respectively:
<Referrals ID="1"><Reason>gfgdfgdfg</Reason><CreationDate>Apr 27 2004
2:01PM</CreationDate><PVDetails>gdfgfdgdfgdf</PVDetails><BenefitType>RP</BenefitType><LSCAction>Yes</LSCAction><PVCategory>Member
of Household</PVCategory></Referrals>
AND
<Reference RefType="Titles"><Option Value="8" Text=""/><Option
Value="7" Text="Dr"/><Option Value="9" Text="Miss"/><Option Value="5"
Text="Mr"/><Option Value="6" Text="Mrs"/></Reference>
they have different structures, obviously, but I would now like to
combine these and return them as one XML as children of a single
<RootNode> and as one recordset.
Is this possible please?
Thanks in advance
Jane
I don't think this is easily possible in SQL Server 2000. But you can do it
in SQL Server 2005 (formerly known as Yukon), out to be in beta in summer.
Note that you will not be able to use stored procs (since they use
side-effects to return data), but you will be able to use user defined
functions...
Best regards
Michael
"Jane" <fitzfreckle@.yahoo.co.uk> wrote in message
news:b709383b.0404280323.46b933be@.posting.google.c om...
> Hi,
> I was wondering if the following was possible in VB/SQL?
> To call one stored procedure, eg. sp_Referrals, which in turn calls
> two stored procedures that each return their own XML structure. then
> to combine these two results sets, before returning them as one
> record/output/stream query result?
> For example,
> sp_CustomerSummary calls
> exec sp_GetReferrals @.CustomerId
> exec sp_GetReferenceData 'Titles'
> which return the following XML respectively:
> <Referrals ID="1"><Reason>gfgdfgdfg</Reason><CreationDate>Apr 27 2004
> 2:01PM</CreationDate><PVDetails>gdfgfdgdfgdf</PVDetails><BenefitType>RP</BenefitType><LSCAction>Yes</LSCAction><PVCategory>Member
> of Household</PVCategory></Referrals>
>
> AND
> <Reference RefType="Titles"><Option Value="8" Text=""/><Option
> Value="7" Text="Dr"/><Option Value="9" Text="Miss"/><Option Value="5"
> Text="Mr"/><Option Value="6" Text="Mrs"/></Reference>
> they have different structures, obviously, but I would now like to
> combine these and return them as one XML as children of a single
> <RootNode> and as one recordset.
> Is this possible please?
> Thanks in advance
> Jane