How to select XML and return a specific column from an anonymous block?

Question

I have an anonymous block in PL/SQL that returns to me an XML as the result of running a web service. How do I select this XML to only get a desired column?

Anonymous block:

DECLARE
l_filename varchar2(255);
l_BLOB BLOB;
l_CLOB CLOB;
l_envelope CLOB;
l_response_msg varchar2(32767);
l_result VARCHAR2(32767);
l_xml XMLTYPE;
BEGIN
l_envelope := q'!<?xml version='1.0' encoding='UTF-8'?>!';
l_envelope := l_envelope ||
'<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"xmlns:rev1="http://www.ikhon.com.br/soap/rev1">
<soapenv:Header/>
<soapenv:Body>
  <rev1:AssuntoPesquisar>
     <!--Optional:-->
     <rev1:cod_assunto>9999</rev1:cod_assunto>
     <!--Optional:-->
     <rev1:txt_conarq_codigo></rev1:txt_conarq_codigo>
     <!--Optional:-->
     <rev1:txt_conarq_assunto></rev1:txt_conarq_assunto>
  </rev1:AssuntoPesquisar>
</soapenv:Body>
</soapenv:Envelope>';

l_xml := apex_web_service.make_request(
p_url               => 'http://intrahml/system/x64/ws/Proton.asmx',
p_action            => 'http://www.ikhon.com.br/soap/rev1/AssuntoPesquisar',
p_envelope          => l_envelope,
p_username          => 'username',
p_password          => 'password' );

DBMS_OUTPUT.put_line('Resultado: ' || l_xml.getClobVal());

END;

Answer 1

It is possible to use the method Xmltable, where it is possible to map the xml and return it in an SQL query.

Example:

declare 
  -- Local variables here
  vXML XMLType;

  cursor consulta is 
    SELECT * FROM xmlTable('/CATALOG/CD' Passing vXML Columns ARTIST varchar2(20) path 'ARTIST');
begin
  -- Test statements here
  vXML := XMLType('<CATALOG><CD><TITLE>Empire Burlesque</TITLE><ARTIST>Bob Dylan</ARTIST><COUNTRY>USA</COUNTRY><COMPANY>Columbia</COMPANY><PRICE>10.90</PRICE><YEAR>1985</YEAR></CD><CD><TITLE>Hide your heart</TITLE><ARTIST>Bonnie Tyler</ARTIST><COUNTRY>UK</COUNTRY><COMPANY>CBS Records</COMPANY><PRICE>9.90</PRICE><YEAR>1988</YEAR></CD></CATALOG>');
  for item in consulta loop
    dbms_output.put_line(item.ARTIST);
  end loop;
end;