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 Go to next message
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 #400858 is a reply to message #400857] Thu, 30 April 2009 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your query is too complex for us to take time to reverse engineer it to understand what it does (or is supposed to do).
Put detailled specifications of the result you want.

Regards
Michel
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 Go to previous message
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 2020 times)

[Updated on: Mon, 04 May 2009 02:49] by Moderator

Report message to a moderator

Previous Topic: How to generate XSL file from table data
Next Topic: Loading XML data into a table
Goto Forum:
  


Current Time: Fri Mar 29 05:22:06 CDT 2024