2

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?

H4p7ic
  • 1,669
  • 2
  • 32
  • 61

2 Answers2

4

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');
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • Just a little side question here, is there anyway to export that xml result to an xml file in the disk with an sql query? – H4p7ic Sep 19 '17 at 12:59
  • @John, although you could do that with SQLCLR or xp_cmdshell ugliness, I suggest you don't go there. It's trivial save the XML with a client app or script, like Powershell. – Dan Guzman Sep 19 '17 at 13:02
  • Ok, Thank you for the info, I will definitely look into my options. – H4p7ic Sep 19 '17 at 13:10
1

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.

PacoDePaco
  • 689
  • 5
  • 16