This discussion is archived
1 Reply Latest reply: Jun 18, 2012 8:24 AM by odie_63 RSS

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

937454 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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, ...)
    ;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points