如何使用SQL Server FOR XML生成可扩展层级结构XML文档

核心逻辑 #

整个查询以Equipment表为驱动表，无需硬编码任何装备ID，所有表关联逻辑自动拉取关联数据，新增装备、配方、材料、分类数据时无需修改查询代码，即可自动生成对应结构的XML节点。

• Gear节点的id属性通过窗口函数ROW_NUMBER() OVER(ORDER BY 装备表.id)生成顺序序号，仅做节点顺序标识，不绑定业务表主键，新增装备时会自动生成连续序号
• 内部四类子节点全部通过关联子查询+嵌套FOR XML PATH生成：
  • materials节点：自动拉取当前装备所有配方关联的材料
  • category节点：自动拉取当前装备所属的分类信息
  • equipment节点：输出当前装备自身的基础信息
  • recipes节点：自动拉取当前装备对应的所有配方条目

可直接执行的查询代码 #

USE tempdb;
GO

SELECT
    ROW_NUMBER() OVER(ORDER BY e.id) AS [@id],
    -- 构建materials子节点
    (
        SELECT
            m.id,
            m.material_name
        FROM dbo.Recipe r
        INNER JOIN dbo.Materials m 
            ON r.material_id = m.id
        WHERE r.equipment_id = e.id
        FOR XML PATH('material'), ROOT('materials'), TYPE
    ),
    -- 构建category子节点
    (
        SELECT
            c.id,
            c.name
        FROM dbo.Categories c
        WHERE c.id = e.category_id
        FOR XML PATH('category'), TYPE
    ),
    -- 构建equipment子节点
    (
        SELECT
            e.id,
            e.equipment_name,
            e.category_id AS [category]
        FOR XML PATH('equipment'), TYPE
    ),
    -- 构建recipes子节点
    (
        SELECT
            r.equipment_id,
            r.material_id,
            r.material_quantity
        FROM dbo.Recipe r
        WHERE r.equipment_id = e.id
        FOR XML PATH('recipe'), ROOT('recipes'), TYPE
    )
FROM dbo.Equipment e
FOR XML PATH('Gear'), ROOT('root'), TYPE;

扩展验证说明 #

基于提供的样例数据运行上述代码，会自动生成对应数量的Gear节点，结构完全匹配要求的XML层级。后续新增装备（比如Guardian Armor）时，只需要按照表结构往四个业务表中写入对应数据，再次运行查询就会自动生成序号顺延的新Gear节点，不需要调整任何查询逻辑。

如果需要固定材料、配方的输出顺序，可以在对应子查询中增加ORDER BY子句自定义排序规则。

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