Ever been looking for the (or a) XML collector? You have a file in XML, let's say with some accounts in it, a simple XML file. Is there a quick way to collect it without having to convert to CSV before? Here is a quick example, based on a simple XML file. I will provide a more complex example later.
XML file (you can download stuff.xml):
<stuff> <conferenceRooms> <room num="1"> <name>Room 1</name> </room> <room num="2"> <name>Room 2</name> </room> <room num="3"> <name>Room 3</name> </room> <room num="4"> <name>Room 4</name> </room> </conferenceRooms> <accounts> <account id="accountA"> <shortDesc>Acct A</shortDesc> <longDesc>Account A</longDesc> <level>1</level> </account> <account id="accountB"> <shortDesc>Acct B</shortDesc> <longDesc>Account B</longDesc> <level>1</level> </account> <account id="accountC"> <shortDesc>Acct C</shortDesc> <longDesc>Account C</longDesc> <level>1</level> </account> <account id="accountD"> <shortDesc>Acct D</shortDesc> <longDesc>Account D</longDesc> <level>1</level> </account> </accounts> </stuff>
------------------
You need to create a Directory for where your file is located and Grant access to AVUSER using a SQL tool:
CREATE OR REPLACE DIRECTORY test_dir AS '/home/oracle/dbtest'; grant read on directory test_dir to AVUSER;
If you run the following Query in SQL Developer or another tool:
SELECT x.* FROM (SELECT xmltype(bfilename('TEST_DIR','stuff.xml'), nls_charset_id('WE8ISO8859P1')) xmlcol FROM dual) t, XMLTABLE ('/stuff/accounts/account' PASSING t.xmlcol COLUMNS AcctID VARCHAR2(15) PATH '@id', shortDesc VARCHAR2(256) PATH 'shortDesc', longDesc VARCHAR2(512) PATH 'longDesc', alevel NUMBER(2) PATH 'level') x;
You get:
ACCTID | shortDesc | longDesc | Alevel |
---|---|---|---|
accountA | Acct A | account A | 1 |
accountB | Acct B | account B | 1 |
accountC | Acct C | account C | 1 |
accountD | Acct D | account D | 1 |
Now you need a collector for your file. You can create a simple Account Collector using database:
Note that you need to use a subquery otherwise when you try to click Finish it will complain that columns are not in Query.
For more information and examples you can look at:
http://viralpatel.net/blogs/oracle-xmltable-tutorial/
Ritesh Kesharwani: Load/Import XML file into database table (Oracle or SQL Server)
Hey Russell Waliszewski, I'm curious what you think about this in terms of upgradability... it's really cool that the directory object is OUTSIDE of AVUSER, so that is awesome- however do you think we'll have any issues with the GRANT after an upgrade? I'm actually seeing some other great use cases for this.