Search code examples
sql-serverxmlstored-proceduresfor-xml

Getting xml as result from exec stored procedure

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?

Solution

  • 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');