title: Extract from XML response with multiple child nodes using PLSQL in different set of rows
body: <p>This is related to SOAP xml response, and fetching the data via Oracle PLSQL.</p>
  
  <p>The SOAP xml response is this (which contains multiple child nodes in contacts parent node) -</p>
  
  <pre><code>&lt;firstName&gt;ABC&lt;/firstName&gt;
  &lt;lastname&gt;XYZ&lt;/lastName&gt;
  &lt;gender&gt;M&lt;/gender&gt;
  &lt;service @effdate='2015-02-02'&gt;ACTIVE&lt;/service&gt;
  
  &lt;contacts&gt;
  &lt;contactTier&gt;&lt;contactId&gt;CT001&lt;/contactId&gt;&lt;contactName&gt;Core Contact&lt;/contactName&gt;&lt;/contactTier&gt;
  &lt;contactTier&gt;&lt;contactId&gt;CT015&lt;/contactId&gt;&lt;contactName&gt;Contact 2&lt;/contactName&gt;&lt;/contactTier&gt;
  &lt;/contacts&gt;
  </code></pre>
  
  <p>I have this select query for my ORACLE APEX report, which directly utilizes the SOAP xml response and displays the fields - </p>
  
  <pre><code>    select
      quota.firstName
     ,quota.lastName 
     ,quota.gender
     ,quota.service
     ,quota.contacts.extract('//contactTier//contactName//text()','xmlns="http://grocery.org/schema/quota"').getstringval() as name
     ,quota.contacts.extract('//contactTier//contactName//text()','xmlns="http://grocery.org/schema/quota"').getstringval() as ID
  
  
     from
     xmltable (
        xmlnamespaces(
           'http://grocery.org/schema/quota/service'
           ,'http://grocery.org/schema/quota'
           )
     ,'for $i in /*/*/*/quota/quota return /$i'
     passing (select xmlresp from mytable where id = 'SOAPID3')
     columns
         service                xmltype         path '/quota/v61:service'      
        ,contacts               xmltype          path '/quota/v61:contacts'        
        ,first_name             varchar2(2000)   path '/quota/v61:firstName'
        ,last_name              varchar2(2000)   path '/quota/lastName'
        ,gender                 varchar2(2000)   path '/quota/gender'
       ) quota ;
  </code></pre>
  
  <p>My concern is that the extract function concatenates the contactNames (from contacts node) and returns them as part of one single row.</p>
  
  <p>I need the query to return as many number of rows as there are contactTiers.</p>
  
  <p>(Please ignore the xmlnamespace and other aspects if they seem wrong..)
  Hope that makes sense..</p>
  
  <p>Looking forward to all replies.
  thanks in advance..</p>