验证从XML提取数据插入SQL时生成行序列号的脚本正确性

如何为从XML字符串中提取的一组行附加序列号？以下是基于用户@T N提供的方案修改后的SQL脚本，目前运行结果看似正确，但需确认该脚本是否存在问题。（更新于2024/06/25）

脚本代码 #

CREATE TABLE [dbo].[UseSequence](
    [UId] [bigint] NOT NULL,
    [Version] [int] NOT NULL,
    [Speed] [int] NOT NULL,
    [TrainType] [int] NOT NULL,
    [HeadEnd] [int] NOT NULL,
    [Restricted] [int] NOT NULL,
    [Sequence] [int] NOT NULL
);

DECLARE 
    @BulletindataText nvarchar(max)
    ,@BulletinDataXml XML
    ,@BulletinSpeedsXml xml
    ,@DigitalUID    bigint
    ,@Version int;
;

Set @BulletindataText = '<BulletinData>
    <Bul>       
        <BulletinSpeeds>
            <BulletinSpeedRestriction>
                <Speed >30</Speed>
                <TrainType Numeric="2">Passenger</TrainType>
                <HeadEndOnlySpeedRestriction Numeric="2">No</HeadEndOnlySpeedRestriction>
                <RestrictedSpeed Numeric="2">No</RestrictedSpeed>
            </BulletinSpeedRestriction>
            <BulletinSpeedRestriction>
                <Speed >30</Speed>
                <TrainType Numeric="1">Freight</TrainType>
                <HeadEndOnlySpeedRestriction Numeric="2">No</HeadEndOnlySpeedRestriction>
                <RestrictedSpeed Numeric="2">No</RestrictedSpeed>
            </BulletinSpeedRestriction>
            <BulletinSpeedRestriction>
                <Speed >30</Speed>
                <TrainType Numeric="3">Intermodal</TrainType>
                <HeadEndOnlySpeedRestriction Numeric="2">No</HeadEndOnlySpeedRestriction>
                <RestrictedSpeed Numeric="2">No</RestrictedSpeed>
            </BulletinSpeedRestriction>
            <BulletinSpeedRestriction>
                <Speed >30</Speed>
                <TrainType Numeric="6">Commuter</TrainType>
                <HeadEndOnlySpeedRestriction Numeric="2">No</HeadEndOnlySpeedRestriction>
                <RestrictedSpeed Numeric="2">No</RestrictedSpeed>
            </BulletinSpeedRestriction>
            <BulletinSpeedRestriction>
                <Speed >30</Speed>
                <TrainType Numeric="5">TiltTrain</TrainType>
                <HeadEndOnlySpeedRestriction Numeric="2">No</HeadEndOnlySpeedRestriction>
                <RestrictedSpeed Numeric="2">No</RestrictedSpeed>
            </BulletinSpeedRestriction>
            <BulletinSpeedRestriction>
                <Speed >30</Speed>
                <TrainType Numeric="4">HighSpeedPassenger</TrainType>
                <HeadEndOnlySpeedRestriction Numeric="2">No</HeadEndOnlySpeedRestriction>
                <RestrictedSpeed Numeric="2">No</RestrictedSpeed>
            </BulletinSpeedRestriction>
        </BulletinSpeeds>
    </Bul>
</BulletinData>'
;

SET @BulletinDataXml = cast(@BulletinDataText as xml)
SET @BulletinSpeedsXml =  @BulletinDataXml.query('<BulletinSpeeds> {for $x in /BulletinData/Bul/BulletinSpeeds/child::* return $x} </BulletinSpeeds>'); 


-- Insert 1
Set @DigitalUID = 104;
Set @Version = 1;

-- Insert 2
--Set @DigitalUID = 104;
--Set @Version = 2;

INSERT INTO [dbo].[BulletinSpeedRestrictionUseSequence]
(DigitalUID
,[Version]
,Speed
,TrainType
,HeadEndOnlySpeedRestriction
,RestrictedSpeed,
[Sequence]
)
SELECT  @DigitalUID,
        @Version,
        a.c.value('Speed[1]','int') as 'Speed',
        a.c.value('TrainType[1]/@Numeric','int') as 'TrainType',
        a.c.value('HeadEndOnlySpeedRestriction[1]/@Numeric','int') as 'HeadEndOnlySpeedRestriction',
        a.c.value('RestrictedSpeed[1]/@Numeric','int') as 'RestrictedSpeed',
        row_number() over (partition by @DigitalUID, @Version order by a.c) AS SequenceNum
FROM @BulletinSpeedsXml.nodes('/BulletinSpeeds/BulletinSpeedRestriction') a(c)
;

运行结果 #

脚本潜在问题分析 #

1. 表定义与插入目标表不匹配
   创建的表是[dbo].[UseSequence]，但插入操作的目标表是[dbo].[BulletinSpeedRestrictionUseSequence]，且两个表字段名不一致（比如UseSequence中的HeadEnd对应插入时的HeadEndOnlySpeedRestriction），若BulletinSpeedRestrictionUseSequence未预先正确创建，会直接导致插入失败。

2. 排序逻辑存在不确定性
   row_number()中使用order by a.c是按XML节点对象排序，SQL Server对XML节点的排序依赖内部存储顺序，虽当前结果与XML节点顺序一致，但无明确业务字段支撑，后续XML结构变化或存储顺序改变时，序列号顺序可能不符合预期。建议改为按明确业务字段排序，比如TrainType的Numeric属性，或XML节点的位置：

   -- 按TrainType的Numeric属性排序
   row_number() over (partition by @DigitalUID, @Version order by a.c.value('TrainType[1]/@Numeric','int')) AS SequenceNum
   -- 或按XML节点在原结构中的位置排序
   row_number() over (partition by @DigitalUID, @Version order by a.c.value('for $i in . return count(../../BulletinSpeedRestriction[. << $i]) + 1', 'int')) AS SequenceNum
   

3. 冗余的XML处理步骤
   脚本中通过XQuery将@BulletinDataXml重新构造为@BulletinSpeedsXml属于冗余操作，可直接从原始XML节点提取数据，减少不必要的性能开销：

   FROM @BulletinDataXml.nodes('/BulletinData/Bul/BulletinSpeeds/BulletinSpeedRestriction') a(c)
   

4. 变量与字段命名不一致
   声明的变量@DigitalUID对应插入字段DigitalUID，但创建的表UseSequence中是UId，命名不一致易造成混淆，建议保持命名统一。

内容的提问来源于stack exchange，提问作者Tech with Thiru