Home » Developer & Programmer » JDeveloper, Java & XML » Using XML to insert into a table with self-reference (11.0.2.10)
Using XML to insert into a table with self-reference [message #650980] Mon, 09 May 2016 06:15 Go to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Hi All,

I am following on post: http://www.orafaq.com/forum/mv/msg/200480/649819/#msg_649819
The performance is much better with large data when I avoided the loop to insert new records as per my last trial in that post.

However, I have a case of self-reference table that I need to insert using XML as follows:
create table test_self_ref
(
 record_id number ,
 fk_parent_id number,
 record_value number,
  primary key (record_id),
 CONSTRAINT self_ref FOREIGN KEY (fk_parent_id) REFERENCES test_self_ref(record_id)
);


<dsOra>
  <test_self_ref>
    <record_id>1</record_id>
    <fk_parent_id></fk_parent_id>
    <record_value>10</record_value>
  </test_self_ref>
  <test_self_ref>
    <record_id>2</record_id>
    <fk_parent_id></fk_parent_id>
    <record_value>20</record_value>
  </test_self_ref>
  <test_self_ref>
    <record_id>3</record_id>
    <fk_parent_id></fk_parent_id>
    <record_value>30</record_value>
  </test_self_ref>
  <test_self_ref>
    <record_id>4</record_id>
    <fk_parent_id>2</fk_parent_id>
    <record_value>40</record_value>
  </test_self_ref>
  <test_self_ref>
    <record_id>5</record_id>
    <fk_parent_id></fk_parent_id>
    <record_value>50</record_value>
  </test_self_ref>
  <test_self_ref>
    <record_id>6</record_id>
    <fk_parent_id>1</fk_parent_id>
    <record_value>60</record_value>
  </test_self_ref>
</dsOra>



I need an advice on the following:
1- Is XML the right way to handle this need? I had to insert fake record_id in the XML just to be able to trace the self-reference (the PK actually is filled from a sequence).
2- Is there a way using XML that allows the insert without loading the records in a cursor then loop over the cursor and update the record it with the read DB ID, and then insert?

Note: I have not invested much time in a draft SP as I ma not sure of the right way to do it:

PROCEDURE P_ADD_SELF_REF (XML IN XMLTYPE, I_CREATOR_ID IN NUMBER DEFAULT 0,S_MODULE_NAME IN VARCHAR2)
IS
   I_TEMPLATE_ID NUMBER(3);
BEGIN

END P_ADD_TEMPLATE;



Thanks
Ferro
Re: Using XML to insert into a table with self-reference [message #650990 is a reply to message #650980] Mon, 09 May 2016 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What need? What is the input and what is the desired result?

Re: Using XML to insert into a table with self-reference [message #651010 is a reply to message #650980] Mon, 09 May 2016 14:49 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
In order to automatically populate primary and foreign keys, you would need to structure your xml in some manner that it can be determined which child rows belong to which parent rows. Please see the following demonstration that uses such xml and a global temporary table to do one insert into the temporary table and two inserts into the target table, populating the primary and foreign keys from a sequence. It avoids looping row by row. It assumes there are only two levels. Someone else may be able to design a better method.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE test_self_ref
  2    (record_id     NUMBER,
  3  	fk_parent_id  NUMBER,
  4  	record_value  NUMBER,
  5  	PRIMARY KEY   (record_id),
  6  	CONSTRAINT    self_ref FOREIGN KEY (fk_parent_id)
  7  			       REFERENCES test_self_ref (record_id))
  8  /

Table created.

SCOTT@orcl_12.1.0.2.0> CREATE GLOBAL TEMPORARY TABLE test_self_ref_temp
  2    (record_id     NUMBER,
  3  	record_value  NUMBER,
  4  	fk2	      number,
  5  	val2	      number)
  6  /

Table created.

SCOTT@orcl_12.1.0.2.0> CREATE SEQUENCE template_seq
  2  /

Sequence created.

SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE p_add_self_ref
  2    (xml IN XMLTYPE)
  3  IS
  4    I_template_id  NUMBER(3);
  5  BEGIN
  6    DELETE FROM test_self_ref_temp;
  7    INSERT INTO test_self_ref_temp (record_id, record_value, fk2, val2)
  8    SELECT template_seq.NEXTVAL, x.record_value, template_seq.CURRVAL, y.record_value
  9    FROM   XMLTABLE
 10  		('/dsOra/test_self_ref'
 11  		  PASSING xml
 12  		  COLUMNS
 13  		    record_value  NUMBER  PATH '/test_self_ref/record_value',
 14  		    child_rows	  XMLTYPE PATH '/test_self_ref/child_row') x
 15  		 LEFT OUTER JOIN
 16  		 XMLTABLE
 17  		   ('/child_row'
 18  		    PASSING x.child_rows
 19  		    COLUMNS
 20  		      record_value  NUMBER  PATH '/child_row/record_value') y
 21  		 ON 1 = 1;
 22    INSERT INTO test_self_ref (record_id, fk_parent_id, record_value)
 23    SELECT record_id, NULL, record_value
 24    FROM   test_self_ref_temp;
 25    INSERT INTO test_self_ref (record_id, fk_parent_id, record_value)
 26    SELECT template_seq.NEXTVAL, fk2, val2
 27    FROM   test_self_ref_temp
 28    WHERE  val2 IS NOT NULL;
 29  END p_add_self_ref;
 30  /

Procedure created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> BEGIN
  2    p_add_self_ref (XMLTYPE (
  3  	 '<dsOra>
  4  	    <test_self_ref>
  5  	      <record_value>10</record_value>
  6  	      <child_row>
  7  		<fk_parent_id>1</fk_parent_id>
  8  		<record_value>60</record_value>
  9  	      </child_row>
 10  	    </test_self_ref>
 11  	    <test_self_ref>
 12  	      <record_value>20</record_value>
 13  	      <child_row>
 14  		<record_value>40</record_value>
 15  	      </child_row>
 16  	    </test_self_ref>
 17  	    <test_self_ref>
 18  	      <record_value>30</record_value>
 19  	    </test_self_ref>
 20  	    <test_self_ref>
 21  	      <record_value>50</record_value>
 22  	    </test_self_ref>
 23  	  </dsOra>'));
 24  END;
 25  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM test_self_ref
  2  /

 RECORD_ID FK_PARENT_ID RECORD_VALUE
---------- ------------ ------------
         1                        10
         2                        20
         3                        30
         4                        50
         5            1           60
         6            2           40

6 rows selected.

Previous Topic: Stitching strings into xml (merged)
Next Topic: Need to insert data from XML file into Oracle Table (very urgent)
Goto Forum:
  


Current Time: Thu Mar 28 11:13:30 CDT 2024