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
Solved

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

Posted on 2008-10-31
3
923 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 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

839 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