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

У меня есть хранимая процедура, возвращающая XML. XML возвращается не как параметр, а как результат SELECT:

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

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

declare @xml as nvarchar(max)

Но я не могу найти, как это сделать. Моя лучшая идея была INSERT INTO ... EXEC, но я получаю сообщение об ошибке «Предложение 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


xml variables sql-server stored-procedures
person Alsin    schedule 18.06.2010    source источник
comment
Разве вы не можете просто вернуть его как выходной параметр? Если это так, этот подход должен делать то, что вам нужно stackoverflow.com/questions/914009/   -  person Martin Smith    schedule 18.06.2010
comment
К сожалению, я не могу изменить хранимую процедуру :(   -  person Alsin    schedule 18.06.2010

Ответы (3)


arrow_upward
3
arrow_downward

На этой странице есть довольно много примеров SELECT из 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
person Mike    schedule 18.06.2010
comment
Да, я видел эту статью, когда искал ответ. К сожалению, это касается только SELECT. Мне нужно получить XML из хранимой процедуры. - person Alsin; 18.06.2010
comment
О, я вижу. Я неправильно понял этот момент. Как насчет EXEC @xml = xml_test()? - person Mike; 18.06.2010
comment
Нет, это полная ерунда. Я бы посоветовал вам сделать несколько тестов, прежде чем отвечать. - person Alsin; 18.06.2010
comment
@Mike - Хороший ответ - Не кажется ли возможным избежать кодировки BASE64? - person Martin Smith; 20.06.2010
comment
@Martin Smith: я не нашел способа избежать BASE64 с XML RAW, но это может быть возможно с другими режимами - я не тратил много времени, пытаясь найти решение для этого бита. Однако BASE64 кажется довольно неприятным последствием. - person Mike; 21.06.2010

arrow_upward
0
arrow_downward

Возможно, вы сможете обернуть SELECT, чтобы он устанавливал локальную переменную, а затем 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 есть некоторые проблемы, поэтому, если у вас есть подзапросы, объединения и т. д., вам может потребоваться немного переработать запрос.

person Tom H    schedule 18.06.2010
comment
К сожалению, я не могу изменить хранимую процедуру. - person Alsin; 18.06.2010
comment
Тогда я думаю, что вам не повезло... :( - person Tom H; 18.06.2010

arrow_upward
0
arrow_downward

Эта проблема занимала меня некоторое время.

У меня есть функция, которая генерирует переменную xml с разницей двух входных переменных xml.

Вот как я могу вернуть результат.

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

Теперь функция сгенерирует переменную XML с таким содержимым:

<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>
person Jan Raak    schedule 30.09.2013