SQL Server 2012 递归更新表XML列并插入TextReadingId节点

原代码问题说明 #

1. 仅能获取单条记录的Step节点总数，未遍历表中所有行，也未遍历单个XML内的全部Step节点，无法实现批量插入。
2. 未实现每个Step对应TextReadingId的序号生成逻辑，仅能给最后一个Step插入固定值。
3. 你提供的建表语句末尾存在笔误，应将}改为)。

实现方案 #

方案1：游标逐节点更新（适合Step结构不统一的场景） #

该方案不会修改原有XML的其他节点结构，兼容性更好：

DECLARE @ToDoId INT, @MaxStep INT, @StepIdx INT
-- 遍历所有表行的游标
DECLARE step_cursor CURSOR FOR
SELECT ToDoId, Data.value('count(/Steplist/Step)', 'INT') 
FROM dbo.tblStepList

OPEN step_cursor
FETCH NEXT FROM step_cursor INTO @ToDoId, @MaxStep

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @StepIdx = 1
    -- 遍历当前行所有Step节点
    WHILE @StepIdx <= @MaxStep
    BEGIN
        UPDATE dbo.tblStepList
        SET Data.modify('insert <TextReadingId>{sql:variable("@StepIdx")}</TextReadingId> after (/Steplist/Step[sql:variable("@StepIdx")]/TextReadingName)[1]')
        WHERE ToDoId = @ToDoId
        SET @StepIdx = @StepIdx + 1
    END
    FETCH NEXT FROM step_cursor INTO @ToDoId, @MaxStep
END

CLOSE step_cursor
DEALLOCATE step_cursor

如果需要所有TextReadingId的值为1，直接把代码中的sql:variable("@StepIdx")替换为1即可匹配你给出的示例结果。

方案2：重建XML（适合数据量大、Step结构统一的场景） #

该方案执行效率远高于逐节点更新：

UPDATE s
SET Data = (
    SELECT 
        Step.value('(StepId)[1]', 'UNIQUEIDENTIFIER') AS StepId,
        Step.value('(Rank)[1]', 'INT') AS Rank,
        Step.value('(IsComplete)[1]', 'VARCHAR(10)') AS IsComplete,
        Step.value('(TextReadingName)[1]', 'VARCHAR(200)') AS TextReadingName,
        -- 要固定值1直接写1，要当前Step的顺序号保留ROW_NUMBER逻辑
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS TextReadingId
    FROM s.Data.nodes('/Steplist/Step') AS T(Step)
    FOR XML PATH('Step'), ROOT('Steplist'), TYPE
)
FROM dbo.tblStepList s

内容的提问来源于stack exchange，提问作者Helen Araya