sql-server - 如何将存储过程返回的 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，但我收到错误消息“INSERT 语句中不允许使用 FOR XML 子句。”:

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 上

Answer 1

此页面上有很多从 XML 中选择变量的示例:

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