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.

Answer 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;