如何使用T-SQL为XML文档所有缺失revNumber属性的节点添加该属性

实现方案 #

受限于SQL Server XML DML的语法限制，单次modify操作仅能修改一个目标节点，因此循环遍历所有符合条件的节点是适配不定结构XML的最优方案，具体实现如下：

单XML变量处理示例

DECLARE @requestXML XML
-- 赋值原始XML
SET @requestXML = N'
<ol type="decimal" id="id-a4bb3b6d-9908-4972-8b02-72e286795b7a">
    <item id="id-92332716-4561-4874-d353-39fdf993fa89">
        <para id="id-a3fae837-59d8-4702-b689-f869137a92a7">Item1</para>
    </item>
    <item revNumber="1.0" id="id-d8031e83-5b68-47cb-f535-41bee9682f4a">
        <para id="id-8746fe56-3160-4543-bf28-5f1e1de83ec0">Item2</para>
    </item>
</ol>'

DECLARE @nodeCount INT, @currentRevNum DECIMAL(2,1) = 1.1
-- 统计所有未携带revNumber属性的节点总数，//*匹配所有层级任意名称的节点
SET @nodeCount = @requestXML.value('count(//*[not(@revNumber)])', 'INT')

WHILE @nodeCount > 0
BEGIN
    SET @requestXML.modify('
        insert attribute revNumber {sql:variable("@currentRevNum")}
        into (//*[not(@revNumber)])[1]
    ')
    -- 可根据业务需求调整revNumber的生成规则，不需要动态赋值可删除此行
    SET @currentRevNum = @currentRevNum + 0.1
    SET @nodeCount = @nodeCount - 1
END

-- 输出处理结果
SELECT @requestXML

批量更新XmlDocument表示例

如果需要批量更新表中所有XML记录，可以参考以下代码：

DECLARE @docId INT, @xmlContent XML, @currentRev DECIMAL(2,1)
-- 声明游标遍历表数据
DECLARE xmlCur CURSOR FOR
SELECT 你的主键字段名, 你的XML类型字段名 FROM XmlDocument

OPEN xmlCur
FETCH NEXT FROM xmlCur INTO @docId, @xmlContent
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @pendingNodeCount INT = @xmlContent.value('count(//*[not(@revNumber)])', 'INT')
    SET @currentRev = 1.1 -- 可按业务规则调整每条记录的初始版本号
    WHILE @pendingNodeCount > 0
    BEGIN
        SET @xmlContent.modify('
            insert attribute revNumber {sql:variable("@currentRev")}
            into (//*[not(@revNumber)])[1]
        ')
        SET @currentRev = @currentRev + 0.1
        SET @pendingNodeCount = @pendingNodeCount - 1
    END
    -- 回写处理后的XML到表
    UPDATE XmlDocument 
    SET 你的XML类型字段名 = @xmlContent 
    WHERE 你的主键字段名 = @docId

    FETCH NEXT FROM xmlCur INTO @docId, @xmlContent
END
CLOSE xmlCur
DEALLOCATE xmlCur

关键说明 #

• 核心使用XPath语法//*[not(@revNumber)]匹配所有层级、任意名称的未携带revNumber属性的节点，无需提前知道XML结构，完全适配不定结构的XML场景。
• 已存在revNumber属性的节点会自动被过滤，不会被重复修改。
• 若单XML文档节点数超过1000个，可考虑先将XML拆分为关系数据处理后再重组为XML，可获得更高的处理性能。

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