SQL Server 2012使用FLWOR关联外部表为XML列插入TextReadingId节点

首先假设存储StepId和TextReadingId映射关系的辅助表名为dbo.StepMapping，结构如下：

CREATE TABLE dbo.StepMapping (
    StepId uniqueidentifier NOT NULL PRIMARY KEY,
    TextReadingId INT NOT NULL
)

你可以用以下两种方案实现需求：

方案1：逐节点更新（适合单条XML内Step节点较少的场景） #

通过循环遍历每条记录的每个Step节点，关联映射表后插入对应节点：

DECLARE @ToDoId INT, @StepCount INT, @CurrentStepIdx INT, @StepId uniqueidentifier, @TextReadingId INT
-- 游标遍历tblStepList的每一行
DECLARE cur_step CURSOR FOR
SELECT ToDoId, Data.value('count(/Steplist/Step)', 'INT') AS StepCount
FROM dbo.tblStepList

OPEN cur_step
FETCH NEXT FROM cur_step INTO @ToDoId, @StepCount

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @CurrentStepIdx = 1
    -- 遍历当前行的每个Step节点
    WHILE @CurrentStepIdx <= @StepCount
    BEGIN
        -- 取当前Step的StepId
        SELECT @StepId = Data.value('(/Steplist/Step[sql:variable("@CurrentStepIdx")]/StepId)[1]', 'uniqueidentifier')
        FROM dbo.tblStepList WHERE ToDoId = @ToDoId

        -- 关联映射表取对应TextReadingId
        SELECT @TextReadingId = TextReadingId
        FROM dbo.StepMapping WHERE StepId = @StepId

        -- 若存在映射则插入节点，放在TextReadingName节点之后
        IF @TextReadingId IS NOT NULL
        BEGIN
            UPDATE dbo.tblStepList
            SET Data.modify('insert <TextReadingId>{sql:variable("@TextReadingId")}</TextReadingId> after (/Steplist/Step[sql:variable("@CurrentStepIdx")]/TextReadingName)[1]')
            WHERE ToDoId = @ToDoId
        END

        SET @CurrentStepIdx = @CurrentStepIdx + 1
    END
    FETCH NEXT FROM cur_step INTO @ToDoId, @StepCount
END

CLOSE cur_step
DEALLOCATE cur_step

方案2：重构XML（适合数据量较大、性能要求高的场景） #

直接将XML节点拆解后关联映射表，重新生成完整XML再更新回原表，效率远高于逐节点修改：

UPDATE sl
SET Data = (
    SELECT
        s.value('(StepId)[1]', 'uniqueidentifier') AS StepId,
        s.value('(Rank)[1]', 'INT') AS Rank,
        s.value('(IsComplete)[1]', 'VARCHAR(10)') AS IsComplete,
        s.value('(TextReadingName)[1]', 'VARCHAR(200)') AS TextReadingName,
        sm.TextReadingId AS TextReadingId
    FROM sl.Data.nodes('/Steplist/Step') AS t(s)
    LEFT JOIN dbo.StepMapping sm ON sm.StepId = s.value('(StepId)[1]', 'uniqueidentifier')
    FOR XML PATH('Step'), ROOT('Steplist'), TYPE
)
FROM dbo.tblStepList sl

原代码问题说明 #

你原来的代码只取了单条记录的Step节点总数量，没有遍历所有节点、也没有关联映射表取值，因此无法得到预期结果。

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