如何将存储过程返回的XML放入变量中？

Question

我有一个返回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。

- Alsin

---

你不能只将它作为输出参数返回吗？如果可以的话，这种方法应该可以满足你的需求。https://dev59.com/8XNA5IYBdhLWcg3wjOre - Martin Smith

很遗憾，我无法更改存储过程 :( - Alsin

Answer 1

这个页面上有很多关于从XML中SELECT到变量的例子：

Microsoft SQL Server 2005中FOR XML的新功能 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分布式查询。

无论如何，以下是完全测试过的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

- Mike

---

是的，当我在寻找答案时，我看到了这篇文章。不幸的是，它仅涉及SELECTs。我必须从存储过程中获取XML。 - Alsin

哦，我明白了。我误解了那一点。那么"EXEC @xml = xml_test()"怎么样？ - Mike

不，这完全是胡说八道。我建议你在回答之前进行一些测试。 - Alsin

@Mike - 很好的回答 - 然而似乎无法避免使用BASE64编码呢？ - Martin Smith

@Martin Smith：我没有找到避免使用XML RAW的BASE64的方法，但是可能可以通过其他模式实现-我没有花太多时间尝试找到解决方案。不过，BASE64似乎是一个相当不幸的后果。 - Mike

Answer 2

你可以尝试将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时，有一些需要注意的地方，如果你有子查询、联合等情况，可能需要稍微调整查询语句。

- Tom H

---

很遗憾，我无法更改存储过程。 - Alsin

那我想你运气不好了... :( - Tom H

Answer 3

这个问题让我忙了一段时间。

我有一个函数，可以生成一个包含两个输入 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>

---

---

- Jan Raak