Store XML into a database

[Fahd Shariff]

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?

 

[Daniel Dyer]

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.

 

[rpitre]

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();

 

[Thomas Hawtin]

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>

 

[iksrazal]

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

 

[Wibble]

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

 

[Jon Martin Solaas]

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?

 

[iksrazal]

So does Hibernate.

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

HTH,
iksrazal

 

[Raymond DeCampo]

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

 

[Chris Smith]

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