create table tb_sample
(
 id number,
 text varchar2(30),
 tag varchar2(20),
 parent_id number)

id text tag parent_id 
1  1    NULL NULL
2  2.1  NULL 1
3  3.1  NULL 2
4  3.2  NULL 2

create table tb_samplexmldata
(
 xmldata xmltype
);

create table tb_xmltorelational
(
id number, 
text varchar2(50),
tag varchar2(50),
parent_id number, primary key(id));

create sequence lastid start with 1 increment by 1 nomaxvalue;

insert into tb_samplexmldata values(xmltype(
'<?xml version="1.0"?>
<Node>
  <Text>RootNode</Text><Tag>RootTag</Tag>
  <Nodes>
    <Node>
      <Text>Child1</Text><Tag>Child1Tag</Tag>
      <Nodes>
        <Node>
          <Text>Child1.1</Text><Tag>Child1.1Tag</Tag>
        </Node>
        <Node>
          <Text>Child1.2</Text><Tag>Child1.2Tag</Tag>
          <Nodes>
            <Node>
              <Text>Child1.2.1</Text><Tag>Child1.2.1Tag</Tag>
            </Node>
          </Nodes>
        </Node>
      </Nodes>
    </Node>
  </Nodes>
</Node>'));

CREATE OR REPLACE PROCEDURE Pr_Xmltorelational
IS
xmlFile xmltype;
currvalue NUMBER;
BEGIN
   INSERT INTO  
     TB_XMLTORELATIONAL 
   SELECT 
     lastid.NEXTVAL,
     Extract(xmldata,'/Node/Text/text()').getStringVal(),
     Extract(xmldata,'/Node/Tag/text()').getStringVal(),
     NULL 
  FROM TB_SAMPLEXMLDATA;
    SELECT xmldata INTO xmlFile FROM TB_SAMPLEXMLDATA;
    SELECT MAX(id) INTO currvalue FROM TB_XMLTORELATIONAL;
     fn_getchildnodes(xmlFile,currvalue,'/Node/Nodes/Node/Text');
    commit;
END;


CREATE OR REPLACE PROCEDURE Fn_Getchildnodes(xmlData XMLTYPE,tagId NUMBER,xpath VARCHAR2)
IS 
 childNodes NUMBER;
 nextValue NUMBER;
BEGIN
  SELECT Extract(xmlData,'count('||xpath||')').getStringVal() 
  INTO childNodes FROM dual;/*Gives Error At Run Time*/
 FOR getChild  IN  1..childNodes LOOP
   INSERT INTO  TB_XMLTORELATIONAL 
   SELECT lastid.NEXTVAL,
	   Extract(xmlData,REPLACE(xpath,'Node/Text','Node['||getChild||']/Text/text()')),
	   Extract(xmlData,REPLACE(xpath,'Node/Text','Node['||getChild||']/Tag/text()')),tagId 
   FROM 
      dual;
 END LOOP;
 SELECT lastid.NEXTVAL INTO nextValue FROM dual;
 Fn_Getchildnodes(xmlData,nextValue,REPLACE(xpath,'Text','Nodes/Node/Text'));
END;
/

SQL> exec Pr_Xmltorelational
BEGIN Pr_Xmltorelational; END;

*
ERROR at line 1:
ORA-31012: Given XPATH expression not supported
ORA-06512: at "MICHEL.FN_GETCHILDNODES", line 6
ORA-06512: at "MICHEL.PR_XMLTORELATIONAL", line 16
ORA-06512: at line 1


SQL> @v

Version Oracle : 10.2.0.4.0

SCOTT@orcl_11g> create table tb_samplexmldata
  2    (xmldata    xmltype)
  3  /

Table created.

SCOTT@orcl_11g> create table tb_xmltorelational
  2    (id	   number,
  3  	text	   varchar2(50),
  4  	tag	   varchar2(50),
  5  	parent_id  number,
  6  	primary key (id))
  7  /

Table created.

SCOTT@orcl_11g> create sequence lastid start with 1 increment by 1 nomaxvalue
  2  /

Sequence created.

SCOTT@orcl_11g> insert into tb_samplexmldata values(xmltype(
  2  '<?xml version="1.0"?>
  3  <Node>
  4    <Text>RootNode</Text><Tag>RootTag</Tag>
  5    <Nodes>
  6  	 <Node>
  7  	   <Text>Child1</Text><Tag>Child1Tag</Tag>
  8  	   <Nodes>
  9  	     <Node>
 10  	       <Text>Child1.1</Text><Tag>Child1.1Tag</Tag>
 11  	     </Node>
 12  	     <Node>
 13  	       <Text>Child1.2</Text><Tag>Child1.2Tag</Tag>
 14  	       <Nodes>
 15  		 <Node>
 16  		   <Text>Child1.2.1</Text><Tag>Child1.2.1Tag</Tag>
 17  		 </Node>
 18  	       </Nodes>
 19  	     </Node>
 20  	   </Nodes>
 21  	 </Node>
 22    </Nodes>
 23  </Node>'))
 24  /

1 row created.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE pr_xmltorelational
  2    (p_xpath      IN VARCHAR2 DEFAULT '/Node',
  3  	p_parent_id  IN NUMBER	 DEFAULT NULL)
  4  AS
  5    v_childnodes	NUMBER;
  6  BEGIN
  7    SELECT COUNT (*)
  8    INTO   v_childnodes
  9    FROM   tb_samplexmldata t,
 10  	      TABLE (XMLSEQUENCE (EXTRACT (t.xmldata, p_xpath))) p;
 11    FOR child IN 1 .. v_childnodes
 12    LOOP
 13  	 INSERT INTO tb_xmltorelational (id, text, tag, parent_id)
 14  	 SELECT lastid.NEXTVAL,
 15  		EXTRACT (xmldata, p_xpath || '[' || child || ']/Text/text()').getStringVal(),
 16  		EXTRACT (xmldata, p_xpath || '[' || child || ']/Tag/text()').getStringVal(),
 17  		p_parent_id
 18  	 FROM	tb_samplexmldata;
 19  	 pr_xmltorelational (p_xpath || '[' || child || ']/Nodes/Node', lastid.CURRVAL);
 20    END LOOP;
 21  END pr_xmltorelational;
 22  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> EXEC pr_xmltorelational

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM tb_xmltorelational
  2  /

        ID TEXT                 TAG                   PARENT_ID
---------- -------------------- -------------------- ----------
         1 RootNode             RootTag
         2 Child1               Child1Tag                     1
         3 Child1.1             Child1.1Tag                   2
         4 Child1.2             Child1.2Tag                   2
         5 Child1.2.1           Child1.2.1Tag                 4

SCOTT@orcl_11g> 

select lastid.CURRVAL into  v_currval from dual;

pr_xmltorelational(
p_xpath
|| '[' || child || ']/Nodes/Node',  
v_currval);

SELECT STATEMENT CHOOSECost: 904  
4 SORT AGGREGATE   		
  3 NESTED LOOPS  Cost: 904  
   1 TABLE ACCESS FULL TB_SAMPLEXMLDATA Cost: 2    
   2 COLLECTION ITERATOR PICKLER FETCH XMLSEQUENCEFROMXMLTYPE 

SCOTT@orcl_11g> create table tb_samplexmldata
  2    (xmldata    xmltype)
  3  /

Table created.

SCOTT@orcl_11g> create table tb_xmltorelational
  2    (id	   number,
  3  	text	   varchar2(50),
  4  	tag	   varchar2(50),
  5  	parent_id  number,
  6  	primary key (id)
  7    )
  8  /

Table created.

SCOTT@orcl_11g> CREATE SEQUENCE lastid
  2  /

Sequence created.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE pr_xmltorelational
  2    (p_xmldata    in xmltype,
  3  	p_xpath      IN VARCHAR2 DEFAULT '/Node',
  4  	p_parent_id  IN NUMBER	 DEFAULT NULL)
  5  AS
  6    v_childnodes	NUMBER;
  7    v_seq		NUMBER;
  8  BEGIN
  9    SELECT COUNT (*)
 10    INTO   v_childnodes
 11    FROM   TABLE (XMLSEQUENCE (p_xmldata.EXTRACT (p_xpath))) p;
 12    FOR child IN 1 .. v_childnodes
 13    LOOP
 14  	 SELECT lastid.NEXTVAL INTO v_seq FROM DUAL;
 15  	 INSERT INTO tb_xmltorelational (id, text, tag, parent_id)
 16  	 SELECT v_seq,
 17  		EXTRACT (p_xmldata, p_xpath || '[' || child || ']/Text/text()').getStringVal(),
 18  		EXTRACT (p_xmldata, p_xpath || '[' || child || ']/Tag/text()').getStringVal(),
 19  		p_parent_id
 20  	 FROM	TABLE (XMLSEQUENCE (p_xmldata.EXTRACT ('/Node'))) p;
 21  	 pr_xmltorelational (p_xmldata, p_xpath || '[' || child || ']/Nodes/Node', v_seq);
 22    END LOOP;
 23  END pr_xmltorelational;
 24  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER xmltorelational
  2    BEFORE INSERT ON tb_samplexmldata
  3    FOR EACH ROW
  4  BEGIN
  5    pr_xmltorelational (:NEW.xmldata);
  6  END xmltorelational;
  7  /

Trigger created.

SCOTT@orcl_11g> show errors
No errors.
SCOTT@orcl_11g> insert into tb_samplexmldata values(xmltype(
  2  '<?xml version="1.0"?>
  3  <Node>
  4    <Text>RootNode</Text><Tag>RootTag</Tag>
  5    <Nodes>
  6  	 <Node>
  7  	   <Text>Child1</Text><Tag>Child1Tag</Tag>
  8  	   <Nodes>
  9  	     <Node>
 10  	       <Text>Child1.1</Text><Tag>Child1.1Tag</Tag>
 11  	     </Node>
 12  	     <Node>
 13  	       <Text>Child1.2</Text><Tag>Child1.2Tag</Tag>
 14  	       <Nodes>
 15  		 <Node>
 16  		   <Text>Child1.2.1</Text><Tag>Child1.2.1Tag</Tag>
 17  		 </Node>
 18  	       </Nodes>
 19  	     </Node>
 20  	   </Nodes>
 21  	 </Node>
 22    </Nodes>
 23  </Node>'))
 24  /

1 row created.

SCOTT@orcl_11g> insert into tb_samplexmldata values(xmltype(
  2  '<?xml version="1.0"?>
  3  <Node>
  4    <Text>2ndRootNode</Text><Tag>2ndRootTag</Tag>
  5    <Nodes>
  6  	 <Node>
  7  	   <Text>2ndChild1</Text><Tag>2ndChild1Tag</Tag>
  8  	   <Nodes>
  9  	     <Node>
 10  	       <Text>2ndChild1.1</Text><Tag>2ndChild1.1Tag</Tag>
 11  	     </Node>
 12  	     <Node>
 13  	       <Text>2ndChild1.2</Text><Tag>2ndChild1.2Tag</Tag>
 14  	       <Nodes>
 15  		 <Node>
 16  		   <Text>2ndChild1.2.1</Text><Tag>2ndChild1.2.1Tag</Tag>
 17  		 </Node>
 18  	       </Nodes>
 19  	     </Node>
 20  	   </Nodes>
 21  	 </Node>
 22    </Nodes>
 23  </Node>'))
 24  /

1 row created.

SCOTT@orcl_11g> select * from tb_xmltorelational
  2  /
        ID TEXT                 TAG                   PARENT_ID
---------- -------------------- -------------------- ----------
         1 RootNode             RootTag
         2 Child1               Child1Tag                     1
         3 Child1.1             Child1.1Tag                   2
         4 Child1.2             Child1.2Tag                   2
         5 Child1.2.1           Child1.2.1Tag                 4
         6 2ndRootNode          2ndRootTag
         7 2ndChild1            2ndChild1Tag                  6
         8 2ndChild1.1          2ndChild1.1Tag                7
         9 2ndChild1.2          2ndChild1.2Tag                7
        10 2ndChild1.2.1        2ndChild1.2.1Tag              9

10 rows selected.

SCOTT@orcl_11g>