使用xQuery更新XML中的第3級深度欄位值-有解無憂

我有以下示例 XML，我希望更新： AttendeeID 為 7878（將是一個 sql 變數）其中序列 = 1（將是一個 sql 變數）預期的輸出是在 AttendeeID 欄位的值中看到 7878。當我運行我嘗試過的 2 個選項中的任何一個時，它都不會產生正確的結果。例如。洗掉有效，但未添加元素。替換值不會更新值。任何輸入都受到高度贊賞。謝謝你。

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

uj5u.com熱心網友回復：

請嘗試以下解決方案。

問題是有問題的 XML 片段是編碼的。

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;

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/512764.html

標籤：sqlsql服务器xml查询

上一篇：如何在Xquery(oracle,pl/sql)中放置命名空間參考

下一篇：XML檔案到pandasDataFrame