基于TestID删除XML中SubType111节点并更新后续Sequence值

现有如下XML数据，需根据变量@TestID删除NAME为SubType111且TestID匹配的OBJECT节点，之后将后续所有同类型节点的Sequence字段值减1。目前已实现节点删除功能，但无法正确访问并更新后续节点的Sequence值，需要实现思路及代码指引。

原始XML与TSQL代码 #

DECLARE @cartXML XML = 
'<OBJECT CLASS="Test1" ID="-1" FULL="FULL" VERSION="1">
   <FIELD NAME="OrderDate">20220619</FIELD>
   <FIELD NAME="OrderParty">Individual</FIELD>
   <FIELD NAME="ShipToID">34567</FIELD>
   <FIELD NAME="ShipToAddress1">123 Test Street</FIELD>
   <FIELD NAME="ShipToCity">TestCity</FIELD>
   <FIELD NAME="ShipToState">IL</FIELD>
   <FIELD NAME="ShipTocountry">USA</FIELD>
   <FIELD NAME="TaxNumber">444</FIELD>
   <FIELD NAME="DiscountCode">Summer22</FIELD>
   <SUBTYPE NAME="SubType1">
      <OBJECT NAME="SubType111" ID="-1">
         <FIELD NAME="TestID">123</FIELD>
         <FIELD NAME="Sequence">1</FIELD>
         <FIELD NAME="ParentSequence">-1</FIELD>
         <FIELD NAME="ExtID">-1</FIELD>
         <FIELD NAME="ExtName">ABC</FIELD>
      </OBJECT>
      <OBJECT NAME="SubType111" ID="-1">
         <FIELD NAME="TestID">223</FIELD>
         <FIELD NAME="Sequence">2</FIELD>
         <FIELD NAME="ParentSequence">1</FIELD>
         <FIELD NAME="ExtID">-1</FIELD>
         <FIELD NAME="ExtName">DEF</FIELD>
         <FIELD NAME="__ExtendedData">&lt;OBJECT 
            CLASS="Meet123" ID="-1" FULL="FULL" 
            VERSION="1"&gt;&lt;FIELD 
            NAME="OrderDetailID"&gt;-1&lt;/FIELD&gt;&lt;FIELD 
            NAME="OrderID"&gt;-1&lt;/FIELD&gt;&lt;FIELD 
            NAME="Sequence"&gt;0&lt;/FIELD&gt;&lt;FIELD 
            NAME="AttendeeID"&gt;123&lt;/FIELD&gt;&lt;FIELD 
            NAME="AttendeeID_Name"&gt;Test, Mark/I 
            H 6&lt;/FIELD&gt;&lt;FIELD 
            NAME="ShowList"&gt;1&lt;/FIELD&gt;&lt;FIELD 
            NAME="BdgeName"&gt;Mark&lt;/FIELD&gt;&lt;FIELD 
            NAME="BadgeCompanyName"&gt;I H 6&lt;/FIELD&gt;
         &lt;/OBJECT&gt;</FIELD>
      </OBJECT>
      <OBJECT NAME="SubType111" ID="-1">
         <FIELD NAME="TestID">333</FIELD>
         <FIELD NAME="Sequence">3</FIELD>
         <FIELD NAME="ParentSequence">1</FIELD>
         <FIELD NAME="ExtID">-1</FIELD>
         <FIELD NAME="ExtName">GHI</FIELD>
      </OBJECT>
   </SUBTYPE>
   <SUBTYPE NAME="SubType2"/>
   <SUBTYPE NAME="SubType3"/>
</OBJECT>'

DECLARE @TestID as int,@sequenceId as int, @orderLineCount as int,@orderLineXML as XML 
set @TestID = 223

-------------已实现的删除功能----------------------------------------------------
--Select @cartXML as OriginalXML
--SET @cartXML.modify('delete 
--/OBJECT/SUBTYPE/OBJECT[FIELD[@NAME="TestID"]/text()=sql:variable("@TestID")]')
--Select @cartXML as XMLAfterDeletion
---------------------------------------------------

------更新后续节点Sequence值（此部分无法正确工作）----------------------
select @sequenceId = c.value('(FIELD[@NAME="Sequence"]/text())[1]', 'INT'), 
@orderLineCount = 
@cartXML.value('count(/OBJECT/SUBTYPE[@NAME="SubType1"]
/OBJECT[@NAME="SubType111"])','int') 
from     
@cartXML.nodes('/OBJECT/SUBTYPE/OBJECT[FIELD[@NAME="TestID"]
/text()=sql:variable("@TestID")]')  t1(c)
select @sequenceId  as SequenceID, @orderLineCount as TotalOrderLines 

-----------无法正确获取待修改的XML
if @orderLineCount > @sequenceId
select @cartXML.query('.') from  
@cartXML.nodes('/OBJECT/SUBTYPE/OBJECT[FIELD[@NAME="Sequence"]/text()
>sql:variable("@sequenceId")]')  t1(c)
where  @cartXML.exist('/OBJECT/SUBTYPE/OBJECT[FIELD[@NAME="Sequence"]/text()
>sql:variable("@sequenceId")]') =1
Select @cartxMl as XMLtoModify
---需要将TestID=223节点之后的所有SubType111节点的Sequence值减1

解决方案思路与完整代码 #

核心思路 #

1. 先获取待删除节点的Sequence值：必须在删除节点前完成，否则删除后无法定位该节点获取其Sequence。
2. 执行节点删除：使用XML的modify方法删除目标节点。
3. 批量更新后续节点的Sequence：由于TSQL的XML modify方法每次只能修改一个节点，需通过循环处理所有Sequence大于目标值的节点，将其值减1。

完整实现代码 #

DECLARE @cartXML XML = 
'<OBJECT CLASS="Test1" ID="-1" FULL="FULL" VERSION="1">
   <FIELD NAME="OrderDate">20220619</FIELD>
   <FIELD NAME="OrderParty">Individual</FIELD>
   <FIELD NAME="ShipToID">34567</FIELD>
   <FIELD NAME="ShipToAddress1">123 Test Street</FIELD>
   <FIELD NAME="ShipToCity">TestCity</FIELD>
   <FIELD NAME="ShipToState">IL</FIELD>
   <FIELD NAME="ShipTocountry">USA</FIELD>
   <FIELD NAME="TaxNumber">444</FIELD>
   <FIELD NAME="DiscountCode">Summer22</FIELD>
   <SUBTYPE NAME="SubType1">
      <OBJECT NAME="SubType111" ID="-1">
         <FIELD NAME="TestID">123</FIELD>
         <FIELD NAME="Sequence">1</FIELD>
         <FIELD NAME="ParentSequence">-1</FIELD>
         <FIELD NAME="ExtID">-1</FIELD>
         <FIELD NAME="ExtName">ABC</FIELD>
      </OBJECT>
      <OBJECT NAME="SubType111" ID="-1">
         <FIELD NAME="TestID">223</FIELD>
         <FIELD NAME="Sequence">2</FIELD>
         <FIELD NAME="ParentSequence">1</FIELD>
         <FIELD NAME="ExtID">-1</FIELD>
         <FIELD NAME="ExtName">DEF</FIELD>
         <FIELD NAME="__ExtendedData">&lt;OBJECT 
            CLASS="Meet123" ID="-1" FULL="FULL" 
            VERSION="1"&gt;&lt;FIELD 
            NAME="OrderDetailID"&gt;-1&lt;/FIELD&gt;&lt;FIELD 
            NAME="OrderID"&gt;-1&lt;/FIELD&gt;&lt;FIELD 
            NAME="Sequence"&gt;0&lt;/FIELD&gt;&lt;FIELD 
            NAME="AttendeeID"&gt;123&lt;/FIELD&gt;&lt;FIELD 
            NAME="AttendeeID_Name"&gt;Test, Mark/I 
            H 6&lt;/FIELD&gt;&lt;FIELD 
            NAME="ShowList"&gt;1&lt;/FIELD&gt;&lt;FIELD 
            NAME="BdgeName"&gt;Mark&lt;/FIELD&gt;&lt;FIELD 
            NAME="BadgeCompanyName"&gt;I H 6&lt;/FIELD&gt;
         &lt;/OBJECT&gt;</FIELD>
      </OBJECT>
      <OBJECT NAME="SubType111" ID="-1">
         <FIELD NAME="TestID">333</FIELD>
         <FIELD NAME="Sequence">3</FIELD>
         <FIELD NAME="ParentSequence">1</FIELD>
         <FIELD NAME="ExtID">-1</FIELD>
         <FIELD NAME="ExtName">GHI</FIELD>
      </OBJECT>
   </SUBTYPE>
   <SUBTYPE NAME="SubType2"/>
   <SUBTYPE NAME="SubType3"/>
</OBJECT>'

DECLARE @TestID int = 223
DECLARE @targetSequence int

-- 1. 获取待删除节点的Sequence值（删除前执行）
SELECT @targetSequence = c.value('(FIELD[@NAME="Sequence"])[1]', 'INT')
FROM @cartXML.nodes('/OBJECT/SUBTYPE[@NAME="SubType1"]/OBJECT[@NAME="SubType111" and FIELD[@NAME="TestID"]=sql:variable("@TestID")]') t1(c)

-- 2. 删除目标节点
SET @cartXML.modify('delete /OBJECT/SUBTYPE[@NAME="SubType1"]/OBJECT[@NAME="SubType111" and FIELD[@NAME="TestID"]=sql:variable("@TestID")]')

-- 3. 循环更新后续节点的Sequence值（每次modify只能修改一个节点）
WHILE @cartXML.exist('/OBJECT/SUBTYPE[@NAME="SubType1"]/OBJECT[@NAME="SubType111" and FIELD[@NAME="Sequence"]>sql:variable("@targetSequence")]') = 1
BEGIN
    SET @cartXML.modify('replace value of (/OBJECT/SUBTYPE[@NAME="SubType1"]/OBJECT[@NAME="SubType111" and FIELD[@NAME="Sequence"]>sql:variable("@targetSequence")]/FIELD[@NAME="Sequence"]/text())[1] 
                         with (sql:variable("@targetSequence"))')
    SET @targetSequence = @targetSequence + 1
END

-- 查看最终结果
SELECT @cartXML AS FinalXML

代码说明 #

• 获取目标Sequence：通过XPath精准定位到SubType1下的SubType111节点，匹配TestID后提取其Sequence值，避免删除节点后无法获取。
• 删除节点：使用modify('delete ...')删除目标节点，XPath限定了范围，防止误删其他节点。
• 循环更新Sequence：利用循环依次处理所有符合条件的节点，每次将节点的Sequence替换为当前目标值，随后目标值加1，直到没有符合条件的节点为止，实现后续节点Sequence值减1的效果。

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