How to put XML returned by stored procedure in a variable?

I have stored procedure returning XML. XML returned not as parameter but 开发者_开发知识库as result of SELECT:

create procedure #xml_test
as
  select 1 as a for xml raw
go

I'm trying to put this XML in a variable:

declare @xml as nvarchar(max)

But I can't find how to do it. My best idea was INSERT INTO ... EXEC, but I get error 'The FOR XML clause is not allowed in a INSERT statement.':

create table #tmp(col1 nvarchar(max) not null)

insert into #tmp
exec #xml_test

This approach works well for usual text:

create procedure #text_test
as
  select 'aaa' as a 
go

insert into #tmp
exec #text_test

I wonder if somebody bumped into this issue before? I'm on SQL Server 2005

There are quite a few examples of SELECTing from XML into variables on this page:

What's New in FOR XML in Microsoft SQL Server 2005 http://msdn.microsoft.com/en-us/library/ms345137%28SQL.90%29.aspx

The simplest example given is:

DECLARE @cust XML;
SET @cust = (SELECT * FROM Customers FOR XML AUTO, TYPE)

Okay, after suitable admonishment for a silly, ill thought-out comment, here is an answer which I hope is somewhat better. It uses the OPENROWSET to store the results of a stored procedure into a temporary table. From there, the results can be passed to a variable. It's a bit messy, and requires ALTER SETTINGS server-level permission to enable Ad Hoc Distributed Queries.

Anyway, here's the fully tested T-SQL:

CREATE DATABASE db_test;
GO

USE [db_test];
GO

CREATE PROCEDURE xml_test
AS
    SELECT 1 AS a FOR XML RAW
GO

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

SELECT * INTO #tbl_test FROM
    OPENROWSET(
        'SQLNCLI',
        'Server=(local);trusted_connection=yes',
        'set fmtonly off exec db_test.dbo.xml_test') AS tbl_test;
GO

DECLARE @xml_test AS XML;
SET @xml_test = (SELECT * FROM #tbl_test FOR XML RAW, BINARY BASE64);
GO

You might be able to wrap the SELECT so that it sets a local variable and then SELECT that at the end of your stored procedure:

CREATE PROCEDURE xml_test
AS
BEGIN
    DECLARE @xml

    SET @xml = (SELECT 1 AS a FOR XML RAW)

    SELECT @xml AS my_xml
END

There are some gotchas with using FOR XML though, so if you have subqueries, unions, etc. then you might need to rework the query slightly.

This problem kept me busy for some time.

I have a function that generates an xml variable with the diff of two input xml variables.

This is how I'm able to return the result.

CREATE FUNCTION [dbo].[GetXmlDiff]
(
    @id uniqueidentifier,
    @left XML,
    @right XML
)
RETURNS XML
AS
BEGIN
    DECLARE @Delta TABLE
    (
        id uniqueidentifier,
        Delta NVARCHAR( 10 ),
        Attribute NVARCHAR( MAX ),
        Value NVARCHAR( MAX )
    );
        ---
    --- DO THE DIFFING INTO  table VARIABLE
        ---
    DECLARE @ResultVar XML = (
    SELECT * FROM  ( 
                        SELECT  1 AS tag, 
                                null as parent, 
                                id   AS 'o!1!id',
                                null AS 'a!2!delta',
                                null AS 'a!2!name',
                                null AS 'a!2!!element'
                                 FROM @Delta 
                        UNION
                        SELECT  2 as tag, 
                                1 as parent, 
                                id,
                                Delta,
                                Attribute,
                                Value
                                 FROM @Delta 
                    ) Q 
                    FOR XML EXPLICIT );
    RETURN @ResultVar;
END

The function will now generate a XML variable with a content like:

<o id="4CBA2CC4-1FB6-426E-8504-0000468CD7E5">
  <a delta="Add" name="attribute1">value 1</a>
  <a delta="Delete" name="attribute2"></a>
  <a delta="Update" name="attribute3">value 3</a>
 </o>