Reputation: 1638
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
Upvotes: 6
Views: 41048
Reputation: 1
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>
Upvotes: 0
Reputation: 21659
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
Upvotes: 3
Reputation: 47402
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.
Upvotes: 0