MERGE INTO unit_labels@WED_REMODE_DB old
            USING(
                select y.unit unit,y.cnd_id cnd_id,y.rate_number rate_number,y.non non
              from XmlTable(
                    '/MESSAGES/MESSAGE'
                    passing xmltype(' <MESSAGES>
<MESSAGE ID="3026900">
  <MSG_ID>3026900</MSG_ID>
  <DT_POSTED>6/20/2013 08:15:48</DT_POSTED>
  <POSTED_BY>GPD_MSG_EXTRACTOR</POSTED_BY>
  <DT_LAST_QUEUED />
<MSG>
<WORK_SET TRANSACTION_ID="@TRANS_ID" TRANSACTION_TYPE="Batch" IS_ACID="@IS_ACID">
<WORK_UNIT GROUP="rate_number" ACTION="UPDATE" AFFECTED="rate_number">
<RECORDSET TABLE_NAME="rate_number">
<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
<rs:data>
  <z:row cnd_id="3838" non="AT" unit="A0814818" dw_maint_flag="False" stock_status_id="0" rate_number="30" est_qty="0" candid_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_unit_PROCESS" candid_dts="2010-05-22T02:21:21.707" candid_uid="rate_number_Rules" />
  <z:row cnd_id="202" non="GB" unit="A0814819" dw_maint_flag="False" stock_status_id="0" rate_number="30" est_qty="0" candid_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_unit_PROCESS" candid_dts="2010-05-22T02:21:21.707" candid_uid="rate_number_Rules" />
  <z:row cnd_id="2828" non="BE" unit="A0814820" dw_maint_flag="False" stock_status_id="0" rate_number="30" est_qty="0" candid_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_unit_PROCESS" candid_dts="2010-05-22T02:21:21.707" candid_uid="rate_number_Rules" />
</rs:data>
  </xml>
  </RECORDSET>
  </WORK_UNIT>
  </WORK_SET>
  </MSG>
  </MESSAGE>
  </MESSAGES>')
  columns
      MESSAGE_NO  for ordinality
    , MESSAGE_ID  number  path '@ID'
    , data        xmltype path 'MSG/WORK_SET/WORK_UNIT/RECORDSET'
  ) x
, xmltable(
    xmlnamespaces('urn:schemas-microsoft-com:rowset' as "rs", '#RowsetSchema' as "z"),
    '/RECORDSET/xml/rs:data/z:row'
    passing x.data
     columns cnd_id number path './@cnd_id'
                          ,non       varchar2(2)  path './@non'
                         ,unit       varchar2(10) path './@unit'
                         ,rate_number number       path './@rate_number'
                ) y
            ) new
              on (old.unit = new.unit
          AND old.cnd_id = new.cnd_id)
            WHEN MATCHED THEN UPDATE
               SET old.rate_number = new.rate_number,
            WHEN NOT MATCHED THEN INSERT
                 (  old.unit
                   , old.cnd_id
                   , old.rate_number
                   , old.CRD_BY
                   , old.CRD_DATE       
                 ) values
                 ( new.unit
                 , new.cnd_id
                 , new.rate_number
                 , 'SYSTEM'
                 , SYSDATE
                 );
 
DDL of target table.
 
CREATE TABLE unit_labels
(   
  unit             VARCHAR2(25 CHAR)      NOT NULL,
  cnd_id           VARCHAR2(4 CHAR)       NOT NULL,
  rate_number      NUMBER,
  crd_by           VARCHAR2(100 CHAR)     NOT NULL,
  crd_date         DATE                   NOT NULL
) ;
 
ALTER TABLE unit_labels ADD (CONSTRAINT unit_labels_PK PRIMARY KEY(unit, cnd_id));