how to get an xml in a variable

Question

I have a stored procedure that will return xml. I have delared a variable of type xml and trying to execute the following code

 declare @v xml
 set @v = execute get_xml @id, 33

whereas id is returned by another query. now it keeps compalinng about the following error Incorrect syntax near the keyword 'execute'.

Answer 1

Instead of returning it make the XML an OUTPUT parameter and call like

declare @v xml
execute get_xml @id, 33, @v OUTPUT 

The definition of the sp will need to be changed as well. example below.

CREATE PROCEDURE get_xml2 
@id INT, 
@OtherNumber INT, 
@XML XML = NULL OUTPUT
AS


SET @XML = '<blah />'