How to read and process xml data from stored procedure in sql server?

Question

I am calling a stored procedure like this:

usp_ReportResults @query = '759,905,1048,170,725,80129', 
                  @ReportName = 'GenRepot'

where usp_ReportResult is defined as

CREATE PROCEDURE [dbo].[usp_ReportResults]
     @query VARCHAR(MAX), 
     @ReportName VARCHAR(100), 
     @AutoSelectXML BIT = 1, 
     @XMLResult XML = NULL OUTPUT

I am trying to get result in xml variable to make further process as:

DECLARE @XMLRESULT xml

SET @XMLRESULT = exec usp_ReportResults @query = '759,905,1048,170,725,80129', @ReportName = 'GenRepot'

But I'm not able to get result in @XMLRESULT and unable to read and store data result from xml to table.

Mark Sinkinson · Accepted Answer · 2016-01-20 10:17:52Z

1

You don't need to SET the XMLRESULT.

You should pass it in as an OUTPUT parameter.

https://technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx

DECLARE @XMLRESULT xml;
EXEC usp_ReportResults @query = '759,905,1048,170,725,80129', @ReportName ='GenRepot', @XMLResult = @XMLRESULT OUTPUT;

You can then use the @XMLRESULT parameter, which has been populated with the result of the Stored Procedure.

INSERT INTO dbo.my_table(xml_field)
SELECT @XMLRESULT;

edited Jan 20, 2016 at 10:17

answered Jan 20, 2016 at 10:01

Mark Sinkinson

10.7k4 gold badges47 silver badges54 bronze badges

Add a comment |

Stack Exchange Network

How to read and process xml data from stored procedure in sql server?

1 Answer 1

Your Answer

Hot Network Questions

How to read and process xml data from stored procedure in sql server?

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions