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 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
icon14.gif  Re: Cursor FOR LOOP Issue [message #301776 is a reply to message #301765] Thu, 21 February 2008 12:23 Go to previous message
rs1969
Messages: 24
Registered: December 2007
Location: UK
Junior Member
Thanks very much
Previous Topic: error occurring in java source
Next Topic: How to read a list returned by Java in PL/SQL?
Goto Forum:
  


Current Time: Thu Mar 28 15:16:23 CDT 2024