Как поместить XML, возвращенную хранимой процедурой в переменной?

Question

Как поместить XML, возвращенную хранимой процедурой в переменной?

У меня есть хранимая процедура, возвращая XML. XML, возвращенная не как параметр, но как результат ВЫБОРА:

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

Я пытаюсь поместить этот XML в переменную:

declare @xml as nvarchar(max)

Но я не могу найти, как сделать это. Моей лучшей идеей был INSERT INTO... ДОЛЖНОСТНОЕ ЛИЦО, но я добираюсь, ошибка 'Оператор FOR XML не позволяется в операторе INSERT'.:

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

insert into #tmp
exec #xml_test

Этот подход работает хорошо на обычный текст:

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

insert into #tmp
exec #text_test

Интересно, врезался ли кто-то в эту проблему прежде? Я нахожусь на SQL Server 2005

6

sql-server xml stored-procedures variables

задан Jon Seigel 8 July 2010 в 20:13

2 ответа

Другие вопросы по тегам:

sql-server xml stored-procedures variables

Похожие вопросы:

score 0 · Answer 1

Возможно, вы сможете обернуть SELECT так, чтобы он устанавливал локальную переменную, а затем выбрать ее в конце хранимой процедуры:

CREATE PROCEDURE xml_test
AS
BEGIN
    DECLARE @xml

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

    SELECT @xml AS my_xml
END

Однако есть некоторые проблемы с использованием FOR XML, поэтому если у вас есть подзапросы, союзы и т.д., вам, возможно, придется немного переделать запрос.

score 3 · Answer 2

Есть довольно много примеров SELECTing из XML в переменные на этой странице:

Что нового в FOR XML в Microsoft SQL Server 2005 http://msdn.microsoft.com/en-us/library/ms345137%28SQL.90%29.aspx

Самый простой пример:

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

Ладно, после соответствующего наказания за глупый, непродуманный комментарий, вот ответ, который, я надеюсь, будет немного лучше. Он использует OPENROWSET для хранения результатов хранимой процедуры во временной таблице. Оттуда результаты могут быть переданы в переменную. Это немного сложно и требует разрешения ALTER SETTINGS на уровне сервера, чтобы включить Ad Hoc Distributed Queries.

В любом случае, вот полностью протестированный 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