Update 3rd level deep field value in an XML using xQuery-CodePudding

I have the following sample XML and I am looking to Update: AttendeeID to 7878 (will be a sql variable) where sequence = 1 (will be a sql variable) Expected output is to see 7878 in the AttendeeID field's value. When I run any of the 2 options I tried, it does not yield the correct result. For Eg. Delete works but the element is not added. Replace value does not update the value. Any inputs are highly 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">-1</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">-1</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">-1</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>';
       -----------------------SQL -----------------------

   select @cartXML as originalXML
   DECLARE @ID as int   ,@productID  as int, @attendeeId as int = 7878,
   @sequenceId as int, @orderLineXML as XML , @ExtendedAttrDetail as XML 

   SET @sequenceId  = 2 

   select @orderlineXML  = c.query('.'), @ExtendedAttrDetail = w.query('.') from 
    @cartXML.nodes('/OBJECT/SUBTYPE/OBJECT[FIELD[@NAME="Sequence"]/text()=sql:variable("@sequenceId")]')  t1(c)
    Cross APPLY (VALUES(TRY_CAST(c.query('FIELD[@NAME="__ExtendedData"]').value('.','NVARCHAR(MAX)') AS XML)))AS t2(w)

    -----This works..But I am looking to alter @cartXML as it contains the entire XML
    SET @ExtendedAttrDetail.modify('replace value of 
    (/OBJECT/FIELD[@NAME="AttendeeID"]/text())[1] 
    with sql:variable("@attendeeId")')

    --select @ExtendedAttrDetail 

    ------- Option 1( Preferred)---does not work--

    SET @cartXML.modify ('replace value of 
    (/OBJECT/SUBTYPE/OBJECT[FIELD[@NAME="Sequence"]/text()=sql:variable("@sequenceId")]/FIELD[@NAME="__ExtendedData"]/OBJECT/FIELD[@NAME="AttendeeID"]/text())[1] with sql:variable("@attendeeId")')

    select @cartXML as ModifiedDirectly

   ---Option 2 (Insert does not add correctly )

   
   --SET @cartXML.modify('delete 
   --/OBJECT/SUBTYPE/OBJECT[FIELD[@NAME="Sequence"]/text()=sql:variable("@sequenceId")] 
   --/FIELD[@NAME="__ExtendedData"]');
   --SET @cartXML.modify('insert sql:variable("@ExtendedAttrDetail") into 
   --(/OBJECT/SUBTYPE/OBJECT[FIELD[@NAME="Sequence"]/text()=sql:variable("@sequenceId")]) 
   --[1]');

   --SELECT @cartXML as UpdatedXL;

CodePudding user response：

Please try the following solution.

The issue is that the XML fragment in question is encoded.

SQL

DECLARE @cartXML XML = 
N'<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">-1</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">-1</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">-1</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 @ExtendedData XML
    , @attendeeId INT = 770;;

-- Step #1: select XML fragment in question as real XML data type
SELECT @ExtendedData = w
FROM @cartxml.nodes('/OBJECT/SUBTYPE/OBJECT[@ID="-1"]') as t1(c)
   CROSS APPLY (VALUES(TRY_CAST(c.query('FIELD[@NAME="__ExtendedData"]').value('.','NVARCHAR(MAX)') AS XML))) AS t2(w)
WHERE w.exist('/OBJECT[@CLASS="Meet123"]') = 1;

-- Step #2: remove encoded XML fragment
SET @cartXML.modify('replace value of (/OBJECT/SUBTYPE[@NAME="SubType1"]/OBJECT/FIELD[@NAME="__ExtendedData"]/text())[1] 
   with ""');

-- Step #3: modify AttendeeID
SET @ExtendedData.modify('replace value of 
        (/OBJECT/FIELD[@NAME="AttendeeID"]/text())[1] 
        with sql:variable("@attendeeId")');

-- Step #4: insert real XML fragment
SET @cartXML.modify('insert sql:variable("@ExtendedData") into
        (/OBJECT/SUBTYPE[@NAME="SubType1"]/OBJECT/FIELD[@NAME="__ExtendedData"])[1]');

-- test     
SELECT @cartXML;