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)