Parse, Update, Delete XML elements

Question

I have the following sample XML and I am looking to Update:

1. ShipToAddress1 to: test777
2. TestID to: 1234 where Sequence =1

---

<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"/>

');

---

I have tried the following SQL it says command executed successfully but the data does not get updated:

DECLARE @myDoc XML;  

set @myDoc = The above XML 

SET @myDoc.modify('replace value of (/Object/Field[@NAME=("ShipToAddress1")]/text())[1] with 
"test777"')
 Select @myDoc

Where is the query going wrong?

Thanks.

Answer 1

Please try the following solution.

Notable points:

• XML is case sensitive.
• It is better to use T-SQL variables instead of hard-coded values.

SQL

DECLARE @myDoc 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 @ShipToAddress1 VARCHAR(30) = '770 Crown Heights'
    , @TestID INT = 770;

SET @myDoc.modify('replace value of (/OBJECT/FIELD[@NAME="ShipToAddress1"]/text())[1] 
    with sql:variable("@ShipToAddress1")');

SET @myDoc.modify('replace value of (/OBJECT/SUBTYPE/OBJECT[FIELD[@NAME="Sequence"]/text()="1"]/FIELD[@NAME="TestID"]/text())[1] 
    with sql:variable("@TestID")');

SELECT @myDoc;