Drumrolls!  :  Here comes the XML collector

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:

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)