I try to find some efficient way to load ~500 Excel worksheets (exported as XML 2003 format) into Oracle DB to further processing. Don't ask why not CSV... just not -.-
I find some convinient way to transform Excel xml file to much more decent form, but I wonder if there is some way to use XQuery transformation in external table definition?
I suspect it's not possible, so some alternative way?
Solution: UTL_FILE -> XMLTYPE -> XQuery transformation -> Loading into table using PL/SQL (with xmltype and xquery inside) looks quite ugly...
DB version: 220.127.116.11
See "2. Reading a file" in How To : Read and Write Office 2003 Excel XML Files | Odie's Oracle Blog
You can use the external table (or SQL*Loader) to bulk load all files at once in a binary XMLType table, and then using XMLTable to query the content.
There's another way I've been exploring recently, involving the external table preprocessor and XSLT, but it only performs well in the latest 12.1 release.
You can read more here if interested : XMLTABLE vs. External XSLT preprocessor | Odie's Oracle Blog
But for your current requirement, an XMLTable query as described in the first link should be just fine.