Michael Bluteau

Drumrolls!  :  Here comes the XML collector

Blog Post created by Michael Bluteau Employee on May 4, 2016

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):


        <room num="1">
            <name>Room 1</name>
        <room num="2">
            <name>Room 2</name>
        <room num="3">
            <name>Room 3</name>
        <room num="4">
            <name>Room 4</name>
        <account id="accountA">
            <shortDesc>Acct A</shortDesc>
            <longDesc>Account A</longDesc>
        <account id="accountB">
            <shortDesc>Acct B</shortDesc>
            <longDesc>Account B</longDesc>
        <account id="accountC">
            <shortDesc>Acct C</shortDesc>
            <longDesc>Account C</longDesc>
        <account id="accountD">
            <shortDesc>Acct D</shortDesc>
            <longDesc>Account D</longDesc>



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
accountAAcct Aaccount A1
accountBAcct Baccount B1
accountCAcct Caccount C1
accountDAcct Daccount D1


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:


Ritesh Kesharwani: Load/Import XML file into database table (Oracle or SQL Server)