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

Question

Hello

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

Answer 1

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

Answer 2

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.