• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 929
  • Last Modified:

plsql getting nested xml element's depth stored in xmltype column

hi experts,

i'm trying to get the depth of selected xml element's depth. i've discovered a DEPTH() function but need UNDER_PATH function for it work and i'm not sure how to apply it in my situation.

for eg.

<ppl>
     <person name="johnny" age="13">
          <person name="snake" age="40">
                <person name="harry" age="35"/>
          </person>
     </person>
     <person name="connie" age="18">
           <person name="mandy" age="40"/>
     </person>
</ppl>

example above, how do i query the depth number of person element name="snake"? is it possible?

the oracle version i'm using is 11g. version 11.1.0.6. environment used solely in pl/sql.

please advise and thanks.

wxII
0
wxII
Asked:
wxII
  • 2
1 Solution
 
sdstuberCommented:
under_path only works for object in the XDB repository

as an alternate, you can generate the list of ancestors and then count them.

select count(*) from yourtable,xmltable('//person[@name="snake"]/ancestor::node()' passing yourxml)

you "should" also be able to the xpath count() function, however I couldn't get it to work, I got an unsupported expression exception, that may be a error on my part in the xpath syntax.
The above xmltable query does work though.  It returns a 1-based depth,  if you want the <ppl> root node to be depth 0 then subtract 1 from the count.
0
 
sdstuberCommented:
I would recommend cross posting to the 11.x zone as well as one of the XML zones for future posts.
The more zones you post to, the more people will see your question.

It also helps in future PAQ mining because the zones will help narrow down the solution search.
0
 
wxIIAuthor Commented:
thanks for the reply,

it works well, thanks much

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now