| Using XML to insert into a table with self-reference [message #650980] |
Mon, 09 May 2016 06:15  |
 |
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 #651010 is a reply to message #650980] |
Mon, 09 May 2016 14:49  |
 |
Barbara Boehmer
Messages: 9103
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
