基于XML列的动态文本While循环：值为空时需终止循环

问题背景 #

当前通过固定上限的嵌套WHILE循环遍历XML列的重复索引，比如外层@id<=91、内层@SecondID<=10，导致生成大量冗余数据（实际仅需约180行却生成910行），性能低下。需要实现：

• 当Coverholder字段为空时，终止外层@id循环
• 当BinderUMR字段为空时，终止内层@SecondID循环

核心优化思路 #

• 外层循环改为无限循环，每次迭代先检查当前索引对应的Coverholder值，为空则直接跳出循环
• 内层循环同理，先检查当前二级索引对应的BinderUMR值，为空则终止内层循环
• 用sql:variable()直接在XPath中引用变量，取消动态SQL拼接，减少性能开销和语法风险

修改后的可运行脚本 #

-- 动态遍历XML重复索引，为空时终止循环
DECLARE @id INT 
DECLARE @SecondID INT 
DECLARE @Coverholder NVARCHAR(MAX)
DECLARE @BinderUMR NVARCHAR(MAX)

SET @id = 1

WHILE 1=1 -- 无限循环，通过条件判断终止
BEGIN
    -- 获取当前@id对应的Coverholder值
    SELECT @Coverholder = audit.value(N'(rowdata[@REPEATINGINDEX=sql:variable("@id")]/Name/text())[1]', N'nvarchar(max)')
    FROM #Dataset t
    CROSS APPLY TransXML.nodes('pagedata/Audit_DirtyList/pxResults[@REPEATINGTYPE="PageList"]') AS pagedata(audit);

    -- Coverholder为空则终止外层循环
    IF @Coverholder IS NULL OR @Coverholder = ''
        BREAK;

    SET @SecondID = 1

    WHILE 1=1 -- 内层无限循环，条件终止
    BEGIN
        -- 获取当前@SecondID对应的BinderUMR值
        SELECT @BinderUMR = audit.value(N'(rowdata[@REPEATINGINDEX=sql:variable("@id")]/pxResults[@REPEATINGTYPE="PageList"]/rowdata[@REPEATINGINDEX=sql:variable("@SecondID")]/BinderUMR/text())[1]', N'nvarchar(max)')
        FROM #Dataset t
        CROSS APPLY TransXML.nodes('pagedata/Audit_DirtyList/pxResults[@REPEATINGTYPE="PageList"]') AS pagedata(audit);

        -- BinderUMR为空则终止内层循环
        IF @BinderUMR IS NULL OR @BinderUMR = ''
            BREAK;

        -- 输出有效数据
        SELECT 
            @id AS ID, 
            @SecondID AS Second_ID, 
            @Coverholder AS [Coverholder],
            @BinderUMR AS [BinderUMR]
        FROM #Dataset t
        CROSS APPLY TransXML.nodes('pagedata/Audit_DirtyList/pxResults[@REPEATINGTYPE="PageList"]') AS pagedata(audit);

        SET @SecondID = @SecondID + 1
    END

    SET @id = @id + 1
END

关键原理说明 #

1. 循环终止逻辑：
   • 外层循环：每次迭代先验证当前索引的Coverholder是否存在，不存在则直接跳出，不再处理后续索引
   • 内层循环：针对每个外层索引，逐个验证二级索引的BinderUMR，不存在则停止当前外层索引的内层遍历
2. 变量引用优化：
   使用sql:variable("@id")和sql:variable("@SecondID")在XPath中直接引用SQL变量，避免了原脚本中拼接动态SQL的繁琐和性能损耗，同时降低了SQL注入风险
3. 冗余数据消除：
   仅当Coverholder和BinderUMR都不为空时才输出数据，彻底避免了空值行的生成，大幅减少输出行数并提升性能

内容的提问来源于stack exchange，提问作者Nicholas Campbell