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

Question

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

Navigation

#1 by (1 votes)

1

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

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;

oracle select xml pl-sql

asked by anonymous 07.05.2018 / 20:18

1 answer

Error in scan (file = file, what = what ...) How to send the src value of an img tag to the value of a text field?

score 1 · Answer 1

You can use the XMLTable method, where you can map xml and returns it in a 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;