0

I have the following XML. I need to delete an entire node with Object Name = SubType111 depending upon the variable TestID Then I need to update the sequence field of all the subsequent nodes, subtract by 1

I am able to get the First Part right, but in the second part - I am not able to access all the subsequent nodes. Expected output is to remove the desired node and subtract 1 from the sequence field of subsequent nodes. any inputs or pointers are greatly appreciated. Thank you.
------------------------XML ---------------------------

       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>'
---TSQL
   DECLARE @TestID as int,@sequenceId as int, @orderLineCount as int,@orderLineXML as XML 
   set @TestID = 223

   -------------This works ----------------------------------------------------
   --Select @cartXML as OriginalXML
   --SET @cartXML.modify('delete 
    --/OBJECT/SUBTYPE/OBJECT[FIELD[@NAME="TestID"]/text()=sql:variable("@TestID")]')
    --Select @cartXML as XMLAfterDeletion
    ---------------------------------------------------

    ------Update sequenceID of the subsequent nodes. Reduce by 1 ----------------------
    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 
    -----------This part does not yield correct XML to be modified
     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
     ---Subtract 1 from the sequence ID of all the SubType111 nodes that follow the node - 
     --TestID =223
vbgp
  • 73
  • 7

1 Answers1

0

Figured it out.

While @i < (@orderLineCount + 1)  
BEGIN 
SET @i = @i +1; 
SET @cartXML.modify('replace value of (/OBJECT/SUBTYPE/OBJECT[FIELD[@NAME="Sequence"]/text()=sql:variable("@i")]/FIELD[@NAME="Sequence"]/text())[1] 
    with sql:variable("@i") -1 ');  
END
vbgp
  • 73
  • 7