?
Solved

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

Posted on 2008-10-31
3
Medium Priority
?
926 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 2000 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

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

777 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