1 Reply Latest reply: Jun 18, 2012 10:24 AM by odie_63 RSS

    XML contains some records that need to be inserted and some updated

    937454
      Hi,

      I get an XML that contains records which I need to insert into a table ABC.

      The challenge is some of the records within the xml are already present in the table ABC ( thats why I am getting unique key constraint error). So I have to update those records instead of insert.

      How can I differentiate those records from new records and create a separate xml and update into table ABC??
        • 1. Re: XML contains some records that need to be inserted and some updated
          odie_63
          Hi,

          Use a MERGE statement whose source is the result of an XMLTable.

          Something like this, in pseudo-code :
          MERGE INTO target_table t
          USING (
            SELECT x.pk_id, x.col1, x.col2, ...
            FROM XMLTable(
                   '/root/record'
                   passing my_xml_doc
                   columns pk_id number       path 'ID'
                         , col1  varchar2(30) path 'COL1'
                         , col2  varchar2(30) path 'COL2'
                         , ...
                 ) x
          ) src
          ON ( t.pk_id = src.pk_id )
          WHEN MATCHED THEN UPDATE
           SET t.col1 = src.col1
             , t.col2 = src.col2
             , ...
          WHEN NOT MATCHED THEN INSERT
           (pk_id, col1, col2, ...)
           VALUES ( src.pk_id, src.col1, src.col2, ...)
          ;