使用SQL FOR XML子句实现XML同一元素多子节点的方法

你的问题核心是原查询以Miscari（库存变动）表为基础，每一条变动记录都会生成一个独立的<nsSAFT:Account>节点，导致同一产品被拆分到多个节点中。要实现同一产品的所有变动归到单个<nsSAFT:Produs>下，需要用分组嵌套查询来重构XML结构：

优化后的SQL查询 #

WITH XMLNAMESPACES ('uri' as nsSAFT)
SELECT
    (
        -- 生成单个Produs节点，包含产品基础信息和所有变动记录
        SELECT
            RTRIM(P.codProdus) AS 'nsSAFT:CodProdus',
            RTRIM(P.Denumire) AS 'nsSAFT:Denumire',
            (
                -- 嵌套查询生成当前产品的所有Miscari节点
                SELECT STR(M.Cantitate, 18, 2) AS 'nsSAFT:Cantitate'
                FROM Miscari M
                INNER JOIN ProdusGestiune PG ON M.idProdusGestiune = PG.idProdusGestiune
                WHERE PG.idProdus = P.idProdus
                FOR XML PATH('nsSAFT:Miscari'), ELEMENTS, TYPE
            )
        FOR XML PATH('nsSAFT:Produs'), ELEMENTS, TYPE
    )
FROM Produs P
-- 只保留有库存变动的产品，避免生成空节点
WHERE EXISTS (
    SELECT 1
    FROM Miscari M
    INNER JOIN ProdusGestiune PG ON M.idProdusGestiune = PG.idProdusGestiune
    WHERE PG.idProdus = P.idProdus
)
-- 每个产品对应一个Account节点
FOR XML PATH('nsSAFT:Account'), ELEMENTS;

关键逻辑说明 #

1. 以产品表为主表：外层查询从Produs表取唯一产品，确保每个产品只生成一个<nsSAFT:Account>节点。
2. 嵌套生成变动节点：通过子查询关联Miscari表，用FOR XML PATH('nsSAFT:Miscari')为当前产品生成所有库存变动的<nsSAFT:Miscari>子节点，TYPE关键字保证XML结构正确嵌套。
3. 过滤无变动产品：EXISTS子句排除没有任何库存记录的产品，避免生成空的Account节点。

原查询问题分析 #

原查询从Miscari表出发，每一条变动记录都会触发一次<nsSAFT:Account>节点的生成，所以同一产品的多条变动会被拆分成多个独立的Account节点，这是FOR XML PATH按行生成节点的默认行为。

内容的提问来源于stack exchange，提问作者Sven Alexander Gal