Solved

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

Posted on 2008-10-31
3
924 Views
Last Modified: 2013-12-07
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
Comment
Question by:wxII
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 22857326
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 22857329
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
 

Author Comment

by:wxII
ID: 22859279
thanks for the reply,

it works well, thanks much

0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question