Solved

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

Posted on 2008-10-31
3
921 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
  • 2
3 Comments
 
LVL 73

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 73

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

867 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now