Re: FOR XML EXPLICIT Over Multiple Stored Procedures

From: Michael Rys [MSFT] (mrys_at_online.microsoft.com)
Date: 04/28/04 

Date: Wed, 28 Apr 2004 10:50:06 -0700

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.com...
> 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

Relevant Pages

  • Re: Cannot fetch a row from OLE DB provider "BULK" for linked serv
    ... the top of my stored procedure in T-SQL by pointing to the XML file? ... have the procedure in SQL Server to have an nvarcharparameter ...
    (microsoft.public.sqlserver.programming)
  • Re: HIT and MISS
    ... clear both data cache and buffer cache but still does ... Create a stored procedure demo_sp as dbo, and grant exec rights to some ... This is because SQL Server first looks up if the user owns a procedure ...
    (comp.databases.ms-sqlserver)
  • =?Utf-8?Q?Re:_XML_and_Pound_=28=C2=A3=29_Symbol?=
    ... To be honest we were looking at moving away from XML anyway (too clunky and ... DECLARE @m VARCHAR ... exec sp_xml_preparedocument @h OUTPUT, @m ... for the string you are passing into SQL Server. ...
    (microsoft.public.sqlserver.xml)
  • Re: How to update using OpenXML in a stored proc?
    ... it as a stored procedure argument to a TEXT or NTEXT parameter. ... In SQL Server 2005, you can use OpenRowset. ... What if the XML that you showed in this example is in a text file ...
    (microsoft.public.sqlserver.xml)
  • Re: Store data horizontally or vertically?
    ... > 3- 1 individual table per stored procedure, ... The other extreme is to say that each procedure is an object of its own, ... that has some appeal to me is to use XML. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
Quantcast