create type x_row as object
(
xnode         VARCHAR2(100),
xpath         VARCHAR2(1000),
xval          VARCHAR2(255)
);
/
create type x_table as table of x_row;
/
create or replace function bal.xml2cursor (p_xml in xmltype) return x_table pipelined is
l_rootnode varchar2(100);
begin  
  l_rootnode := p_xml.getrootelement();
  for rec in (
    select  
    t6.child xnode,
    '/'||p_xml.getrootelement() ||sys_connect_by_path(t6.child, '/') xpath,
    t6.value  xval
    from 
    (
      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 ( 
       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(p_xml.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=l_rootnode 
    connect by prior t6.child=t6.parent
    order by level
  )
  loop
    pipe row( x_row(rec.xnode,rec.xpath,rec.xval) );
  end loop;
end xml2cursor;
/