SQL Server动态拼接列遇XML转NVARCHAR隐式转换错误，求解决

错误原因 #

1. 循环逻辑漏洞：原代码WHILE @I<@NUMBER_OF_COLUMN会漏掉最后一列，且每次循环重复添加同一列两次，导致列列表冗余。
2. XML类型转换限制：CONCAT函数无法隐式将XML类型转为字符串，必须显式用CONVERT函数完成类型转换。

修正后的代码 #

DECLARE @SCHEMA VARCHAR(25) = 'PERSON'
DECLARE @TABLE_NAME VARCHAR(25) = 'PERSON'
DECLARE @I INT = 1
DROP TABLE IF EXISTS #COLUMNS

-- 同时获取列名与数据类型，用于处理XML列
SELECT
  ORDINAL_POSITION,
  COLUMN_NAME,
  DATA_TYPE
INTO #COLUMNS
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLE_NAME AND TABLE_SCHEMA = @SCHEMA -- 增加schema过滤避免表名冲突

DECLARE @NUMBER_OF_COLUMN INT = (SELECT COUNT(*) FROM #COLUMNS)
DECLARE @COLUMNLIST NVARCHAR(MAX) = ''

WHILE @I <= @NUMBER_OF_COLUMN -- 改为<=确保所有列都被处理
BEGIN
    -- 根据数据类型生成列表达式：XML列强制转换，其他列直接使用
    DECLARE @COL_EXPR NVARCHAR(MAX)
    SELECT @COL_EXPR = CASE 
                        WHEN DATA_TYPE = 'xml' THEN CONCAT('CONVERT(NVARCHAR(MAX), ', COLUMN_NAME, ')')
                        ELSE COLUMN_NAME
                      END
    FROM #COLUMNS
    WHERE ORDINAL_POSITION = @I

    -- 拼接列表达式，处理初始为空的情况
    SET @COLUMNLIST = CASE 
                        WHEN @COLUMNLIST = '' THEN @COL_EXPR
                        ELSE CONCAT(@COLUMNLIST, ', ', @COL_EXPR)
                      END
    SET @I = @I + 1
END

-- 生成最终查询，使用schema.table格式更严谨
DECLARE @QUERY NVARCHAR(MAX) = CONCAT('SELECT CONCAT(', @COLUMNLIST, ') AS ConcatenatedResult FROM ', @SCHEMA, '.', @TABLE_NAME)
EXEC sp_executesql @QUERY -- 推荐用sp_executesql替代EXEC，安全性更高

关键修正说明 #

• 新增TABLE_SCHEMA过滤，避免不同schema下重名表的干扰
• 调整循环条件为@I <= @NUMBER_OF_COLUMN，确保所有列都被纳入拼接
• 针对XML类型列，用CONVERT(NVARCHAR(MAX), 列名)显式完成类型转换
• 修复列拼接逻辑，消除重复添加同一列的问题
• 使用sp_executesql执行动态SQL，比直接EXEC更安全，支持后续参数化扩展

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