如何将XML类型返回的文本转换为SELECT语句的查询列

问题根源 #

你现有代码的核心错误是混淆了字符串变量和SQL执行语句的区别：

• 你赋值给@TSQL1的是拼接好的列名字符串，比如id, name, permission这类文本
• 直接写SELECT @TSQL1 FROM [aubeakon_scrm4].[acl_roles]时，SQL会把@TSQL1当做固定字符串值返回，目标表有多少行就会返回多少行相同的列名字符串，不会把它解析为要查询的字段。

另外还有两个隐藏问题：

• 声明@TSQL1为varchar(1000)，后续又强转为varchar(max)，长度不匹配可能导致列名拼接结果被截断
• 你从ProdLS库的roles表取列名，但是最终查询的是aubeakon_scrm4库的acl_roles表，要确认两个表的列完全一致，否则执行会报字段不存在错误

修正方案 #

你需要用动态SQL来执行拼接好的查询语句，修正后的代码如下，同时优化了XML拼接的转义问题：

DECLARE @TSQL1 NVARCHAR(MAX); -- 用MAX长度避免截断，NVARCHAR适配特殊字符

-- 拼接列名，加TYPE避免特殊字符被XML转义
SELECT @TSQL1 = SUBSTRING((
    SELECT ', ' + QUOTENAME(COLUMN_NAME) 
    FROM [ProdLS].[information_schema.columns] 
    WHERE table_name = 'roles' 
    ORDER BY ORDINAL_POSITION 
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 3, 200000);

-- 拼接完整查询语句并执行动态SQL
SET @TSQL1 = N'SELECT ' + @TSQL1 + N' FROM [aubeakon_scrm4].[acl_roles]';
EXEC sp_executesql @TSQL1;

注意：如果ProdLS库下有多个Schema都存在roles表，建议在查询information_schema.columns时增加TABLE_SCHEMA = '你的Schema名'的过滤条件，避免取到错误的列名。

内容的提问来源于stack exchange，提问作者Richard