Home » RDBMS Server » Performance Tuning » Stroring Hierarchical Data(XML) into relational Table (Oracle 9.2,XP)
Stroring Hierarchical Data(XML) into relational Table [message #333672] Mon, 14 July 2008 02:08 Go to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Dear All,

Can you please guide me the logic to store Hierarchical Data(XML) in relational table using pl/sql.

Please find the sample file that has to be stored in this table.


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


Data into table Should Be.

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


I think to implement this functionality i need recursive call to same procedure.Is this Possible in Oracle.

Your expert Comments needed.

Regards,
Rajat


  • Attachment: tree.txt
    (Size: 0.33KB, Downloaded 192 times)

[Updated on: Mon, 14 July 2008 02:11]

Report message to a moderator

Re: Stroring Hierarchical Data(XML) into relational Table [message #333673 is a reply to message #333672] Mon, 14 July 2008 02:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Recursion is possible in Oracle, but I think the problems lie more in the structure of your XML.
What posessed you to come up with a structure that nests the same element name 5 levels deep?
Re: Stroring Hierarchical Data(XML) into relational Table [message #333677 is a reply to message #333673] Mon, 14 July 2008 02:26 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
JRowbottom Thanks for replying.

Actually the file shows a tree structure that is created via a user.A user can create multiple levels and sublevels under a parent node.I get an XML file of the Tree.

Then i need to store that in oracle table as parent child relationship.

I can't control level because that is user defined at runtime.

i.e. I have to convert a Hierarchical Data in Relational Table.


Regards,
Rajat

[Updated on: Mon, 14 July 2008 02:27]

Report message to a moderator

Re: Stroring Hierarchical Data(XML) into relational Table [message #333713 is a reply to message #333677] Mon, 14 July 2008 04:10 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Any ideas friends??

Regards,
Rajat
Re: Stroring Hierarchical Data(XML) into relational Table [message #334031 is a reply to message #333673] Tue, 15 July 2008 04:28 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Michel and Barbara Can you please give your expert comments on this problem.

I need your ideas freinds.

Regards,
Rajat
Re: Stroring Hierarchical Data(XML) into relational Table [message #334038 is a reply to message #334031] Tue, 15 July 2008 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not a simple problem and I don't have time to investigate on it. Sorry.

Regards
Michel

[Edit: add missing word]

[Updated on: Tue, 15 July 2008 05:54]

Report message to a moderator

Re: Stroring Hierarchical Data(XML) into relational Table [message #334041 is a reply to message #334038] Tue, 15 July 2008 04:53 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Thanks Michel for replying.

I am working on it.And i hope i can solve this.Will definitly post the results when done.

Regards,
Rajat
Re: Stroring Hierarchical Data(XML) into relational Table [message #334067 is a reply to message #334038] Tue, 15 July 2008 06:43 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Michel i Tried some code in 9i.But i got stuck on one thing.
Oracle 9i doesnot support (count) function in XPATH syntax.

Can you verify the results on 10G.

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;
/


Regards,
Rajat

[Updated on: Tue, 15 July 2008 08:22] by Moderator

Report message to a moderator

Re: Stroring Hierarchical Data(XML) into relational Table [message #334115 is a reply to message #334067] Tue, 15 July 2008 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's what I got:
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

Regards
Michel
Re: Stroring Hierarchical Data(XML) into relational Table [message #334194 is a reply to message #334067] Tue, 15 July 2008 15:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
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> 

Re: Stroring Hierarchical Data(XML) into relational Table [message #334221 is a reply to message #334194] Tue, 15 July 2008 22:30 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Thanks Michel And Barbara for verifying the results.


Regards,
Rajat Ratewal
Re: Stroring Hierarchical Data(XML) into relational Table [message #334227 is a reply to message #334221] Tue, 15 July 2008 23:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
rajatratewal wrote on Tue, 15 July 2008 20:30

Thanks Michel And Barbara for verifying the results.



I hope you noticed that my code was different from your code. I was not verifying the results of your code, which produced the same error in 11g as the other versions. I was demonstrating corrected code.

icon10.gif  Re: Stroring Hierarchical Data(XML) into relational Table [message #334230 is a reply to message #334227] Tue, 15 July 2008 23:18 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Yes i have checked that your code is different.And i tried it in 9i with this change.

select lastid.CURRVAL into  v_currval from dual;

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


And it's working perfectly.

Thanks barbara for saving my neck. Smile

Regards,
Rajat Ratewal

[Updated on: Tue, 15 July 2008 23:20]

Report message to a moderator

Re: Stroring Hierarchical Data(XML) into relational Table [message #334234 is a reply to message #334230] Tue, 15 July 2008 23:25 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Oops, yes, using nextval and currval without selecting is a new 11g feature:

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28424/adfns_whatsnew.htm#CEGDHCHF

I put the nextval in a select, but forgot about the currval.

[Updated on: Tue, 15 July 2008 23:26]

Report message to a moderator

Re: Stroring Hierarchical Data(XML) into relational Table [message #334242 is a reply to message #334234] Tue, 15 July 2008 23:43 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Thanks barbara but i think using
TABLE (XMLSEQUENCE (EXTRACT (t.xmldata, p_xpath))
is a performance hit.Because it will scan whole table.


I checked the cost of this query.

Explain Plan

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 


How can i improve the cost of this query.


Regards,
Rajat

[Updated on: Tue, 15 July 2008 23:45]

Report message to a moderator

Re: Stroring Hierarchical Data(XML) into relational Table [message #334250 is a reply to message #334242] Wed, 16 July 2008 00:01 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Well, it's past my bedtime. I think this thread started out in the SQL and PL/SQL forum and I moved it to the XML forum when it became more about XML. Since the new focus is tuning, I will move it to the performance tuning forum. Perhaps Ross Leishman will have some ideas. He is good at tuning and XML stuff.
Re: Stroring Hierarchical Data(XML) into relational Table [message #334253 is a reply to message #334242] Wed, 16 July 2008 00:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Bear in mind that we are selecting from a one-row table. How can we expect it to do anything other than a full table scan? A full table scan is not necessarily an evil thing. Did you run a test and check the time that it took to run?
Re: Stroring Hierarchical Data(XML) into relational Table [message #334287 is a reply to message #334253] Wed, 16 July 2008 01:48 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Dear Barbara,

In real time the table can have many records not single.

My main concern is

Table Access Full cost is 2 only.
When Oracle Do Nested Loop of Table with
COLLECTION ITERATOR PICKLER FETCH XMLSEQUENCEFROMXMLTYPE
It's cost becomes 904.

So step 2 is doing some extra work.My question is what
step 2 is doing.It scans whole table for each row
accessed via step 1.

Regards,
Rajat


Re: Stroring Hierarchical Data(XML) into relational Table [message #334304 is a reply to message #334287] Wed, 16 July 2008 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
question is what step 2 is doing

Analyzing and extracting information from XML.
This is a very expensive operation.

Regards
Michel

Re: Stroring Hierarchical Data(XML) into relational Table [message #334458 is a reply to message #334287] Wed, 16 July 2008 10:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
I was under the apparent misimpression that the entire hierarchy was going to be contained in one row. If that is not the case, then the procedure will not work properly. An alternative method would be to use a trigger that executes a modified procedure to process the row. That would eliminate the full table scan. However, you are still going to have the pickler fetch from the virtual table. You should rely on timings, not cost. The cost is just something that Oracle uses internally to compare one plan to another internally. Since it does not know how many rows are in the virtual table, it assigns the number 8,168 by default. Unless you actually have 8,168 rows in the hierarchy of one row of xml data, your performance is not going to be that bad. Please test and time, instead of relying on misleading cost values. Also, bear in mind, as previously stated that your entire design is strange, so this is just making the best you can of the situation. Please see the demonstration below which uses a trigger to pass one row of xml data to the procedure for processing on insert.

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> 


Re: Stroring Hierarchical Data(XML) into relational Table [message #335498 is a reply to message #334458] Tue, 22 July 2008 06:03 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Thanks Barbara for explaining.

That what i like about your post.
Simple and Full of Information.


Regards,
Rajat
Re: Stroring Hierarchical Data(XML) into relational Table [message #350358 is a reply to message #333672] Wed, 24 September 2008 17:11 Go to previous message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
Hi Barbara,

How should I convert this into forall loop. I tried using forall but only the last record is being inserted. It overwrites the previous records while inserting.

Ravi
Previous Topic: procedure tuning
Next Topic: Improve in-memory sort performance
Goto Forum:
  


Current Time: Fri Dec 09 19:19:50 CST 2016

Total time taken to generate the page: 0.09650 seconds