SQL select from a XML file or directory of xml files
646659Jun 23 2008 — edited Aug 26 2008I'm running Oracle 10gr2 and have turned on auditing using the xml,extended audit_trail so that it creates xml audit files in the audit directory. Once a month I copy the xml audit files found in the audit directory to another machine where the auditor can get to them and then I clear out the current audit directory. The auditor would like to be able to query the xml audit files like a sql table. On the database being audited, I use the Oracle provided view, v$xml_audit_trail, to query the xml audit files. Works very nice and is very simple. I was hoping on just creating a view like v$xml_audit_trail that our auditor could use only from a different database. Figure I'd just move the xml audit files to another directory on another Oracle 10g or 11g server (we have both) and build the view there that the user could just query from. I do realize that I could just copy the data over to a table that the user could query but I'd rather allow the user to work with the xml files and since the v$xml_audit_trail view works so well, that is the functionality I was hoping on duplicating. I can't seem to see how Oracle's v$xml_audit_trail view works. Any ideas on how I can accomplish this? I'd like to build a view on a directory containing Oracle xml audit files that a user can SQL query from. Just like I query from v$xml_audit_trail. I hope this makes sense. Any ideas?