is there a way to store the results of an exec statement in a varchar?
DECLARE @TableName varchar(100)
DECLARE @ExecStatement varchar(max)
DECLARE @PromotionXML varchar(max)
SET @TableName = 'Feeds'
Set @ExecStatement = (
'
SET @PromotionXML = (
SELECT
*
FROM
' + @TableName + ' for xml auto, elements
)'
)
exec @ExecStatement
select @PromotionXML
You need to use sp_executesql, not EXEC, since you need to treat the inner variable as an output parameter (which you can't do with EXEC). Also all of these parameters should be NVARCHAR, not VARCHAR (though I'm curious why you aren't returning the xml as XML).
DECLARE
@TableName NVARCHAR(512),
@sql NVARCHAR(MAX),
@xml NVARCHAR(MAX);
SET @TableName = N'dbo.Feeds';
SET @sql = N'SELECT @xml = CONVERT(NVARCHAR(MAX), (
SELECT * FROM ' + @TableName + ' FOR XML AUTO, ELEMENTS));';
EXEC sp_executesql @sql, N'@xml NVARCHAR(MAX) OUTPUT', @xml OUTPUT;
SELECT @xml;