Get XML from stored procedure output

How can I convert SP output into XML format.
Here: Similar discussion[^]

Hi,

You may also like to have a look at the best practices. http://msdn.microsoft.com/en-us/library/ms187508%28v=sql.90%29.aspx[^]

Good Luck

Solution 1 - no changes to the second stored procedure
   a. Create temporary table to store the results of the second stored procedure ( the table must have the same structure as the result set from the second stored procedure)
   b. Insert into temp table the result from the second stored procedure
   c. Store data from temp table as xml
   d. Update using the xml variable
E.g.

-- a
if object_id('tempdb..@temp') is not null
   begin
   drop table #temp
   end

-- b
create table #temp
   (
   col1 int,
   col2 varchar(1)
   )
insert into #temp
   (
   col1,
   col2
   )
exec [secondStoredProcedure] @p1,@p2

-- c
declare @xmlResult xml
set @xmlResult =
   (
   select col1,
          col2
   from   #temp
   for xml auto
   )

-- d
-- do your insert/update here using @xmlResult

Solution 2 - change the second stored procedure without affecting application
   a. Add new parameter with default
@returnXml BIT = 0
   b. Add new parameter with OUTPUT
@xmlResult XML=null OUTPUT
   c. Change the body of the second stored procedure

if @returnXml = 1
   begin
   set @xmlResult = 
   (
   select  col1,
           col2
   from tableUsedBySecondStoredProcedure
   for xml auto
   )
   end
else
   begin
   select  col1,
           col2
   from tableUsedBySecondStoredProcedure
   end

   d. in your proc have something like this

declare @xmlResult xml
exec [secondStoredProcedure] @p1,@p2,1,@xmlResult OUTPUT --- @p1,@p2 other parameters use by proc; 1=@returnXml

-- do your insert/update here using @xmlResult