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

Errang Genevre
Errang Genevre used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
IT-specialist
Commented:
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'
  )

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial