決議、更新、洗掉XML元素

我有以下示例 XML，我正在尋找更新：

1. ShipToAddress1 到：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

查詢哪里出錯了？

謝謝。

uj5u.com熱心網友回復：

請嘗試以下解決方案。

值得注意的點：

• 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;

標籤：sql服务器xmltsql查询xml.修改

上一篇：為什么此XML驗證在擴展型別的NameIDFormat上失敗？

下一篇：SearchView圖示和查詢文本在黑色背景下不可見