Getting xml as result from exec stored procedure

Question

I'm trying to get the result of an executed stored procedure to be retrieved as a XML of the table the result is shown as originally.

What I'm trying to do is somthing like this.

exec dbo.StoredProcedure FOR XML RAW, ROOT ('root_name').

Lets say exec dbo.StoredProcedure returns the table in the Stored Procedure, I want the FOR XML RAW, ROOT ('root_name') to return the XML value of that whole result.

How do I achieve this In SQL server?

Answer 1

One method is to insert the proc results into a temp table or variable using INSERT...EXEC and then select from that table with the desired FOR XML query:

DECLARE @results AS TABLE(col1 int, col2 int);
INSERT INTO @results EXEC dbo.StoredProcedure;
SELECT col1, col2
FROM @results
FOR XML RAW, ROOT ('root_name');

Answer 2

As per this question, you should not select from the stored procedure. Also if you dig into that question, you will find a link to an interesting article describing options you have with stored procedures.

If I were you I would either return an XML with an output parameter, or insert-exec into a table and then query it with for xml.