SQL Server 2016中无需枚举列生成指定XML输出的方法

方法1：XQuery转换原始XML #

先将查询结果生成原生XML，再通过XQuery遍历所有列节点，重构为目标格式。

单行/多行通用示例 #

-- 替换为你的查询语句
DECLARE @SourceQuery NVARCHAR(MAX) = N'SELECT FirstName, LastName, Age FROM YourTable';
DECLARE @OriginalXML XML;

-- 执行查询生成原始XML
EXEC sp_executesql N'SELECT @Result = (' + @SourceQuery + ' FOR XML PATH(''Row''), ROOT(''Root''), TYPE)',
    N'@Result XML OUTPUT',
    @Result = @OriginalXML OUTPUT;

-- 转换为目标XML格式
SELECT
    @OriginalXML.query('
        for $row in /Root/Row
        return
            <Row>
                {
                    for $col in $row/*
                    return
                        <Answer name="{local-name($col)}">
                            <value>{data($col)}</value>
                        </Answer>
                }
            </Row>
    ') AS TargetXML;

此方案无需手动枚举列，XQuery会自动处理原始XML中的所有列节点，适配任意列数的查询结果。

方法2：动态SQL自动生成XML结构 #

通过系统视图sys.columns自动获取目标表的列名，动态拼接SQL生成目标XML，避免手动列枚举。

SQL Server 2016 兼容版本（替代STRING_AGG） #

DECLARE @TableName NVARCHAR(128) = N'YourTable';
DECLARE @SQL NVARCHAR(MAX);

-- 动态拼接列对应的XML节点
SELECT @SQL = N'
SELECT
    ' + STUFF((
        SELECT N', <Answer name="' + QUOTENAME(c.name, '"') + '"><value>' + QUOTENAME(c.name) + '</value></Answer>'
        FROM sys.columns c
        WHERE c.object_id = OBJECT_ID(@TableName)
        ORDER BY c.column_id
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + N'
FROM ' + QUOTENAME(@TableName) + N'
FOR XML PATH(''''), ROOT(''Answers'')';

-- 执行动态SQL生成XML
EXEC sp_executesql @SQL;

如果你的查询不是直接从单表读取，而是复杂查询，可将复杂查询结果存入临时表，再对临时表使用上述动态SQL逻辑。

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