Big Data/BI Zone is brought to you in partnership with:

DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Fyodor Kupolov

Fyodor has posted 14 posts at DZone. View Full User Profile

Importing XML Into A Database With Scriptella ETL

02.18.2007

| 7882 views |

        The following <a href="http://scriptella.javaforge.com">Scriptella ETL</a> simple usage example imports RSS file into a database table.

<!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
<etl>
    <connection id="in" driver="xpath" url="http://snippets.dzone.com/rss"/>
    <connection id="db" driver="hsqldb" url="jdbc:hsqldb:db/rss" user="sa" classpath="hsqldb.jar"/>
classpath="hsqldb.jar"/>
    <query connection-id="in">
        /rss/channel/item
        <script connection-id="db">
            INSERT INTO Rss (ID, Title, Description, Link) 
            VALUES (?rownum, ?title, ?description, ?link);
        </script>
    </query>
</etl>


Here is the full version of the example described above. It creates an RSS table, downloads rss file, inserts rss records into a database, converts rss.xml to a plain text file and saves it to rss.txt.

<!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
<etl>
    <connection id="in" driver="xpath" url="http://snippets.dzone.com/rss"/>
    <connection id="out" driver="text" url="rss.txt"/>
    <connection id="db" driver="hsqldb" url="jdbc:hsqldb:db/rss" user="sa" classpath="hsqldb.jar"/>
    <script connection-id="db">
       CREATE TABLE Rss (
           ID Integer,
           Title VARCHAR(255),
           Description VARCHAR(255),   
           Link VARCHAR(255)

       )
    </script>
    <query connection-id="in">
        /rss/channel/item
        <script connection-id="out">
            Title: $title
            Description: [
            ${description.substring(0, 20)}...
            ]
            Link: $link
            ----------------------------------
        </script>
        <script connection-id="db">
            INSERT INTO Rss (ID, Title, Description, Link) 
            VALUES (?rownum, ?title, ?description, ?link);
        </script>
    </query>
</etl>

Tags: