解析、更新、删除XML元素

Question

我有以下示例XML，我希望更新：

1. 收货地址1至：test777
2. TestID到：1234，其中序列=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"/>

'(；

---

我尝试了以下SQL，它说命令执行成功，但数据没有得到更新：

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

查询哪里出了问题？

谢谢。

Answer 1

请尝试以下解决方案。

注意事项：

• XML区分大小写
• 最好使用T-SQL变量，而不是硬编码的值

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;