This discussion is archived
2 Replies Latest reply: Aug 9, 2012 12:14 PM by 937454 RSS

XML insertion/updation using merge and generate sequence number

937454 Newbie
Currently Being Moderated
Hi,

I am working on Oracle 11g.

I have a doubt with XML load.
I get an xml and I have to insert or update data into a table A. This I can achieve using MERGE statement.
But there is one column in Table A, that I need to populate with a sequence number, based on the data sent in the xml.
The xml does not send this column data.
And I have to make sure the sequence is created based on the order in which records are present in xml.
For example the MERGE is inserting five rows and then updating next two rows and again inserting 3 rows from xml into table A. The sequence number should be created in the same order for the column in table A.
Also for each new xml, the sequence starts with 1 and ends with the number of records in the xml. So I cannot create a sequence and use seq.nextval.

Please let me know, there is a way of achieveing this.

Thank you!

Edited by: 934451 on Aug 8, 2012 6:33 AM

Edited by: 934451 on Aug 8, 2012 6:50 AM
  • 1. Re: XML insertion/updation using merge and generate sequence number
    odie_63 Guru
    Currently Being Moderated
    Hi,

    Following up on your previous thread : {thread:id=2403469}

    You can use the FOR ORDINALITY clause in XMLTable to generate the required sequence :
    MERGE INTO target_table t
    USING (
      SELECT x.seq_num, x.pk_id, x.col1, x.col2, ...
      FROM XMLTable(
             '/root/record'
             passing my_xml_doc
             columns seq_num FOR ORDINALITY
                   , 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.seq_num = src.seq_num
       , t.col1 = src.col1
       , t.col2 = src.col2
       , ...
    WHEN NOT MATCHED THEN INSERT
     (seq_num, pk_id, col1, col2, ...)
     VALUES (src.seq_num, src.pk_id, src.col1, src.col2, ...)
    ;
  • 2. Re: XML insertion/updation using merge and generate sequence number
    937454 Newbie
    Currently Being Moderated
    Thank you!!...It worked.

Legend

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