XML API to WebSource

useful url

https://community.oracle.com/thread/4323510

https://docs.oracle.com/en/database/oracle/application-express/19.2/aeapi/MAKE_REST_REQUEST-Function.html#GUID-C77BB45A-8968-470C-8243-BADB63743DE9

https://oracle-base.com/articles/misc/apex_web_service-consuming-soap-and-rest-web-services

https://oracle-base.com/articles/misc/xmltable-convert-xml-data-into-rows-and-columns-using-sql

https://stackoverflow.com/questions/34786699/select-and-update-oracle-blob-column-with-xmlquery

https://oracle-base.com/articles/misc/xmltable-convert-xml-data-into-rows-and-columns-using-sql

useful url

https://community.oracle.com/thread/4323510

https://docs.oracle.com/en/database/oracle/application-express/19.2/aeapi/MAKE_REST_REQUEST-Function.html#GUID-C77BB45A-8968-470C-8243-BADB63743DE9

https://oracle-base.com/articles/misc/apex_web_service-consuming-soap-and-rest-web-services

https://oracle-base.com/articles/misc/xmltable-convert-xml-data-into-rows-and-columns-using-sql

https://stackoverflow.com/questions/34786699/select-and-update-oracle-blob-column-with-xmlquery

https://oracle-base.com/articles/misc/xmltable-convert-xml-data-into-rows-and-columns-using-sql
===============
 xml api
============
http://<URL>/sap/opu/odata/sap/ZPM_ODATA_LOCGEO_SRV/ZPMT_LOC_GCORSet(LOCCODE='A00006',ZGEOFC='A00006-10')

==================
xml output
==================

<entry xmlns="http://www.w3.org/2005/Atom" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xml:base="<URL>/">

<id><URL></id>

<title type="text">XYZ</title>

<updated>2020-06-25T12:47:38Z</updated>

<category term="ZPM_ODATA_LOCGEO_SRV.ZPMT_LOC_GCOR" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme"/>

<link href="ZPMT_LOC_GCORSet(LOCCODE='A00006',ZGEOFC='A00006-10')" rel="edit" title="ZPMT_LOC_GCOR"/>

<content type="application/xml">

<m:properties>

<d:LOCCODE>A00006</d:LOCCODE>
<d:ZGEOFC>A00006-10</d:ZGEOFC>
<d:LATITUDE/>
<d:LONGITUDE/>
<d:GEOCORD>58.396989772717895,23.58112598246403,58.39906580249542,23.580536005944968,58.39961297313446,23.58252224966164,58.39763350288147,23.583023722283425</d:GEOCORD>

</m:properties>

</content>
</entry>


===========================
2) DB code
============================

2.1) CREATE OR REPLACE VIEW api_collection_v AS
    (SELECT
        c001   AS loccode,
        c002   AS zgeofc,
        c003   AS latitude,
        c004   AS geocord
    FROM
        apex_collections
    WHERE
        collection_name = 'API_COLLECTION');

 2.2)            
CREATE or replace PROCEDURE web_api_test AS
    l_clob CLOB;
BEGIN
    l_clob := apex_web_service.make_rest_request(
                  p_url => '<URL>')'
                , p_http_method => 'GET'
            );

    IF l_clob IS NOT NULL THEN
        IF apex_collection.collection_exists(p_collection_name => 'api_collection') THEN
            apex_collection.truncate_collection(p_collection_name => 'api_collection');
        ELSE
            apex_collection.create_collection(p_collection_name => 'api_collection');
        END IF;

        FOR cur_rec IN (
            SELECT
                xt.*
            FROM
                XMLTABLE ( xmlnamespaces( default 'http://www.w3.org/2005/Atom',
                                          'http://schemas.microsoft.com/ado/2007/08/dataservices/metadata' as "m",
                                          'http://schemas.microsoft.com/ado/2007/08/dataservices' as "d",
                                          '<URL>/' as "base"
                                          ),
                                          'entry/content/m:properties'
                                          PASSING xmltype(l_clob)
                                          COLUMNS
                                            loccode VARCHAR2(25)PATH 'd:LOCCODE',
                                            zgeofc VARCHAR2(25) PATH 'd:ZGEOFC',
                                            latitude VARCHAR2(25) PATH 'd:LATITUDE',
                                            geocord VARCHAR2(250) PATH 'd:GEOCORD'
                ) xt
        ) LOOP       
            apex_collection.add_member(
                    p_collection_name => 'api_collection',
                    p_c001 => cur_rec.loccode,
                    p_c002 => cur_rec.zgeofc,
                    p_c003 => cur_rec.latitude,
                    p_c004 => cur_rec.geocord
            );
        END LOOP;

    END IF;

END;

================================
3 Call Collection in Apex Report
================================

3.1 ) Re

go to apex and crate report based on view
  (select * from api_collection_v)

===================================
content-type defaul setting if need
===================================

  SET SERVEROUTPUT ON
BEGIN
  APEX_WEB_SERVICE.g_request_cookies.delete();
  APEX_WEB_SERVICE.g_request_cookies(1).name := 'username';
  APEX_WEB_SERVICE.g_request_cookies(1).value := 'me';

  DBMS_OUTPUT.put_line('name: ' || APEX_WEB_SERVICE.g_request_cookies(1).name);
  DBMS_OUTPUT.put_line('value: ' || APEX_WEB_SERVICE.g_request_cookies(1).value);
END;


====================
other example
====================


CREATE OR REPLACE FUNCTION add_numbers (p_int_1  IN  NUMBER,
                                        p_int_2  IN  NUMBER)
  RETURN NUMBER
AS
  l_clob    CLOB;
  l_result  VARCHAR2(32767);
BEGIN

  -- Get the XML response from the web service.
  l_clob := APEX_WEB_SERVICE.make_rest_request(
    p_url         => 'http://oracle-base.com/webservices/add-numbers.php',
    p_http_method => 'GET',
    p_parm_name   => APEX_UTIL.string_to_table('p_int_1:p_int_2'),
    p_parm_value  => APEX_UTIL.string_to_table(p_int_1 || ':' || p_int_2)
  );

  -- Display the whole document returned.
  DBMS_OUTPUT.put_line('l_clob=' || l_clob);

  -- Pull out the specific value of interest.
  l_result := APEX_WEB_SERVICE.parse_xml(
    p_xml   => XMLTYPE(l_clob),
    p_xpath => '//answer/number/text()'
  );

  DBMS_OUTPUT.put_line('l_result=' || l_result);

  RETURN TO_NUMBER(l_result);
END;
/