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 Go to next message](/forum/theme/orafaq/images/down.png) |
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 1395 times)
[Updated on: Mon, 14 July 2008 02:11] Report message to a moderator
|
|
|
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #334067 is a reply to message #334038] |
Tue, 15 July 2008 06:43 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #334194 is a reply to message #334067] |
Tue, 15 July 2008 15:16 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 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 #334242 is a reply to message #334234] |
Tue, 15 July 2008 23:43 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #334287 is a reply to message #334253] |
Wed, 16 July 2008 01:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #334458 is a reply to message #334287] |
Wed, 16 July 2008 10:46 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 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>
|
|
|
|
|
Goto Forum:
Current Time: Wed Sep 11 21:23:40 CDT 2024
|