SQL Server使用FOR XML创建指定嵌套子标签XML文档方法

原有写法存在两个核心错误：

• 直接将4张表做内连接得到的是按配方行展开的扁平结果集：一件装备对应N种合成材料，结果集就会返回N行，装备、分类的基础信息会在这N行中重复出现。
• FOR XML AUTO模式仅能根据查询中的表引用顺序、连接关系自动推导节点嵌套逻辑，无法自定义要求的「materials、category、equipment、recipes四个平级独立子块」结构，最终必然出现节点重复嵌套、层级错乱的问题。

要实现自定义层级的XML结构，需要使用FOR XML PATH模式配合关联子查询，显式定义每个层级的节点规则：以装备表为主表逐行生成Gear节点，每个子块单独通过关联子查询拉取对应数据，避免多表连接导致的行重复问题。
可直接运行的SQL代码如下：

USE CrafterAlbion
GO
SELECT 
    ROW_NUMBER() OVER(ORDER BY e.id) AS [@id],
    -- 合成材料节点块
    (
        SELECT 
            m.id,
            m.material_name
        FROM Recipe r
        INNER JOIN Materials m ON r.material_id = m.id
        WHERE r.equipment_id = e.id
        FOR XML PATH('material'), ROOT('materials'), TYPE
    ),
    -- 所属分类节点块
    (
        SELECT 
            c.id,
            c.name
        FROM Categories c
        WHERE c.id = e.category_id
        FOR XML PATH('category'), TYPE
    ),
    -- 装备自身信息节点块
    (
        SELECT 
            e.id,
            e.equipment_name,
            e.category_id AS [category]
        FOR XML PATH('equipment'), TYPE
    ),
    -- 合成配方节点块
    (
        SELECT 
            r.equipment_id,
            r.material_id,
            r.material_quantity
        FROM Recipe r
        WHERE r.equipment_id = e.id
        FOR XML PATH('recipe'), ROOT('recipes'), TYPE
    )
FROM Equipment e
FOR XML PATH('Gear'), ROOT('root'), TYPE

• 最外层查询以Equipment表为数据源，保证每件装备仅生成一个Gear节点；通过ROW_NUMBER()窗口函数按装备id排序生成顺序id属性，符合id作为顺序标识、不绑定业务字段的要求。
• 所有子查询添加TYPE关键字，返回原生XML类型数据而非转义后的字符串，避免节点被解析为纯文本。
• materials、recipes属于一对多的数组结构，通过ROOT()参数指定外层包裹节点名，子查询返回的每一行自动生成对应的material/recipe子节点。
• category、equipment属于单对象结构，直接通过PATH()指定节点名，生成单个对象节点。
• 所有子查询通过WHERE条件与外层当前处理的装备id做关联，不会出现跨装备数据串扰；后续新增装备、配方数据时无需修改查询语句，会自动生成对应结构的Gear节点。

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