CODEWITHSUNDEEP
sqlsql-serverxmlsql-server-2008t-sql
BoogaWooga
BoogaWooga

Reputation: 108

How can I pass xml data from one sproc to another?

I have 2 sprocs for an assignment, what I'm trying to do is pass the xml output from one sproc to another and put it in a variable, I know ex1.xml_sp1 is returning an int while calling it with EXEC and obviously when trying to select this it returns null because @x is xml data type.

What I want to do is retrieve and store the xml data from sproc 1 in to @x in sproc 2.

Is there any way to do this?

sproc 1: 

ALTER PROC [ex1].[xml_sp1]
@careteamid int
as

select CareTeams.CareTeamID, Doctors.DoctorID, Doctors.DoctorName,
   CareTeamDoctors.DateJoined, CareTeamDoctors.CurrentMember
from dbo.CareTeamTbl as CareTeams
inner join dbo.CareTeamDoctorTbl as CareTeamDoctors on
CareTeams.CareTeamID = CareTeamDoctors.CareTeamID
inner join dbo.DoctorTbl as Doctors on
CareTeamDoctors.DoctorID=CareTeamDoctors.DoctorID
where CareTeamDoctors.CareTeamID = @careteamid
and CareTeamDoctors.DoctorID = Doctors.DoctorID
for xml auto, root('thedata')

sproc 2:

ALTER PROC [ex1].[xml_sp2]
@careteamid int
as 
declare @x xml

exec @x = ex1.xml_sp1
          @careteamid = @careteamid

select @x as XMLData

Upvotes: 2

Views: 71

Answers (4)

Solomon Rutzky
Solomon Rutzky

Reputation: 48864

For the return value (i.e. EXEC @ReturnValue = StoredProcName...;), INT is the only datatype allowed. If this needs to really stay as a Stored Procedure then you can either use an OUTPUT variable or create a temp table or table variable in the second Stored Procedure and do INSERT INTO ... EXEC StoredProc1;.

However, given that the first Stored Procedure is only doing a simple SELECT statement, you would be far better off converting this to be an Inline Table-Valued Function (iTVF) as follows:

CREATE FUNCTION dbo.GetData (@CareTeamID INT)
RETURNS TABLE
AS RETURN

SELECT tab.col AS [CareData]
FROM (    
    SELECT CareTeams.CareTeamID, Doctors.DoctorID, Doctors.DoctorName,
           CareTeamDoctors.DateJoined, CareTeamDoctors.CurrentMember
    FROM   dbo.CareTeamTbl as CareTeams
    INNER JOIN dbo.CareTeamDoctorTbl as CareTeamDoctors
            ON  CareTeams.CareTeamID = CareTeamDoctors.CareTeamID
    INNER JOIN dbo.DoctorTbl as Doctors
            ON CareTeamDoctors.DoctorID=CareTeamDoctors.DoctorID
    WHERE  CareTeamDoctors.CareTeamID = @CareTeamID
    AND    CareTeamDoctors.DoctorID = Doctors.DoctorID
    FOR XML AUTO, ROOT('thedata'), TYPE -- "TYPE" causes result to be XML and not NVARCHAR
     ) tab(col);

Then just change the second stored procedure to be:

SELECT  care.CareData AS [XMLData]
FROM    dbo.GetData(@CareTeamID);

Or, if you actually need to make use of that XML data in the second Stored Procedure, do the following:

DECLARE @TempData XML;

SELECT  @TempData = care.CareData
FROM    dbo.GetData(@CareTeamID);

Finally, if you need the XML data to actually be of the XML datatype coming out of that iTVF (or even if you keep it as a Stored Procedure), then you need to add the TYPE option to the FOR XML clause, else what is returned is a string / NVARCHAR(MAX):

FOR XML AUTO, ROOT ('thedata'), TYPE

Upvotes: 3

Lukasz Szozda
Lukasz Szozda

Reputation: 175964

I want to do is retrieve and store the xml data from sproc 1 in to @x in sproc 2.

You could achieve it very easily using OUTPUT parameters:

CREATE PROCEDURE [xml_sp1]
   @careteamid INT,
   @xml_output XML OUTPUT
AS
BEGIN
   SET @xml_output = (SELECT * FROM ... FOR XML AUTO, root('thedata'));
END;
GO

CREATE PROCEDURE [xml_sp2]
   @careteamid INT
AS
BEGIN
   DECLARE @x XML;

   EXEC [xml_sp1]
     @careteamid,
     @x OUTPUT;

   SELECT @x AS XMLData;
END;
GO

And final call:

EXEC [xml_sp2] @careteamid = 1;

LiveDemo

Consider using BEGIN/END block and end each statement with ; to avoid possible nasty problems.

The full list of possible sharing data methods How to Share Data between Stored Procedures by Erland Sommarskog

Upvotes: 3

TheGameiswar
TheGameiswar

Reputation: 28930

Why not try this instead of assigning output of sp1 directly 

 declare  @t table
    (
    val xml
    )

    insert into  @t
    exec ex1.xml_sp1
    @careteamid = @careteamid

    select * from @t

Upvotes: 2

KonB
KonB

Reputation: 220

Will this work?

declare @xml xml
set @xml = (select *
            from tableABC
            for xml auto, elements)

And then pass that variable to another sproc?

I go the answer from here

How to insert FOR AUTO XML result into table?.

Seems to me the idea is similar, or should work similar.

Upvotes: -1

Related Questions