Home » Developer & Programmer » JDeveloper, Java & XML » query to fetch an xmls's absolute xpath and value (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)
| query to fetch an xmls's absolute xpath and value [message #400857] |
Thu, 30 April 2009 01:09  |
suksar
Messages: 9
Registered: February 2008
|
Junior Member |
|
|
Hi all,
My requirement is to parse an xml,show that xmls all tags absoulte path along with its value [if exists]
I have wrote a query like bellow :
SELECT LEVEL, t6.CHILD,
'/Objects' || SYS_CONNECT_BY_PATH (t6.CHILD, '/') xpath, t6.VALUE
FROM (SELECT tt.PARENT,
tt.CHILD
|| DECODE
(tt.child_part,
1, '',
'['
|| ( (RANK () OVER (PARTITION BY tt.child_part ORDER BY ROWNUM)
)
- 1
)
|| ']'
) CHILD,
tt.VALUE
FROM (WITH t AS
(SELECT q.rn, q.nodes,
q.nodes
|| DECODE
(q.node_part,
1, '',
'['
|| ( (RANK () OVER (PARTITION BY q.nodes ORDER BY ROWNUM)
)
- 1
)
|| ']'
) node_ind,
RANK () OVER (PARTITION BY q.nodes ORDER BY ROWNUM)
node_rank,
q.node_part, q.child_xml
FROM (SELECT ROWNUM rn,
t3.COLUMN_VALUE.getrootelement
() nodes,
t3.COLUMN_VALUE child_xml,
COUNT (1) OVER (PARTITION BY t3.COLUMN_VALUE.getrootelement
())
node_part
FROM TABLE
(XMLSEQUENCE
(XMLTYPE
('<Objects>
<Label1>
<Label2>
<Label3>
<Labelx>x1</Labelx>
<Labelx>x2</Labelx>
<Labelx>x3</Labelx>
</Label3>
<Label3>y1</Label3>
</Label2>
</Label1>
<Label4>Label 4</Label4>
<Label5>
<Label6>Label 6</Label6>
</Label5>
</Objects>'
).EXTRACT ('//node()')
)
) t3) q)
SELECT t4.node_ind PARENT, t4.node_rank,
t5.COLUMN_VALUE.getrootelement () CHILD,
COUNT (1) OVER (PARTITION BY t4.rn, t5.COLUMN_VALUE.getrootelement
())
child_part,
t4.node_part,
EXTRACTVALUE (VALUE (t5),
'/child::node()/text()'
) VALUE
FROM t t4,
TABLE
(XMLSEQUENCE
(t4.child_xml.EXTRACT ('/*/child::node()')
)
) t5) tt) t6
START WITH t6.PARENT = 'Objects'
CONNECT BY PRIOR t6.CHILD = t6.PARENT
ORDER BY LEVEL;
It seems the query is too costly and is not working if the xml is too large.
Any body have any better idea regarding this.
My database version is : Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi.
Thanks,
Suk
|
|
|
|
|
|
| Re: query to fetch an xmls's absolute xpath and value [message #401335 is a reply to message #400858] |
Mon, 04 May 2009 02:18  |
suksar
Messages: 9
Registered: February 2008
|
Junior Member |
|
|
A bit modified to remove few bug .
Here I am trying to transform an XML to a cursor in one query?
Well, here is something that I have come up with. It is not very good in performance, since it uses the window functions and collection iterator but there are a few advanges like it removes namespace, handles special characters and most importantly it is only one query.
--Beginning of Level 5 :Generates the Xpath --
select
t6.child,
'/Objects'||Sys_connect_by_path(t6.child, '/') xpath, --Replace 'Objects' with root node here --
t6.value
from
(
--Beginning of Level 4 : Prepares index for repetitive block --
select
tt.parent,
tt.child||
decode(
tt.child_part,1,'','['||((rank() over (partition by tt.parent,tt.child order by rownum))-1)||']'
) child,
tt.value
from (
--Beginning of Level 3 : Seperates out the parent and child --
with t as
(
--Beginning of Level 2 : Assign a rank to count the nodes --
select
q.rn,
q.nodes,
q.nodes||decode(
q.node_part,1,'','['||((rank () over (partition by q.nodes order by rownum))-1)||']'
) node_ind,
rank () over (partition by q.nodes order by rownum) node_rank,
q.node_part,
q.child_xml
from
(
--Beginning of Level 1 : Breaks the XML to nodes and give a partition index --
select
rownum rn,
t3.column_value.getrootelement() nodes,
t3.column_value child_xml,
count(1) over (partition by t3.column_value.getrootelement()) node_part
from
table(xmlsequence(xmltype('<Objects>
<Label1>
<Label2>
<Label3>L1</Label3>
<Label3>L2</Label3>
<Label4>
<Lable5>L5</Lable5>
<Lable5>L51</Lable5>
<Lable6>
<Lable7>L7</Lable7>
<Lable7>L71</Lable7>
</Lable6>
<Lable6>
<Lable7>L67</Lable7>
<Lable7>L6</Lable7>
</Lable6>
</Label4>
</Label2>
</Label1>
</Objects>').extract('//node()'))) t3
--End of Level 1 --
) q
--End of Level 2 --
)
select
t4.node_ind parent,
t4.node_rank,
t5.column_value.getrootelement() child,
count(1) over (partition by t4.rn,t5.column_value.getrootelement()) child_part,
t4.node_part,
extractvalue(value(t5),'/child::node()/text()') value
from t t4,
table(xmlsequence(
t4.child_xml.extract('/*/child::node()')
)
) t5
--End of Level 3 --
) tt
--End of Level 4 --
) t6
start with t6.parent='Objects' --Replace 'Objects' with root node here --
connect by prior t6.child=t6.parent
order by level;
---------------------------------------------
can be used it as a pipelined function (source attached) as well
[Mod-edit: Frank added code tags]
-
Attachment: setup.sql
(Size: 2.07KB, Downloaded 2547 times)
[Updated on: Mon, 04 May 2009 02:49] by Moderator Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Sat Feb 08 07:46:53 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
