如何将XML类型返回文本转换为选择列

Question

我试图使用XML数据类型和information_schema列获取表的列名。当我试图在另一个select语句中使用结果时，我使用的结果是重复的列名，而不是结果集。我甚至试着把它投给varchar，但还是失败了。做错了什么？

DECLARE @TSQL1 varchar(1000);

SELECT @TSQL1 = CAST((SELECT SUBSTRING((SELECT ', ' + QUOTENAME(COLUMN_NAME) 
                      FROM [ProdLS].[ information_schema.columns] 
                      WHERE table_name = 'roles' 
                      ORDER BY ORDINAL_POSITION 
                      FOR XML PATH('')), 3, 200000)) AS varchar(max));

 SELECT @TSQL1 
 FROM [aubeakon_scrm4].[acl_roles]

检索的列名从roles表获取结果的查询。

Answer 1

您不能像这样执行动态SQL。您需要使用sp_executesql。您还需要将动态SQL声明为nvarchar(max)。

您还应该使用.value解除XML。

DECLARE @TSQL1 nvarchar(max) = N'
SELECT 
' + STUFF((
    SELECT ', ' + QUOTENAME(COLUMN_NAME) 
    FROM [ProdLS].[information_schema].columns
    WHERE table_name = 'roles' 
    ORDER BY ORDINAL_POSITION 
    FOR XML PATH(''), TYPE
).value('text()[1]', 'nvarchar(max)'), 1, LEN(', '), '') + '
FROM [aubeakon_scrm4].[acl_roles];
';

EXEC sp_executesql @TSQL1;