动态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
上面是动态sql,当它由sp_executesql执行时,我得到了xml格式的输出。 如果我想将该 xml 存储在变量中。那么我需要在 tsql 脚本中添加什么......请帮助
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
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
发布评论
评论(3)
烟火散人牵绊2024-10-21 00:08:34
也试试这个:
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
~没有更多了~
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
对于任何其他试图解决这个问题的人,这里有一些可以使用的示例表。
此脚本将生成的 XML 存储到变量 @XML (原始!)
For anyone else trying to follow the question, here are some sample tables to use
This script stores the generated XML into the variable @XML (original!)