动态sql和存储变量的输出。

declare @SQL nvarchar(100)
set @SQL = ''

select @SQL = @SQL + FieldName + ' as [' + Description + '], '
from FieldsInfo
where TableName = 'Emp'

set @SQL = 'select '+ left(@SQL, len(@SQL)-1) + ' from Emp FOR XML AUTO, ELEMENTS, ROOT(''customers'')'

exec sp_executesql @SQL

above is dynamic sql and when it is executed by sp_executesql then i got output as as xml. if i want to store that xml in a variable. so then what i need to add in my tsql script....please help

上面是动态sql，当sp_executesql执行它时，我将输出为xml。如果我想将xml存储在一个变量中。所以我需要添加什么tsql脚本....请帮助

3 个解决方案

#1

For anyone else trying to follow the question, here are some sample tables to use

对于任何试图理解这个问题的人，以下是一些示例表

create table emp (a varchar(10), b int, id int identity)
insert emp select 'abc', 1
insert emp select 'def', 2
create table fieldsinfo (tablename sysname, description sysname, fieldname sysname)
insert fieldsinfo select 'emp', 'field 1', 'a'
insert fieldsinfo select 'emp', 'field 2', 'b'

This script stores the generated XML into the variable @XML (original!)

这个脚本将生成的XML存储到变量@XML(原来!)

declare @SQL nvarchar(max)
set @SQL = ''

select @SQL = @SQL + FieldName + ' as [' + Description + '], '
from FieldsInfo
where TableName = 'Emp'

set @SQL = 'set @XML = (select '+ left(@SQL, len(@SQL)-1) + ' from Emp FOR XML AUTO, ELEMENTS, ROOT(''customers''))'

declare @Xml xml
exec sp_executesql @SQL, N'@XML xml output', @xml output

select 'I have >>> ', @Xml   -- check contents

#2

Try this as well:

试试这个:

declare @SQL nvarchar(1000)
set @SQL = ''

select @SQL = @SQL + FieldName + ' as [' + Description + '], '
from FieldsInfo
where TableName = 'Emp'


DECLARE @ParmDefinition nvarchar(1000);
DECLARE @XMLValueString varchar(1000);
SET @ParmDefinition = N'@XMLValue varchar(1000) OUTPUT';
set @SQL = 'SELECT @XMLValue = (select '+ left(@SQL, len(@SQL)-1) + ' from Emp FOR XML AUTO, ELEMENTS, ROOT(''customers''))'
print @SQL 
exec sp_executesql @SQL,@ParmDefinition, @XMLValue=@XMLValueString output
SELECT @XMLValueString

#3

There isn't really any way of getting from the dynamic sql back to the calling process without it smelling like a huge hack.

从动态sql返回到调用过程中，没有任何方法可以让它闻起来像一个巨大的黑客。

If you absolutely must, I suppose you could have a table that your script could write it's values to, and your proc could then read from.

如果一定要这样做，我认为您可以有一个表，您的脚本可以将它的值写入该表，然后您的proc可以从中读取。

you may want to consider doing your dynamic stuff outside of sql server, but even that is fraught with peril.

您可能想要考虑在sql server之外执行动态操作，但即使这样也会有很大的风险。

#1