How to convert a SQL Server result set to XML after the fact?

Is there a way to cause the result set of a SQL Server stored procedure (or any result set, after the fact) to be encoded in XML format?

I want the result set to be encoded in XML as if the FOR XML RAW clause was used during selection.

However the complex stored procedure logic and its internal SELECT statements should not be modified to return XML because the procedure is used for its standard/non-XML result set most of the time.

Update: Emphasis on the fact I'm looking for an answer in the SQL Server environment - the results should be returned as if SQL Server has directly encoded them itself, as XML, just like it does when using the 开发者_如何学Gobuilt-in XML features like the FOR XML clause.

You would insert the data from the SP into a temp table, then select from that FOR XML

This won't work if the SP itself already does a INSERT .. EXEC SPROC because you cannot nest them

Working examples

use tempdb;
create proc giveme
as
select a = 1, b = GETDATE()
union all
select 2, b = '20100101'

Using INSERT.. EXEC

declare @t table (a int, b datetime)
insert @t
exec giveme
select * from @t for xml raw

Using OPENQUERY

exec sp_addlinkedserver 'localhost'

exec sp_serveroption @server = 'localhost' 
   ,@optname = 'DATA ACCESS' 
   ,@optvalue = 'TRUE' 

select *
from openquery(localhost, 'exec tempdb..giveme')
for xml raw

You could try using OPENROWSET in cooperation with FOR XML to do the transformation.

By 'after the fact', do you mean still within the SQL Server environment? Or are you talking about a client program?

Within SQL, you could probably write a sproc that acts as a wrapper for your other sprocs, along these lines. The wrapper sproc would handle the FOR XML work.

In .NET, there are a number of ways to do this.

You can try inserting the result set from the stored procedure into a table variable( or temporary table) and selecting the table rows with the FOR XML clause.

Here is an example:

DECLARE @MyDataTable AS TABLE ( col1 int,...., colN int)

Make sure that the @MyDataTable has the same columns as the stored procedure result set(s).

INSERT INTO @MyDataTable 
EXECUTE mysp_GetData @param1=value,....,@paramN;

SELECT * FROM  @MyDataTable
FOR XML AUTO