sql - 谁能帮助如何使用 SQL 在 XML (CLOB) 中添加数组资源？

Question

<?xml version="1.0" encoding="UTF-8"?>
<Values version="2.0">
  <record name="header" javaclass="com.wm.util.Values">
    <value name="legalEntity">27</value>
    <value name="globalId">a8a49f5d-70bb-421e-99f2-bb4065a46538</value>
    <value name="priority">5</value>
    <value name="requestor">COP</value>
  </record>
  <record name="body" javaclass="com.wm.util.Values">
    <array name="orderItem" type="record" depth="1">
      <record javaclass="com.wm.util.Values">
        <value name="orderItemId">OS-1229460</value>
        <array name="childOrderItem" type="record" depth="1">
          <record javaclass="com.wm.util.Values">
            <value name="orderItemId">280973171</value>
            <array name="relatedOrderItem" type="record" depth="1">
              <record javaclass="com.wm.util.Values">
                <value name="orderType">CUSTOMER_ORDER</value>
                <value name="orderItemId">280973171</value>
                <value name="orderItemDataOwner">Clarify</value>
              </record>
            </array>
            <value name="operationType">CANCEL</value>
            <value name="operationSubType">DEFAULT</value>
            <value name="status"> </value>
            <value name="subStatus"> </value>
            <value name="orderItemDateTime">2020-12-08T17:00:52</value>
            <value name="articleNumber">STANDAARDSIMKAART</value>
           
            <array name="childOrderItem" type="record" depth="1">
              <record javaclass="com.wm.util.Values">
                <value name="orderItemId">280973179</value>
                <value name="operationType">CANCEL</value>
                <value name="operationSubType">DEFAULT</value>
                <value name="status"> </value>
                <value name="subStatus"> </value>
                <record name="product" javaclass="com.wm.util.Values">
                  <value name="productInstanceId">280973179</value>
                  <value name="productSpecificationId">SE555</value>
                  <value name="productSpecificationName">PRIVATE_APN</value>
                  <value name="productDescription">PRIVATE_APN</value>
                  <value name="productType">PRIVATE_APN</value>
                  <value name="manualOverride">false</value>
                </record>
              </record>
            </array>
          </record>
        </array>
      </record>
    </array>
  </record>
</Values>

更准确地说，我必须在手动覆盖行之后添加这个数组资源

       <array name="productInstanceCharacterstic" type="record" depth="1">
                   <record javaclass="com.wm.util.Values">
                     <value name="name">APNProductInstanceID</value>
                     <value name="value">281606343</value>
                   </record>
                   <record javaclass="com.wm.util.Values">
                     <value name="name">UseStaticIP</value>
                     <value name="value">No</value>
                   </record>
                 </array>

我的代码：

UPDATE SD2_95.ORDER_DATA sgo SET sgo.ORDER_CONTENT =
(XMLSERIALIZE
(
  Document
  (
    XMLQuery
    (
      'copy $tmp := . modify  
      (for $i in $tmp//record[@name="body"]/value[@name=]/text()
      
      return $tmp'
      PASSING XMLTYPE(sgo.ORDER_CONTENT) RETURNING CONTENT
    )
    
  ) AS CLOB INDENT SIZE = 2
)
)
WHERE sgo.ORDER_ID IN
(
'OS-1151955',

)

我不确定如何实现 for 循环...请帮助

标签： sqlxmloracleclob