Store XML into a database

Discussion in 'Java' started by Fahd Shariff, Aug 12, 2005.

  1. Fahd Shariff

    Fahd Shariff Guest

    Hello,

    I have a reasonably large xml file which i need to parse and store into
    an oracle database.

    I am currently toying with 2 ideas:

    1. Parse XML using a SAX parser. Map xml tags to database column names
    and generate a prepared statement. Execute each one the normal JDBC
    way.

    2. Create an XSLT which converts the XML to a SQL script containing
    lots of insert statements. Use Runtime.exec to run the script on oracle
    using sqlplus.

    The more I think about it, the more I prefer the second option. Would
    it be faster? I guess the major overhead would be the Runtime.exec
    call? The first option seems a bit messy...

    Which would you use and why?

    --
    Fahd Shariff
     
    Fahd Shariff, Aug 12, 2005
    #1
    1. Advertising

  2. Fahd Shariff

    Daniel Dyer Guest

    On Fri, 12 Aug 2005 16:53:38 +0100, Fahd Shariff <>
    wrote:

    > Hello,
    >
    > I have a reasonably large xml file which i need to parse and store into
    > an oracle database.
    >


    Maybe you could do something with Castor (http://www.castor.org)?

    Dan.

    --
    Daniel Dyer
    http://www.dandyer.co.uk
     
    Daniel Dyer, Aug 12, 2005
    #2
    1. Advertising

  3. Fahd Shariff

    rpitre Guest

    Personally i like the first option since i don't have a clue how XSLT
    works...I've never used it....But If you go with the first option, make
    sure to use batch statements.....

    PreparedStatement pstmt = null;
    pstmt = con.prepareStatement("INSERT INTO multi_select_key_holder
    (group_uid, string_key) VALUES (?,?)");
    for (int i = 0; i < keys.length; i++) {
    pstmt.setString (1, groupUID);
    pstmt.setString (2, (String) keys);
    pstmt.addBatch();
    }
    int[] updateCounts = pstmt.executeBatch();
     
    rpitre, Aug 12, 2005
    #3
  4. Fahd Shariff wrote:
    >
    > I have a reasonably large xml file which i need to parse and store into
    > an oracle database.


    > 1. Parse XML using a SAX parser. Map xml tags to database column names
    > and generate a prepared statement. Execute each one the normal JDBC
    > way.


    Seems straightforward. Possibly cache the prepared statements.

    > 2. Create an XSLT which converts the XML to a SQL script containing
    > lots of insert statements. Use Runtime.exec to run the script on oracle
    > using sqlplus.


    The XSLT probably isn't going to be pleasant. Remember to escape special
    characters. Some of my old code to do it in XSLT 1.0 for XML is below
    (and that doesn't handle weird characters). It's not something you'd
    want to do unless you were committed to XSLT, and didn't want to break
    out. If you do it naively it could be quite slow. Actually XSLT is often
    going be slow and memory hungry (how often depending on implementation
    quality).

    Tom Hawtin

    <xsl:template name="escape">
    <xsl:param name="arg"/>
    <xsl:call-template name="escape-one">
    <xsl:with-param name="arg">
    <xsl:call-template name="escape-one">
    <xsl:with-param name="arg">
    <xsl:call-template name="escape-one">
    <xsl:with-param name="arg">
    <xsl:call-template name="escape-one">
    <xsl:with-param name="arg">
    <xsl:call-template name="escape-one">
    <xsl:with-param name="arg" select="$arg"/>
    <xsl:with-param name="target" select="'&amp;'"/>
    <xsl:with-param name="replace" select="'&amp;amp;'"/>
    </xsl:call-template>
    </xsl:with-param>
    <xsl:with-param name="target" select="'&lt;'"/>
    <xsl:with-param name="replace" select="'&amp;lt;'"/>
    </xsl:call-template>
    </xsl:with-param>
    <xsl:with-param name="target" select="'&gt;'"/>
    <xsl:with-param name="replace" select="'&amp;gt;'"/>
    </xsl:call-template>
    </xsl:with-param>
    <xsl:with-param name="target" select="'&quot;'"/>
    <xsl:with-param name="replace" select="'&amp;quot;'"/>
    </xsl:call-template>
    </xsl:with-param>
    <xsl:with-param name="target" select='"&apos;"'/>
    <xsl:with-param name="replace" select='"&amp;apos;"'/>
    </xsl:call-template>
    </xsl:template>


    <xsl:template name="escape-one">
    <xsl:param name="arg"/>
    <xsl:param name="target"/>
    <xsl:param name="replace"/>
    <xsl:choose>
    <xsl:when test="contains($arg, $target)">
    <xsl:variable name="before" select="substring-before($arg,
    $target)"/>
    <xsl:variable name="after" select="substring-after($arg, $target)"/>
    <xsl:value-of select="$before"/>
    <xsl:value-of select="$replace"/>
    <xsl:call-template name="escape">
    <xsl:with-param name="arg" select="$after"/>
    <xsl:with-param name="target" select="$target"/>
    <xsl:with-param name="replace" select="$replace"/>
    </xsl:call-template>
    </xsl:when>
    <xsl:eek:therwise>
    <xsl:value-of select="$arg"/>
    </xsl:eek:therwise>
    </xsl:choose>
    </xsl:template>
    --
    Unemployed English Java programmer
    http://jroller.com/page/tackline/
     
    Thomas Hawtin, Aug 12, 2005
    #4
  5. Fahd Shariff

    Guest

    Coincidently I have a project that needs to do something similair.
    Might you have a test xml file that goes along with this? I'd give me a
    better idea if it could help me.

    Thanks,
    iksrazal
     
    , Aug 12, 2005
    #5
  6. Fahd Shariff

    Wibble Guest

    Fahd Shariff wrote:
    > Hello,
    >
    > I have a reasonably large xml file which i need to parse and store into
    > an oracle database.
    >
    > I am currently toying with 2 ideas:
    >
    > 1. Parse XML using a SAX parser. Map xml tags to database column names
    > and generate a prepared statement. Execute each one the normal JDBC
    > way.
    >
    > 2. Create an XSLT which converts the XML to a SQL script containing
    > lots of insert statements. Use Runtime.exec to run the script on oracle
    > using sqlplus.
    >
    > The more I think about it, the more I prefer the second option. Would
    > it be faster? I guess the major overhead would be the Runtime.exec
    > call? The first option seems a bit messy...
    >
    > Which would you use and why?
    >

    Oracle has support for XML. See...

    http://www.oracle.com/technology//tech/xml/info/htdocs/otnwp/about_oracle_xml_products.htm

    or

    http://www.orafaq.com/faqxml.htm#XMLTYPE
     
    Wibble, Aug 13, 2005
    #6
  7. Fahd Shariff wrote:

    >
    > The more I think about it, the more I prefer the second option. Would
    > it be faster? I guess the major overhead would be the Runtime.exec
    > call? The first option seems a bit messy...


    Nope, it's the second one which is the messy one ... The first one is
    the normal way, I suppose.

    If the xml scheme changes, you could use xslt to transform back to the
    old format your sax parsing is used to.

    Alternatively you could use a set of xpath queries to pick information
    from the input xml. You could keep the xpath queries in a "dynamic
    place" (database or something) to make this a dynamic solution as well.

    If you need to keep the input xml, you could store it in an Oracle
    XMLTYPE. It's be possible to use xpath embedded in the sql/jdbc-queries
    to retrieve and search, but for performance reasons you may want to
    store certain values in separate columns for indexing as well (or
    prehaps create some functional indexes that will peek into the xml
    structure, if possible)

    I'm not sure how the runtime.exec performs, but xslt transformations are
    surely more resource-hungry than sax parsing. Why exactly do you prefer
    to use method 2?

    --
    jon martin solaas
     
    Jon Martin Solaas, Aug 13, 2005
    #7
  8. Fahd Shariff

    Guest

    , Aug 13, 2005
    #8
  9. Fahd Shariff wrote:
    > Hello,
    >
    > I have a reasonably large xml file which i need to parse and store into
    > an oracle database.
    >
    > I am currently toying with 2 ideas:
    >
    > 1. Parse XML using a SAX parser. Map xml tags to database column names
    > and generate a prepared statement. Execute each one the normal JDBC
    > way.
    >
    > 2. Create an XSLT which converts the XML to a SQL script containing
    > lots of insert statements. Use Runtime.exec to run the script on oracle
    > using sqlplus.
    >
    > The more I think about it, the more I prefer the second option. Would
    > it be faster? I guess the major overhead would be the Runtime.exec
    > call? The first option seems a bit messy...
    >
    > Which would you use and why?
    >


    If you are looking for an Oracle only solution, look at XSU supplied by
    Oracle.

    Ray

    --
    XML is the programmer's duct tape.
     
    Raymond DeCampo, Aug 17, 2005
    #9
  10. Fahd Shariff

    Chris Smith Guest

    Fahd Shariff <> wrote:
    > I have a reasonably large xml file which i need to parse and store into
    > an oracle database.
    >
    > I am currently toying with 2 ideas:
    >
    > 1. Parse XML using a SAX parser. Map xml tags to database column names
    > and generate a prepared statement. Execute each one the normal JDBC
    > way.
    >
    > 2. Create an XSLT which converts the XML to a SQL script containing
    > lots of insert statements. Use Runtime.exec to run the script on oracle
    > using sqlplus.
    >
    > The more I think about it, the more I prefer the second option. Would
    > it be faster?


    As far as speed goes, the first option could be made at least as fast as
    the second, assuming a sane implementation of the parser. The code
    would probably look better, as well.

    --
    www.designacourse.com
    The Easiest Way To Train Anyone... Anywhere.

    Chris Smith - Lead Software Developer/Technical Trainer
    MindIQ Corporation
     
    Chris Smith, Aug 24, 2005
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Curt_C [MVP]
    Replies:
    3
    Views:
    14,359
    jharr100
    Aug 14, 2009
  2. Tampa .NET Koder
    Replies:
    0
    Views:
    527
    Tampa .NET Koder
    Jul 27, 2004
  3. Max
    Replies:
    2
    Views:
    419
  4. Harry Zoroc
    Replies:
    1
    Views:
    995
    Gregory Vaughan
    Jul 12, 2004
  5. =?Utf-8?B?ZGF2aWQ=?=
    Replies:
    2
    Views:
    398
    Steve C. Orr [MCSD, MVP, CSM, ASP Insider]
    Feb 21, 2007
Loading...

Share This Page