Home » Developer & Programmer » JDeveloper, Java & XML » Cursor FOR LOOP Issue (10g Release 10.2.0.3.0)
| Cursor FOR LOOP Issue [message #301427] |
Wed, 20 February 2008 10:09  |
rs1969
Messages: 24
Registered: December 2007
Location: UK
|
Junior Member |
|
|
I am extracting XML data and inserting into various tables. Curently I am navigating through the XML structure using nested cursor FOR LOOPs. Is there an more efficient way as ideally I would like to do a INSERT INTO ... SELECT FROM ... but not sure how to code it?
This is just an example, as the actual XML is quite big and stored on the database. Thanks in advance.
SET SERVEROUT ON
DECLARE
l_user VARCHAR2(30);
l_group VARCHAR2(30);
l_xml XMLTYPE := XMLTYPE('<MigInDays>
<MigInDay type="User">
<XML>
<UN>ENGR01</UN>
<UG/>
<FN>RIPOSTE ENGIN01</FN>
<COM>ENGINEER</COM>
<PE>0</PE>
<AT/>
<UGS>
<UG>ENGINEER</UG>
</UGS>
</XML>
<XML>
<UN>MIGR01</UN>
<UG/>
<FN>MIGRATION USER</FN>
<COM>MIGRATE</COM>
<PE>0</PE>
<AT/>
<UGS>
<UG>MANAGERS</UG>
<UG>MIGRATE</UG>
</UGS>
</XML>
</MigInDay>
<MigInDay type="User">
<XML>
<UN>ENGR02</UN>
<UG/>
<FN>RIPOSTE ENGIN02</FN>
<COM>ENGINEER2</COM>
<PE>0</PE>
<AT/>
<UGS>
<UG>ENGINEER</UG>
</UGS>
</XML>
<XML>
<UN>MIGR02</UN>
<UG/>
<FN>MIGRATION USER2</FN>
<COM>MIGRATE2</COM>
<PE>0</PE>
<AT/>
<UGS>
<UG>MANAGERS</UG>
<UG>MIGRATE</UG>
</UGS>
</XML>
</MigInDay>
</MigInDays>');
BEGIN
FOR r1 IN (SELECT inner.xml_frag
FROM (SELECT VALUE(des) xml_frag
FROM TABLE(XMLSequence(EXTRACT(l_xml,'/MigInDays/MigInDay'))) des
WHERE existsNode(l_xml,'/MigInDays/MigInDay') = 1
) inner
WHERE existsNode(inner.xml_frag,'/MigInDay[@type="User"]') = 1)
LOOP
FOR r1x IN (SELECT VALUE(des) xml_frag
FROM TABLE(XMLSequence(EXTRACT(r1.xml_frag,'/MigInDay/XML'))) des
WHERE existsNode(r1.xml_frag,'/MigInDay/XML') = 1 )
LOOP
SELECT extractValue(r1x.xml_frag,'/XML/UN') INTO l_user FROM DUAL;
dbms_output.put_line('User '||l_user);
-- INSERT INTO t1 (c1, c2, ...) VALUES (r1x.value1, r1x.value2, ...);
--
-- Split out the USER_ROLES.
--
FOR r1y IN (SELECT VALUE(des) xml_frag
FROM TABLE(XMLSequence(EXTRACT(r1x.xml_frag,'/XML/UGS/UG'))) des
WHERE existsNode(r1x.xml_frag,'/XML/UGS/UG') = 1)
LOOP
SELECT extractValue(r1y.xml_frag,'/UG') INTO l_group FROM DUAL;
dbms_output.put_line('Group '||l_group);
-- INSERT INTO t2 (c1, c2, ...) VALUES (r1x.value1, r1y.value1, ...);
END LOOP; -- r1y
--
END LOOP; -- r1x
--
END LOOP; -- r1
END;
/
|
|
|
|
| Re: Cursor FOR LOOP Issue [message #301477 is a reply to message #301427] |
Wed, 20 February 2008 16:55  |
mchadder
Messages: 224
Registered: May 2005
Location: UK
|
Senior Member |
|
|
Hello there.
Well, my first thought would be that there's quite a bit of processing which is unnecessary there anyway, such as the EXISTSNODE in the WHERE (the xpath will not extract where the node doesn't exist anyway), also this can be done in two FOR loops, rather than three.
Here's my setup :
SQL> CREATE TABLE t1 ( un_value VARCHAR2(100), fn_value VARCHAR2(100) );
Table created.
SQL> CREATE TABLE t2 ( un_value VARCHAR2(100), ug_value VARCHAR2(100) );
Table created.
SQL> VARIABLE clb CLOB
begin
:clb :=
'<MigInDays>
<MigInDay type="User">
<XML>
<UN>ENGR01</UN>
<UG/>
<FN>RIPOSTE ENGIN01</FN>
<COM>ENGINEER</COM>
<PE>0</PE>
<AT/>
<UGS>
<UG>ENGINEER</UG>
</UGS>
</XML>
<XML>
<UN>MIGR01</UN>
<UG/>
<FN>MIGRATION USER</FN>
<COM>MIGRATE</COM>
<PE>0</PE>
<AT/>
<UGS>
<UG>MANAGERS</UG>
<UG>MIGRATE</UG>
</UGS>
</XML>
</MigInDay>
<MigInDay type="User">
<XML>
<UN>ENGR02</UN>
<UG/>
<FN>RIPOSTE ENGIN02</FN>
<COM>ENGINEER2</COM>
<PE>0</PE>
<AT/>
<UGS>
<UG>ENGINEER</UG>
</UGS>
</XML>
<XML>
<UN>MIGR02</UN>
<UG/>
<FN>MIGRATION USER2</FN>
<COM>MIGRATE2</COM>
<PE>0</PE>
<AT/>
<UGS>
<UG>MANAGERS</UG>
<UG>MIGRATE</UG>
</UGS>
</XML>
</MigInDay>
</MigInDays>';
end;
/
And then using the XML unnesting technique ( see here ) :
BEGIN
FOR i IN ( SELECT
EXTRACTVALUE(VALUE(t), '/XML/UN') un_value,
EXTRACTVALUE(VALUE(t), '/XML/FN') fn_value,
EXTRACT(VALUE(t), '/XML/UGS') ugs_xml
FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(:clb), '/MigInDays/MigInDay/XML'))) t )
LOOP
INSERT INTO t1 ( un_value, fn_value ) VALUES ( i.un_value, i.fn_value );
FOR j IN ( SELECT EXTRACTVALUE(VALUE(t), '/UG') ug_value
FROM TABLE(XMLSEQUENCE(EXTRACT(i.ugs_xml, '/UGS/UG'))) t )
LOOP
INSERT INTO t2 ( un_value, ug_value ) VALUES ( i.un_value, j.ug_value );
END LOOP;
END LOOP;
END;
/
However, If you want it in one INSERT statement, then you'll have to use some form of the multi-table insert syntax, with some analytics to conditionalise access to t1 (since we'd otherwise get "n" rows in t1 since we're now joining). Note, that I am assuming here that "UN" is effectively the identifier for the <XML> entry, if this is not the case, we'd have to come up with some alternative.
INSERT ALL
WHEN un_rn = 1 THEN
INTO t1 VALUES ( un_value, fn_value )
WHEN 1=1 THEN
INTO t2 VALUES ( un_value, ug_value )
SELECT
un_value,
fn_value,
ROW_NUMBER() OVER ( PARTITION BY un_value ORDER BY un_value ) un_rn,
ug_value
FROM (
SELECT
EXTRACTVALUE(VALUE(t), '/XML/UN') un_value,
EXTRACTVALUE(VALUE(t), '/XML/FN') fn_value,
EXTRACTVALUE(VALUE(s), '/UG') ug_value
FROM
TABLE(XMLSEQUENCE(EXTRACT(xmltype(:clb), '/MigInDays/MigInDay/XML'))) t,
TABLE(XMLSEQUENCE(EXTRACT(VALUE(t), '/XML/UGS/UG'))) s
);
Checking the tables after each run produces the same output :
SQL> select * from t1;
UN_VALUE FN_VALUE
---------- --------------------
ENGR01 RIPOSTE ENGIN01
ENGR02 RIPOSTE ENGIN02
MIGR01 MIGRATION USER
MIGR02 MIGRATION USER2
4 rows selected.
SQL> select * from t2;
UN_VALUE UG_VALUE
---------- ----------
ENGR01 ENGINEER
ENGR02 ENGINEER
MIGR01 MANAGERS
MIGR01 MIGRATE
MIGR02 MANAGERS
MIGR02 MIGRATE
6 rows selected.
Regards
|
|
|
|
| Re: Cursor FOR LOOP Issue [message #301677 is a reply to message #301427] |
Thu, 21 February 2008 05:53 |
rs1969
Messages: 24
Registered: December 2007
Location: UK
|
Junior Member |
|
|
Thanks for your prompt reply. I think the EXISTSNODE is necessary as there can be another nested <XML> tag for another "Type".
e.g.
<MigInDays>
<MigInDay type="User">
<XML>
...
</XML>
</MigInDay>
<MigInDay type="Stock">
<XML>
...
</XML>
</MigInDay>
</MigInDays>
Sorry I did not make it clear earlier. Can I still get rid of the 3 loops?
|
|
|
|
| Re: Cursor FOR LOOP Issue [message #301765 is a reply to message #301427] |
Thu, 21 February 2008 10:58 |
mchadder
Messages: 224
Registered: May 2005
Location: UK
|
Senior Member |
|
|
Hi.
Oh, yeah, I missed the xpath expression there. No worries, you can do that in the EXTRACT, i.e.
BEGIN
FOR i IN ( SELECT
EXTRACTVALUE(VALUE(t), '/XML/UN') un_value,
EXTRACTVALUE(VALUE(t), '/XML/FN') fn_value,
EXTRACT(VALUE(t), '/XML/UGS') ugs_xml
FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(:clb), '/MigInDays/MigInDay[@type="User"]/XML'))) t )
LOOP
INSERT INTO t1 ( un_value, fn_value ) VALUES ( i.un_value, i.fn_value );
FOR j IN ( SELECT EXTRACTVALUE(VALUE(t), '/UG') ug_value
FROM TABLE(XMLSEQUENCE(EXTRACT(i.ugs_xml, '/UGS/UG'))) t )
LOOP
INSERT INTO t2 ( un_value, ug_value ) VALUES ( i.un_value, j.ug_value );
END LOOP;
END LOOP;
END;
/
Or
INSERT ALL
WHEN un_rn = 1 THEN
INTO t1 VALUES ( un_value, fn_value )
WHEN 1=1 THEN
INTO t2 VALUES ( un_value, ug_value )
SELECT
un_value,
fn_value,
ROW_NUMBER() OVER ( PARTITION BY un_value ORDER BY un_value ) un_rn,
ug_value
FROM (
SELECT
EXTRACTVALUE(VALUE(t), '/XML/UN') un_value,
EXTRACTVALUE(VALUE(t), '/XML/FN') fn_value,
EXTRACTVALUE(VALUE(s), '/UG') ug_value
FROM
TABLE(XMLSEQUENCE(EXTRACT(xmltype(:clb), '/MigInDays/MigInDay[@type="User"]/XML'))) t,
TABLE(XMLSEQUENCE(EXTRACT(VALUE(t), '/XML/UGS/UG'))) s
);
Regards
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Nov 25 01:03:33 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
