SQL Server中基于层级结构更新XML内ItemNumber的方法咨询

如果层级数据已存储在关系表中（例如表HierarchyItems，含Id、ParentId、ItemNumber字段），可通过递归CTE遍历层级结构，生成带点分隔的层级编号，再执行更新：

WITH HierarchyCTE AS (
    -- 根节点：ParentId为NULL/0的条目，按指定顺序生成一级编号
    SELECT 
        Id,
        ParentId,
        CAST(ROW_NUMBER() OVER(ORDER BY Id) AS VARCHAR(MAX)) AS HierarchyNumber
    FROM HierarchyItems
    WHERE ParentId IS NULL OR ParentId = 0

    UNION ALL

    -- 递归处理子节点：拼接父节点编号与当前节点的组内序号
    SELECT 
        child.Id,
        child.ParentId,
        CAST(parent.HierarchyNumber + '.' + CAST(ROW_NUMBER() OVER(PARTITION BY child.ParentId ORDER BY child.Id) AS VARCHAR(MAX)) AS VARCHAR(MAX))
    FROM HierarchyItems child
    INNER JOIN HierarchyCTE parent ON child.ParentId = parent.Id
)
UPDATE hi
SET hi.ItemNumber = cte.HierarchyNumber
FROM HierarchyItems hi
INNER JOIN HierarchyCTE cte ON hi.Id = cte.Id;

关键说明 #

• 根节点的排序字段可根据业务需求替换（例如SortOrder），确保编号顺序符合预期
• 递归逻辑自动支持多层级，可生成1.1.1、1.2.3这类嵌套编号
• 若数据量较大，需给ParentId字段创建索引，提升递归CTE的执行效率

如果层级数据直接存储在XML列中（例如表XmlHierarchy，含Id、HierarchyXml字段），可结合递归CTE提取节点路径，再用XQuery更新ItemNumber值。假设XML结构如下：

<Items>
    <Item>
        <Id>1</Id>
        <ItemNumber>1</ItemNumber>
        <Children>
            <Item>
                <Id>2</Id>
                <ItemNumber>2</ItemNumber>
            </Item>
        </Children>
    </Item>
</Items>

执行以下SQL完成更新：

WITH XmlNodesCTE AS (
    -- 提取根节点信息
    SELECT
        Id AS TableId,
        CAST('/Items/Item[' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS VARCHAR) + ']' AS VARCHAR(MAX)) AS NodePath,
        CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS VARCHAR(MAX)) AS ItemNumber,
        HierarchyXml.query('Items/Item') AS NodeXml,
        HierarchyXml
    FROM XmlHierarchy
    WHERE HierarchyXml.exist('Items/Item') = 1

    UNION ALL

    -- 递归提取子节点信息
    SELECT
        parent.TableId,
        CAST(parent.NodePath + '/Children/Item[' + CAST(ROW_NUMBER() OVER(PARTITION BY parent.NodePath ORDER BY (SELECT NULL)) AS VARCHAR) + ']' AS VARCHAR(MAX)),
        CAST(parent.ItemNumber + '.' + CAST(ROW_NUMBER() OVER(PARTITION BY parent.NodePath ORDER BY (SELECT NULL)) AS VARCHAR) AS VARCHAR(MAX)),
        child.Node.query('.') AS NodeXml,
        parent.HierarchyXml
    FROM XmlNodesCTE parent
    CROSS APPLY parent.NodeXml.nodes('Item/Children/Item') AS child(Node)
)
-- 更新XML中的ItemNumber节点
UPDATE XmlHierarchy
SET HierarchyXml.modify('
    replace value of (/Items/Item' + REPLACE(NodePath, '/Items/Item', '') + '/ItemNumber/text())[1]
    with sql:column("cte.ItemNumber")
')
FROM XmlHierarchy xh
INNER JOIN XmlNodesCTE cte ON xh.Id = cte.TableId;

关键说明 #

• 若XML结构不同（例如子节点不是Children标签），需调整XPath表达式适配实际结构
• 大体积XML建议先拆分为关系表处理，再重新生成XML，性能优于直接操作XML列

• 关系表场景：给ParentId、排序字段（如Id/SortOrder）创建索引，避免全表扫描
• XML场景：优先拆分XML为关系表处理，递归CTE对关系型数据的处理效率远高于XML

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