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?
If you're trying to pull out all the Status values, passing the document to XMLTable seems a better way to me.
With this you can convert the XML to relational rows-and-columns:
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