如何将SQL表转换为首行含列名的XML表？求更优实现方案

我需要将SQL表中的数据转换为特定格式的XML。现有示例表结构如下：

SELECT x.*
INTO #t
FROM (VALUES (1, 'John', 'Doe')) x(Id, FirstName, LastName)

SELECT *
FROM #t

DROP TABLE #t

期望得到的XML输出格式如下：

<Table>
  <Row>
    <Column>Id</Column>
    <Column>FirstName</Column>
    <Column>LastName</Column>
  </Row>
  <Row>
    <Column>1</Column>
    <Column>John</Column>
    <Column>Doe</Column>
  </Row>
</Table>

目前通过UNION和XML PATH实现了需求，但操作繁琐。请问是否有更优雅的实现方案？能否无需使用UNION，仅通过XML PATH完成转换？

可以通过查询系统视图动态获取列名，结合FOR XML PATH分两部分生成XML再合并，全程无需UNION，代码更简洁且易于维护：

-- 创建示例临时表
SELECT x.*
INTO #t
FROM (VALUES (1, 'John', 'Doe')) x(Id, FirstName, LastName)

-- 生成目标XML
SELECT 
    -- 生成列名行
    (SELECT 
        COLUMN_NAME AS [Column]
     FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_NAME = '#t' 
       AND TABLE_SCHEMA = 'dbo'
     ORDER BY ORDINAL_POSITION
     FOR XML PATH(''), ROOT('Row'), TYPE)
    ,
    -- 生成数据行
    (SELECT 
        Id AS [Column],
        FirstName AS [Column],
        LastName AS [Column]
     FROM #t
     FOR XML PATH('Row'), TYPE)
FOR XML PATH(''), ROOT('Table')

-- 清理临时表
DROP TABLE #t

关键说明： #

• 列名行生成：利用INFORMATION_SCHEMA.COLUMNS系统视图自动获取临时表的列定义，按字段顺序生成包含所有列名的<Row>节点，无需手动硬编码列名。
• 数据行生成：直接查询表数据，将每个字段映射为<Column>节点，FOR XML PATH('Row')会自动为每行数据包裹<Row>标签。
• XML合并：将列名行和数据行的XML结果通过外层的FOR XML PATH(''), ROOT('Table')组合，最终生成符合要求的完整XML结构。

扩展优化（动态适配任意表） #

如果需要适配任意表，避免硬编码字段名，可以用动态SQL实现：

DECLARE @TableName NVARCHAR(128) = '#t'
DECLARE @Columns NVARCHAR(MAX)

-- 拼接数据查询的字段列表
SELECT @Columns = STRING_AGG(QUOTENAME(COLUMN_NAME) + ' AS [Column]', ', ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName 
  AND TABLE_SCHEMA = 'dbo'
ORDER BY ORDINAL_POSITION

DECLARE @Sql NVARCHAR(MAX) = N'
SELECT 
    (SELECT 
        COLUMN_NAME AS [Column]
     FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_NAME = ''' + @TableName + ''' 
       AND TABLE_SCHEMA = ''dbo''
     ORDER BY ORDINAL_POSITION
     FOR XML PATH(''''), ROOT(''Row''), TYPE)
    ,
    (SELECT ' + @Columns + '
     FROM ' + @TableName + '
     FOR XML PATH(''Row''), TYPE)
FOR XML PATH(''''), ROOT(''Table'')'

EXEC sp_executesql @Sql

这个版本无需修改字段名，新增或修改表结构时自动适配，通用性更强。

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