Oracle 19c：用PL/SQL更新BLOB列XML的ns35:AMOUNTLOCAL1值

需求：在Oracle Database 19c环境下，使用PL/SQL修改存储在BLOB列中的XML数据，将指定路径下的<ns35:AMOUNTLOCAL1>元素值从122000改为500。目标XML结构如下：

<ns0:WSMBBILLPAYMENTSERVICETTResponse xmlns:ns0="http://sample.com/ESBMBWEBSERVICE" xmlns:ns21="http://sample.com/FUNDSTRANSFERSAMESBMBTM" xmlns:ns3="http://sample.com/SAMHBILLPAY" xmlns:ns35="http://sample.com/TELLER" xmlns:ns28="http://sample.com/FUNDSTRANSFERSAMMBONLINEPAY" xmlns:ns19="http://sample.com/FUNDSTRANSFER" xmlns:ns5="http://sample.com/SAMHBILLPAYPAYBYMOBILE" xmlns:ns34="http://sample.com/FUNDSTRANSFERFTSAMESBMOBILESUBFEE" xmlns:ns22="http://sample.com/FUNDSTRANSFERSAMESBMBTTEP" xmlns:ns6="http://sample.com/CUSTOMER" xmlns:ns13="http://sample.com/ENQSAMMOBACCTWS" xmlns:ns26="http://sample.com/FUNDSTRANSFERSAMESBMOBILETTOA" xmlns:ns1="http://sample.com/ACCOUNT" xmlns:ns9="http://sample.com/ENQSAMESBACCTINQ" xmlns:ns32="http://sample.com/FUNDSTRANSFERFTSAMESBMOBILEPITV">
  <Status>
    <transactionId>TT230397M1GC</transactionId>
    <messageId>MOB230391644542177.00</messageId>
    <successIndicator>Success</successIndicator>
    <application>TELLER</application>
  </Status>
  <TELLERType id="DD230397M1GC">
    <ns35:TRANSACTIONCODE>983</ns35:TRANSACTIONCODE>
    <ns35:TELLERID1>2801</ns35:TELLERID1>
    <ns35:DRCRMARKER>CREDIT</ns35:DRCRMARKER>
    <ns35:CURRENCY1>KHR</ns35:CURRENCY1>
    <ns35:CUSTOMER1>166708</ns35:CUSTOMER1>
    <ns35:gACCOUNT1>
      <ns35:mACCOUNT1>
        <ns35:ACCOUNT1>1012448283</ns35:ACCOUNT1>
        <ns35:AMOUNTLOCAL1>122000</ns35:AMOUNTLOCAL1>
        <ns35:sgNARRATIVE1>
          <ns35:NARRATIVE1>
            <ns35:NARRATIVE1>3203738210-SAM000007828</ns35:NARRATIVE1>
          </ns35:NARRATIVE1>
        </ns35:sgNARRATIVE1>
      </ns35:mACCOUNT1>
    </ns35:gACCOUNT1>
    <ns35:VALUEDATE1>20230208</ns35:VALUEDATE1>
    <ns35:EXPOSUREDATE1>20230208</ns35:EXPOSUREDATE1>
    <ns35:CURRMARKET1>10</ns35:CURRMARKET1>
    <ns35:POSTYPE1>TR</ns35:POSTYPE1>
    <ns35:CURRENCY2>KHR</ns35:CURRENCY2>
    <ns35:TELLERID2>2801</ns35:TELLERID2>
    <ns35:ACCOUNT2>KHR1000028010182</ns35:ACCOUNT2>
    <ns35:AMOUNTLOCAL2>122000</ns35:AMOUNTLOCAL2>
    <ns35:NETAMOUNT>122000</ns35:NETAMOUNT>
    <ns35:POSTYPE2>TR</ns35:POSTYPE2>
    <ns35:gNARRATIVE2>
      <ns35:NARRATIVE2>3203738210-SAM000007828</ns35:NARRATIVE2>
    </ns35:gNARRATIVE2>
    <ns35:WAIVECHARGES>NO</ns35:WAIVECHARGES>
    <ns35:gNEWCUSTBAL>
      <ns35:NEWCUSTBAL>10069600</ns35:NEWCUSTBAL>
    </ns35:gNEWCUSTBAL>
    <ns35:AUTHDATE>20230208</ns35:AUTHDATE>
    <ns35:gSTMTNO>
      <ns35:STMTNO>201281644542178.00</ns35:STMTNO>
      <ns35:STMTNO>1-2</ns35:STMTNO>
      <ns35:STMTNO>KH0010001</ns35:STMTNO>
      <ns35:STMTNO>201281644542178.01</ns35:STMTNO>
      <ns35:STMTNO>1-2</ns35:STMTNO>
    </ns35:gSTMTNO>
    <ns35:CURRNO>1</ns35:CURRNO>
    <ns35:gINPUTTER>
      <ns35:INPUTTER>16445_SAM.PHEAROM123_I_INAU_OFS_MOB</ns35:INPUTTER>
    </ns35:gINPUTTER>
    <ns35:gDATETIME>
      <ns35:DATETIME>2302081142</ns35:DATETIME>
      <ns35:DATETIME>2302081142</ns35:DATETIME>
    </ns35:gDATETIME>
    <ns35:AUTHORISER>16445_SAM.PHEAROM123_OFS_MOB</ns35:AUTHORISER>
    <ns35:SAMHBILLPAY>3203738210-សុហជា</ns35:SAMHBILLPAY>
    <ns35:LSAMSUPTYPE>EDC</ns35:LSAMSUPTYPE>
    <ns35:ADDITIONALDATA>អគ្គិសនីážáž¶áž€áŸ‚ážœ</ns35:ADDITIONALDATA>
    <ns35:ATUNIQUEID>SAM000007828</ns35:ATUNIQUEID>
  </TELLERType>
</ns0:WSMBBILLPAYMENTSERVICETTResponse>

解决方案 #

核心思路 #

1. 将BLOB列中的数据转换为XMLType，便于进行XML操作
2. 使用XQuery定位目标元素并修改值
3. 将修改后的XMLType转换回BLOB，更新原表数据

PL/SQL代码示例 #

假设存储XML的表名为XML_DATA_TABLE，BLOB列名为XML_BLOB_COL，用于定位行的主键列名为ID：

DECLARE
  v_xml XMLType;
  v_updated_xml XMLType;
  v_blob BLOB;
BEGIN
  -- 从BLOB列读取XML数据并转换为XMLType
  SELECT XMLType(XML_BLOB_COL, NLS_CHARSET_ID('AL32UTF8'))
  INTO v_xml
  FROM XML_DATA_TABLE
  WHERE ID = '目标行ID'; -- 替换为实际的主键值

  -- 使用XQuery修改指定元素的值，声明必要的命名空间
  SELECT XMLQuery(
    'declare namespace ns0="http://sample.com/ESBMBWEBSERVICE";
     declare namespace ns35="http://sample.com/TELLER";
     copy $new := $doc
     modify (
       for $amt in $new/ns0:WSMBBILLPAYMENTSERVICETTResponse/TELLERType/ns35:gACCOUNT1/ns35:mACCOUNT1/ns35:AMOUNTLOCAL1
       return replace value of node $amt with "500"
     )
     return $new'
    PASSING v_xml AS "doc"
    RETURNING CONTENT
  ) INTO v_updated_xml FROM dual;

  -- 将修改后的XMLType转换回BLOB
  v_updated_xml.getBlobVal(NLS_CHARSET_ID('AL32UTF8')) INTO v_blob;

  -- 更新原表的BLOB列
  UPDATE XML_DATA_TABLE
  SET XML_BLOB_COL = v_blob
  WHERE ID = '目标行ID'; -- 替换为实际的主键值

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

关键说明 #

• 命名空间处理：必须在XQuery中声明所有用到的命名空间（这里是ns0和ns35），否则无法正确定位元素
• 字符集：转换时指定AL32UTF8确保XML字符编码正确，避免乱码
• 元素路径：XQuery中的路径严格对应XML层级，确保路径准确无误
• 事务控制：添加异常处理和事务回滚，避免数据损坏

内容的提问来源于stack exchange，提问作者Nothing