FOR XML EXPLICIT父标签未先打开间歇性报错求助

问题现象 #

执行包含FOR XML EXPLICIT的SQL查询时，间歇性触发两类错误：

• 父标签ID 10不在已打开的标签中。FOR XML EXPLICIT要求父标签必须先打开，请检查结果集的排序。
• 父标签ID 2不在已打开的标签中。FOR XML EXPLICIT要求父标签必须先打开，请检查结果集的排序。

错误并非每次执行都会出现，相关SQL代码如下：

select 1         as Tag,                    
       null   as Parent,                    
                           
       null        as [ScheduleGroups!1],                    
       @HasScheduleTrackingFunctionAccess as [ScheduleGroups!1!HasScheduleTrackingFunctionAccess],                    
                        
       null        as [ScheduleGroup!2!GroupCode],                    
       null        as [ScheduleGroup!2!GroupName],                    
       null        as [ScheduleGroup!2!DisplayOrder],                    
       null        as [ScheduleGroup!2!Visible],                    
       null        as [ScheduleGroup!2!CanChangeDisplayOrder],                    
                           
       null        as [Row!3!RowID],                    
                null                                as [Row!3!BaseScheduleCode],                    
       null        as [Row!3!ScheduleCode],                    
       null        as [Row!3!ScheduleName],                    
       null     as [Row!3!DisplayOrder],                    
       null         as [Row!3!IsMandatory],                    
       null         as [Row!3!InUse],                    
       null         as [Row!3!IsTracked],                    
       null        as [Row!3!Status],                    
       null        as [Row!3!ScheduleGroup],                    
       null        as [Row!3!CanChangeGroup],                    
       null        as [Row!3!ReadOnly],                       
                           
       null        as [ApplicableGroups!10],                    
       null        as [ApplicableGroup!11!GroupCode],                    
       null        as [ApplicableGroup!11!GroupName],                    
       null        as [ApplicableGroup!11!DisplayOrder]                    
                         
     union ALL                    
                         
     -- Custom menu from list table                    
     select 2         as Tag,                    
       1         as Parent,                    
                           
       null        as [ScheduleGroups!1],                    
       @HasScheduleTrackingFunctionAccess as [ScheduleGroups!1!HasScheduleTrackingFunctionAccess],                    
                           
       tsg.GroupCode      as [ScheduleGroup!2!GroupCode],                    
       tsg.GroupName      as [ScheduleGroup!2!GroupName],                    
       coalesce(m.DisplayOrder,1)   as [ScheduleGroup!2!DisplayOrder],                    
       1         as [ScheduleGroup!2!Visible],                    
       1         as [ScheduleGroup!2!CanChangeDisplayOrder],                    
                           
       null        as [Row!3!RowID],                    
       null        as [Row!3!BaseScheduleCode],                
       null        as [Row!3!ScheduleCode],                    
       null        as [Row!3!ScheduleName],                    
       null        as [Row!3!DisplayOrder],                    
       null         as [Row!3!IsMandatory],                    
       null         as [Row!3!InUse],                    
       null         as [Row!3!IsTracked],                    
       null        as [Row!3!Status],                    
       null        as [Row!3!ScheduleGroup],                    
       null        as [Row!3!CanChangeGroup],                    
       null     as [Row!3!ReadOnly],                    
                           
       null        as [ApplicableGroups!10],                    
       null        as [ApplicableGroup!11!GroupCode],                    
       null        as [ApplicableGroup!11!GroupName],                    
       null        as [ApplicableGroup!11!DisplayOrder]                    
                           
     from                    
       dbo.tbl_MenuGroup m                    
       INNER JOIN @TempScheduleGroups tsg                    
        ON m.GroupCode = tsg.GroupCode                     
     where                    
       m.COAID = @COAID                    
                         
     union ALL                    
                         
     -- Default group for non - TA Schedules                    
     select 2         as Tag,                    
       1         as Parent,                    
                           
       null        as [ScheduleGroups!1],                    
       @HasScheduleTrackingFunctionAccess as [ScheduleGroups!1!HasScheduleTrackingFunctionAccess],                    
                           
       ScheduleCode      as [ScheduleGroup!2!GroupCode],                    
       null        as [ScheduleGroup!2!GroupName],                    
       DisplayOrder      as [ScheduleGroup!2!DisplayOrder],                    
       0         as [ScheduleGroup!2!Visible],                    
       null        as [ScheduleGroup!2!CanChangeDisplayOrder],                    
                           
       null        as [Row!3!RowID],                    
       null        as [Row!3!BaseScheduleCode],                    
       null        as [Row!3!ScheduleCode],                    
       null        as [Row!3!ScheduleName],                    
       null        as [Row!3!DisplayOrder],                    
       null         as [Row!3!IsMandatory],                    
       null         as [Row!3!InUse],                    
       null         as [Row!3!IsTracked],                    
       null        as [Row!3!Status],                    
       null        as [Row!3!ScheduleGroup],                    
       null        as [Row!3!CanChangeGroup],                    
       null        as [Row!3!ReadOnly],                    
                           
       null        as [ApplicableGroups!10],                    
       null        as [ApplicableGroup!11!GroupCode],                    
       null        as [ApplicableGroup!11!GroupName],                    
       null        as [ApplicableGroup!11!DisplayOrder]                    
                           
     from                    
       #TempSchedules                    
                           
     where               
       IsTA = 0                    
       AND ScheduleGroup IS NULL                    
                           
     union ALL                    
                         
     -- Schedules - non TA                      
     select 3         as Tag,                    
       2         as Parent,                    
                           
       null        as [ScheduleGroups!1],                      
       @HasScheduleTrackingFunctionAccess as [ScheduleGroups!1!HasScheduleTrackingFunctionAccess],                    
                           
       coalesce(ts.ScheduleGroup,                     
        ts.ScheduleCode)    as [ScheduleGroup!2!GroupCode],                       
       coalesce(tsg.GroupName, null)  as [ScheduleGroup!2!GroupName],                       
       coalesce(tsg.DisplayOrder, ts.DisplayOrder)                 
                as [ScheduleGroup!2!DisplayOrder],                       
       null        as [ScheduleGroup!2!Visible],                       
       null        as [ScheduleGroup!2!CanChangeDisplayOrder],                       
                           
       ts.ScheduleCode      as [Row!3!RowID],                    
       ts.BaseScheduleCode     as [Row!3!BaseScheduleCode],                    
       ts.ScheduleCode      as [Row!3!ScheduleCode],                    
       ScheduleName      as [Row!3!ScheduleName],                    
       ts.DisplayOrder      as [Row!3!DisplayOrder],                    
       ts.IsMandatory       as [Row!3!IsMandatory],                    
       ts.InUse        as [Row!3!InUse],                    
       ts.IsTracked      as [Row!3!IsTracked],                    
       ts.Status       as [Row!3!Status],                    
       coalesce(ts.ScheduleGroup, null) as [Row!3!ScheduleGroup],                    
       ~IsTA        as [Row!3!CanChangeGroup],                    
       0         as [Row!3!ReadOnly],                    
                           
       null        as [ApplicableGroups!10],                    
       null        as [ApplicableGroup!11!GroupCode],                    
       null        as [ApplicableGroup!11!GroupName],                    
       null        as [ApplicableGroup!11!DisplayOrder]                       
                       
     from                     
      #TempSchedules ts                    
      LEFT OUTER JOIN @TempScheduleGroups tsg                     
       ON ts.ScheduleGroup = tsg.GroupCode                       
     where                     
      IsTA = 0                    
                         
     union ALL                    
                         
     -- Default standard menu from tMenu table for TA schedules                     
     select 2         as Tag,                    
       1         as Parent,                    
                           
       null        as [ScheduleGroups!1],                    
       @HasScheduleTrackingFunctionAccess as [ScheduleGroups!1!HasScheduleTrackingFunctionAccess],                    
                           
       GroupCode       as [ScheduleGroup!2!GroupCode],                      
       GroupName       as [ScheduleGroup!2!GroupName],            DisplayOrder      as [ScheduleGroup!2!DisplayOrder],                       
       Visible        as [ScheduleGroup!2!Visible],                       
       null        as [ScheduleGroup!2!CanChangeDisplayOrder],                    
                           
       null        as [Row!3!RowID],                    
       null        as [Row!3!BaseScheduleCode],                    
       null        as [Row!3!ScheduleCode],                    
       null        as [Row!3!ScheduleName],                    
       null        as [Row!3!DisplayOrder],                    
       null         as [Row!3!IsMandatory],                    
       null         as [Row!3!InUse],                    
       null         as [Row!3!IsTracked],                    
       null        as [Row!3!Status],                    
       null        as [Row!3!ScheduleGroup],                    
       null        as [Row!3!CanChangeGroup],           
       0         as [Row!3!ReadOnly],                    
                           
       null        as [ApplicableGroups!10],                    
       null        as [ApplicableGroup!11!GroupCode],                    
       null        as [ApplicableGroup!11!GroupName],                    
       null        as [ApplicableGroup!11!DisplayOrder]                    
                           
     from                                      
       @TempScheduleGroups                    
     where                    
       GroupCode IN (@CONST_SOURCE_GAPP_MENU_CODE, @CONST_DERIVED_GAPP_MENU_CODE)                    
                         
     union ALL                    
                         
     -- Schedules - TA                      
     select 3         as Tag,                    
       2         as Parent,                    
                           
       null        as [ScheduleGroups!1],                      
       @HasScheduleTrackingFunctionAccess as [ScheduleGroups!1!HasScheduleTrackingFunctionAccess],                    
                           
       GroupCode       as [ScheduleGroup!2!GroupCode],                       
       GroupName       as [ScheduleGroup!2!GroupName],                       
       g.DisplayOrder      as [ScheduleGroup!2!DisplayOrder],                       
       null        as [ScheduleGroup!2!Visible],                       
       null        as [ScheduleGroup!2!CanChangeDisplayOrder],                       
                           
       CASE                         
        WHEN GroupCode = @CONST_DERIVED_GAPP_MENU_CODE THEN ScheduleCode + '_S'                    
        ELSE ScheduleCode                    
       END         as [Row!3!RowID],                    
                s.BaseScheduleCode,                     
       CASE                         
        WHEN GroupCode = @CONST_DERIVED_GAPP_MENU_CODE THEN ScheduleCode + '_S'                    
        ELSE ScheduleCode     
       END         as [Row!3!ScheduleCode],                    
       ScheduleName      as [Row!3!ScheduleName],                    
       s.DisplayOrder      as [Row!3!DisplayOrder],                    
       IsMandatory        as [Row!3!IsMandatory],                    
       InUse         as [Row!3!InUse],                    
       IsTracked        as [Row!3!IsTracked],                    
       Status        as [Row!3!Status],                    
       GroupCode       as [Row!3!ScheduleGroup],                    
       ~IsTA        as [Row!3!CanChangeGroup],                    
       CASE                     
        WHEN GroupCode = @CONST_SOURCE_GAPP_MENU_CODE THEN 0                    
        WHEN GroupCode = @CONST_DERIVED_GAPP_MENU_CODE THEN 1                    
       END         as [Row!3!ReadOnly] ,                    
                           
       null        as [ApplicableGroups!10],                    
       null        as [ApplicableGroup!11!GroupCode],                    
       null        as [ApplicableGroup!11!GroupName],                    
       null        as [ApplicableGroup!11!DisplayOrder]                    
                           
     from                     
      #TempSchedules s                    
      INNER JOIN @TempScheduleGroups g                    
       ON GroupCode IN (@CONST_SOURCE_GAPP_MENU_CODE, @CONST_DERIVED_GAPP_MENU_CODE)                      
     where                     
      IsTA = 1                    
                                   
     -- Applicable groups                    
     UNION ALL                    
                         
     select 10         as Tag,                    
       1         as Parent,                    
                           
       null        as [ScheduleGroups!1],                    
       @HasScheduleTrackingFunctionAccess as [ScheduleGroups!1!HasScheduleTrackingFunctionAccess],                    
                           
       null        as [ScheduleGroup!2!GroupCode],                    
       null        as [ScheduleGroup!2!GroupName],                    
       null        as [ScheduleGroup!2!DisplayOrder],                    
       null        as [ScheduleGroup!2!Visible],                    
       null        as [ScheduleGroup!2!CanChangeDisplayOrder],                    
                           
       null        as [Row!3!RowID],                    
       null        as [Row!3!BaseScheduleCode],                    
       null        as [Row!3!ScheduleCode],                    
       null        as [Row!3!ScheduleName],                    
       null        as [Row!3!DisplayOrder],                    
       null         as [Row!3!IsMandatory],                    
       null         as [Row!3!InUse],                    
       null         as [Row!3!IsTracked],                    
       null        as [Row!3!Status],                    
       null        as [Row!3!ScheduleGroup],                    
       null        as [Row!3!CanChangeGroup],                    
       null        as [Row!3!ReadOnly],                       
                           
       null        as [ApplicableGroups!10],                    
       null        as [ApplicableGroup!11!GroupCode],                    
       null        as [ApplicableGroup!11!GroupName],                    
       0         as [ApplicableGroup!11!DisplayOrder]                    
                         
                         
     UNION ALL                    
                         
     select 11         as Tag,                    
       10         as Parent,                    
                           
       null        as [ScheduleGroups!1],                    
       @HasScheduleTrackingFunctionAccess as [ScheduleGroups!1!HasScheduleTrackingFunctionAccess],                    
                           
       null        as [ScheduleGroup!2!GroupCode],                    
       null        as [ScheduleGroup!2!GroupName],                    
       null        as [ScheduleGroup!2!DisplayOrder],                    
       null        as [ScheduleGroup!2!Visible],                    
       null as [ScheduleGroup!2!CanChangeDisplayOrder],                    
                           
       null        as [Row!3!RowID],                    
       null        as [Row!3!BaseScheduleCode],                    
       null        as [Row!3!ScheduleCode],                    
       null        as [Row!3!ScheduleName],                    
       null        as [Row!3!DisplayOrder],                    
       null         as [Row!3!IsMandatory],                    
       null         as [Row!3!InUse],                    
       null         as [Row!3!IsTracked],                    
       null        as [Row!3!Status],                    
       null        as [Row!3!ScheduleGroup],                    
       null        as [Row!3!CanChangeGroup],                    
       null        as [Row!3!ReadOnly],                       
                           
       null        as [ApplicableGroups!10],                    
       GroupCode       as [ApplicableGroup!11!GroupCode],                    
       GroupName       as [ApplicableGroup!11!GroupName],                    
       DisplayOrder      as [ApplicableGroup!11!DisplayOrder]              
       FROM                     
      @TempScheduleGroups                    
         
                         
                         
     ORDER BY [ApplicableGroup!11!DisplayOrder], [ApplicableGroup!11!GroupName],                     
        [ScheduleGroup!2!DisplayOrder], [ScheduleGroup!2!GroupCode], [ScheduleGroup!2!GroupName],                     
        [Row!3!ScheduleGroup], [Row!3!DisplayOrder], [Row!3!ScheduleName] 

原因分析 #

FOR XML EXPLICIT对结果集顺序有强制要求：子标签行必须紧跟对应父标签行，且父标签行必须先出现。报错的核心是当前排序规则存在漏洞，导致部分场景下子标签行排在父标签行之前，且由于NULL值排序逻辑、重复排序值的随机排序，错误表现为间歇性触发。

具体问题点：

1. ApplicableGroups（Tag=10）与ApplicableGroup（Tag=11）的排序冲突
   Tag=10的行（父标签）[ApplicableGroup!11!DisplayOrder]固定为0，而Tag=11的行（子标签）该字段可能为0。当ORDER BY以该字段开头时，两类行的排序顺序不稳定，可能出现Tag=11行排在Tag=10行之前的情况，触发父标签未打开的错误。

2. ScheduleGroup（Tag=2）与Row（Tag=3）的排序逻辑漏洞
   Tag=2的行[Row!3!ScheduleGroup]为NULL，部分Row行（Tag=3，父为2）的该字段也为NULL。NULL值的排序顺序依赖数据库设置，当这些行排序顺序随机时，可能出现Row行排在对应ScheduleGroup行之前的情况，触发报错。

3. 排序字段的唯一性不足
   当多个行的排序字段值完全相同时，数据库会采用物理存储顺序等不确定的规则排序，这种随机性导致错误仅在特定排序结果下触发，表现为间歇性。

修复方案 #

调整ORDER BY子句，通过CASE语句强制父标签行的排序优先级高于子标签行，彻底避免顺序颠倒的情况：

ORDER BY 
    -- 确保根标签（Tag=1）始终排在最前
    CASE WHEN Tag = 1 THEN 0 ELSE 1 END,
    -- 先排ApplicableGroups父标签，再排其子标签
    CASE WHEN Tag = 10 THEN 0 WHEN Tag = 11 THEN 1 ELSE 2 END,
    [ApplicableGroup!11!DisplayOrder], [ApplicableGroup!11!GroupName],
    -- 先排ScheduleGroup父标签，再排其子标签
    CASE WHEN Tag = 2 THEN 0 WHEN Tag = 3 THEN 1 ELSE 2 END,
    [ScheduleGroup!2!DisplayOrder], [ScheduleGroup!2!Group