What is your Oracle version? EXTRACT and EXTRACTVALUE are deprecated since 11.2:
EXTRACTVALUEfunction is deprecated. It is still supported for backward compatibility. However, Oracle recommends that you use the
XMLTABLEfunction, or the
XMLQUERYfunctions instead. See XMLTABLE, XMLCAST, and XMLQUERY for more information.
XMLTABLE was designed exactly for your requirement. I find it a very efficient and powerful tool.
Does anyone have any suggestions on what might be the fastest approach here or have any other suggestions on something else that would be highly performant?)
We'll need more info to suggest the best approach.
- Database version? (SELECT * FROM v$version)
- How many files? Average size?
- Where do the files reside initially? Client or server-side? If the former, can you afford moving them to the db server?
- Are you implementing a recurring process or a one-shot?
Depending on the size and database version, I would either parse them directly into relational format from an Oracle directory (possibly in bulk via an external table), or load them first into a (temp) Binary XMLType column and process them from there.
Both approaches would use XMLTABLE.