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.
if object_id('tempdb..@temp') is not null
begin
drop table #temp
end
create table #temp
(
col1 int,
col2 varchar(1)
)
insert into #temp
(
col1,
col2
)
exec [secondStoredProcedure] @p1,@p2
declare @xmlResult xml
set @xmlResult =
(
select col1,
col2
from #temp
for xml auto
)
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