Return results from 'FOR XML EXPLICIT' in stored procedure output param

Question

I have created a SQL Server 2000 stored procedure to return some XML data using FOR XML EXPLICIT. This is working fine in itself, however I'm having trouble figuring out how to get the XML results returned by the stored procedure in an output parameter (as I want to call this one from another SP).

I've tried options such as the following but just can't seem to get it to work!

CREATE PROCEDURE [dbo].[MyProc]
    @user_index INT,
    @xml_data   VARCHAR(8000) OUTPUT
AS
BEGIN
    SELECT  @xml_data = 
             1 AS Tag
            ,0 AS Parent
            ...
            ...

    UNION ALL

    SELECT   2 AS Tag
            ,1 AS Parent
            ...
            ...
    FOR XML EXPLICIT
END

Any ideas where I'm going wrong?

Answer 1

Your FOR XML query needs to be a subquery, like this:

CREATE PROCEDURE [dbo].[MyProc]     
  @user_index INT,     
  @xml_data   VARCHAR(8000) OUTPUT 
AS 
BEGIN     
  SELECT  @xml_data =               
  (
    SELECT 
       1 AS Tag             
      ,0 AS Parent             
...             
...      
    UNION ALL      
    SELECT   
       2 AS Tag             
      ,1 AS Parent             
...             
...     FOR XML EXPLICIT 
  )
END 

---

ERRATUM: I just noticed your reference to SQL Server 2000. There is no XML data type in SQL Server 2000, so this doesn't work. If I remember correctly, FOR XML in that version can only stream the result to the TDS stream. You cannot capture the output within the database procedure.

So what you are trying to do is impossible on SQL Server 2000.