I need to parse the XML returned by an external REST API and sync its data into a regular table of rows/columns (with number and varchar2 datatypes and not XMLTYPE/CLOB) using REST Synchronization. When I try to create a REST Data Source using Simple HTTP, it does not recognize the incoming XML from the API endpoint and giving an error -
ORA-20987: No columns found for row selector ".". Try to auto-detect or review the JSON structure.
When I create the REST Data Source manually (it cannot Discover automatically), the Data Profile has wrong number of columns with wrong data-types and it doesn't fetch any data.
The examples I see are all using the XML data already residing inside a local table in an XMLTYPE column or CLOB column but not XML data returned from an external REST API.
Please help…
Thanks!