Hi,

I am trying to parse an xml response that has multiple child nodes.

It's something like:

<List>
  <Status>ACK</Status>
  <Status>ACK</Status>
  <Status>NACK</Status>
</List>

I was able to extract one line at a time. But I need to get all of them at once and store in apex_collections.

So I tried these (possibly not-so-wise) things:

- declare the xpath with an anchor

l_xpath_anchor varchar2(100) := '//List[~]/Status/text()';

- create a loop and replace ~ with consecutive numbers, starting from 1 obviously. Then in the same loop call the parse_xml API:

select apex_web_service.parse_xml_clob(
                         p_xml => env,
    p_xpath => l_xpath_anchor,
    p_ns => l_xmlns)
into l_foo
from (select xmltype(service_response) env from xml_master where tx_id = 202001071406422305841 and tx_uid = 413);
l_xpath := replace(l_xpath, '~', i);
dbms_output.put_line (l_foo||'  i: '||i);

This actually worked for a sample data like above. It has 3 child nodes and I knew it had to loop 3 times.
But I can never it on original data.

Is there a way to know how many child node does a parent node have so I can be sure about how many time should it loop?

with x as (
  select xmltype ( '<List>
  <Status>ACK</Status>
  <Status>ACK</Status>
  <Status>NACK</Status>
</List>' ) doc
  from  dual
)
  select xt.* from x, xmltable (
    '/List/Status'
    passing x.doc 
    columns  
      status varchar2(10) path '//text()'
  ) xt;

STATUS   
ACK       
ACK       
NACK