Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > Store XML into a database

Reply
Thread Tools

Store XML into a database

 
 
Fahd Shariff
Guest
Posts: n/a
 
      08-12-2005
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

 
Reply With Quote
 
 
 
 
Daniel Dyer
Guest
Posts: n/a
 
      08-12-2005
On Fri, 12 Aug 2005 16:53:38 +0100, Fahd Shariff <(E-Mail Removed)>
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
 
Reply With Quote
 
 
 
 
rpitre
Guest
Posts: n/a
 
      08-12-2005
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[i]);
pstmt.addBatch();
}
int[] updateCounts = pstmt.executeBatch();

 
Reply With Quote
 
Thomas Hawtin
Guest
Posts: n/a
 
      08-12-2005
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">
<xslaram 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">
<xslaram name="arg"/>
<xslaram name="target"/>
<xslaram 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>
<xsltherwise>
<xsl:value-of select="$arg"/>
</xsltherwise>
</xsl:choose>
</xsl:template>
--
Unemployed English Java programmer
http://jroller.com/page/tackline/
 
Reply With Quote
 
iksrazal@terra.com.br
Guest
Posts: n/a
 
      08-12-2005
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

 
Reply With Quote
 
Wibble
Guest
Posts: n/a
 
      08-13-2005
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//te...l_products.htm

or

http://www.orafaq.com/faqxml.htm#XMLTYPE
 
Reply With Quote
 
Jon Martin Solaas
Guest
Posts: n/a
 
      08-13-2005
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
 
Reply With Quote
 
iksrazal@terra.com.br
Guest
Posts: n/a
 
      08-13-2005
So does Hibernate.

http://www.devx.com/Java/Article/27896

HTH,
iksrazal

 
Reply With Quote
 
Raymond DeCampo
Guest
Posts: n/a
 
      08-17-2005
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.
 
Reply With Quote
 
Chris Smith
Guest
Posts: n/a
 
      08-24-2005
Fahd Shariff <(E-Mail Removed)> 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
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Database Database Database Database scott93727@gmail.com Computer Information 0 09-27-2012 02:43 AM
DataBase DataBase DataBase DataBase scott93727@gmail.com Computer Information 0 09-26-2012 09:40 AM
Re: How to store picture (*.jpg, *.bmp, *.gif) into sql database ? Curt_C [MVP] ASP .Net 3 08-14-2009 01:33 AM
Re: How to store picture (*.jpg, *.bmp, *.gif) into sql database ? Tampa .NET Koder ASP .Net 0 07-27-2004 03:54 AM
Tools to extract data from SQL database and convert it into XML & insert XML data into SQL databases Harry Zoroc XML 1 07-12-2004 10:10 PM



Advertisments