Solved

How to loop through the children nodes of an XML table with Oracle?

Posted on 2014-11-14
1
1,174 Views
Last Modified: 2014-11-22
Hello,

I'm trying to figure out how to loop through multiple child nodes of the same name in an XML table; like this:

<root>
    <attributes>
        <people>
            <name>Joe</name>
            <interests>Sports</interes<wbr ></wbr>ts>
        </people>
        <people>
            <name>James</name>
            <interests>Video Games</interests>
        </people>
    </attributes>
</root>

Open in new window


I would like to get the list of people who are interested in Sports, but when I try to get the people attribute; I get an error saying the XML node had to be unique and wouldn't work unless I use the specific number; at which point it becomes a little frustrating when the node isn't always in the same place every time.

Is there any smarter way to loop through the Oracle XML table programmatically with a query?

Appreciate any help!
0
Comment
Question by:Errang Genevre
[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
1 Comment
 
LVL 20

Accepted Solution

by:
flow01 earned 500 total points
ID: 40444348
You did not mention the  way  you were trying it.

after cleaning up
<interests>Sports</interes<wbr ></wbr>ts>

you can use an sql like this
  with your_xml
  as
  (
  select
  xmltype('
  <root>
    <attributes>
        <people>
            <name>Joe</name>
            <interests>Sports</interests>
        </people>
        <people>
            <name>James</name>
            <interests>Video Games</interests>
        </people>
    </attributes>
</root>
  ') xml_col
  from dual
  )
  select name, interests
  from your_xml,
  xmltable ('/root/attributes/people'
  passing(xml_col)
  columns name varchar2(200) path 'name',
          interests varchar2(4000) path 'interests'
  )
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
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 shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

690 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