Hello everyone!
As I'm not still too used to handle XML, I'd like to ask for some ideas on how to solve a task I've got in hands.
Here's the situation: We're using APEX 5.1 (DB is XE 11g) to build an application, and so have created a page with some search fields, a classic report and a submit button. On submit, this page has a process that calls a (REST) webService (passing the search fields as parameters) and stores its response (XML generated from a query in another db, which I have no access to) in a collection. Then, I'm simply using XMLTABLE on that collection and referencing the columns path to 'feed' the report. The problems with this approach is that, first, it generates a lot of traffic, as everytime the page is submitted, a call to the webService is made, and second, depending on the parameters for the webService, this XML response could be huge (could be a whole table). The solution we thought of is to create a DB job that daily calls this webService (with no parameters, as to get all the data) and store the result in a 'temporary' table of our own, on which we could run our queries. This job would then daily drop the old table and recreate a new one with the updated data. The reason we drop and re-create the table each time is that we found out that, even though the XML is the result of a query in a table and as such its tags always have the same name, sometimes it does not have tha same number of tags in each "block" (i.e. sometimes it does not return all the columns for a row, could be that some columns are nullable at source). As such, there is theorically no way to know the real structure of the XML (we don't have the XSD either) until we parse it.
I know this seems like a situation where we would need to guess the structure of the XML (and indeed it sort of is!), however, this guessed structure would be used only once, as we would daily drop the old table and re-create it based on the new XML.
I thought of something in the likes of what https://www.freeformatter.com/xsd-generator.html and http://xmlgrid.net/xml2xsd.html do. Again, I know it's only a possible XSD but in my context it should be enough. However I did not find anything native in Oracle that would "guess" the structure of a XML.
In summary, I would need general ideas ou thoughts on how to create and populate a table based on an XML without an exact structure. Is there any way to natively "guess" the structure of a XML by parsing it? Or is there another easy way to handle (reorder, group by, etc) data in a XML, even if not by storing it in a table? Am I in a totally wrong path?
Thanks!