wood burning stoves 2.0*
The moose likes XML and Related Technologies and the fly likes inserting data from XML to database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Engineering » XML and Related Technologies
Bookmark "inserting data from XML to database" Watch "inserting data from XML to database" New topic
Author

inserting data from XML to database

sirishree priya
Greenhorn

Joined: Jun 05, 2003
Posts: 4
Hi all,
I have an XML file and want to insert the data of the XML file in the MS SQL database.Now can I do that using parsers if yes then which parser?
Please give me the links where I can get code for this or help.
Thanks in advance
Siri
Balaji Loganathan
author and deputy
Bartender

Joined: Jul 13, 2001
Posts: 3150
how about this two tutorials ?
Using JDBC to insert data from XML into a database and Using JDBC to extract data into XML


Spritle Software Blogs
sateesh arumbaka
Greenhorn

Joined: Nov 22, 2002
Posts: 6
Balaji,

I tried to access the tutorials but its broken.

I too have the same question, what is the best way to push data in to a database from an xml file.

until now I have been using dom parser to parse the xml document and then I put the data in a two dimensional array as name value pairs ,and then create SQL insert into queries from that two dimensional array.

As my XML files have started to become huge, this method is no longer viable as it is taking for ever to load the data in to the two dimensional array.

I am looking out for new and efficient methods of doing this.

One possible solution would be to use XSLTs to generate the SQL insert into queries directly.
or
May be break up the xml files in to smaller pieces and then follow my old procedure...

does any body know of a better way.. please advice..

Thanks in advance
Greg Schultz
Greenhorn

Joined: Oct 08, 2003
Posts: 16
I am currently trying to use Mapforce from www.altova.com. It bascially loads an XML file into java classes that it generates based on a Mapping file that you create by tying XSD elements to Database Columns.

Thus far it is working great, but it is commercial.

Greg
Alexandru Popescu
Ranch Hand

Joined: Jul 12, 2004
Posts: 995
Originally posted by sateesh arumbaka:

As my XML files have started to become huge, this method is no longer viable as it is taking for ever to load the data in to the two dimensional array.


Why do you need this intermediary step? Create a transaction, a preparedstatement and start inserting while parsing. If the parser craches you can rollback the transaction and everything is still oke.


blog - InfoQ.com
sateesh arumbaka
Greenhorn

Joined: Nov 22, 2002
Posts: 6
I think using XSLT to generate the insert into queries would do the job faster.

I just started reading a book on XSLT, and my knowledge wouldnt be enough to do this.

can somebody help me with this.

Say for example I have the following xml

<input>
<row>
    <name>clk1</name>
    <pin>N1</pin>
    <fan_out>1365</fan_out>
</row>
<row>
    <name>clk2</name>
    <pin>N2</pin>
    <fan_out>1</fan_out>
</row>
<row>
    <name>clk3</name>
    <pin>N3</pin>
    <fan_out>47</fan_out>
</row>
</input>


how can I get the out put like the following

insert into tableName (name,pin,fan_out) values('clk1','N1','1365')

insert into tableName (name,pin,fan_out) values('clk2','N2','1')

insert into tableName (name,pin,fan_out) values('clk3','N3','47')
[ July 16, 2004: Message edited by: sateesh arumbaka ]
sateesh arumbaka
Greenhorn

Joined: Nov 22, 2002
Posts: 6
I found this example on
http://www.ibiblio.org/xml/slides/sd2000west/xmlandjava/221.html


<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl utput method="text"/>

<xsl:template match="Row">
INSERT INTO composers (
<xsl:apply-templates select="Field" mode="names"/>
)
VALUES (
<xsl:apply-templates select="Field" mode="values"/>
)
</xsl:template>

<xsl:template match="Field" mode="values">
'<xsl:value-of select="."/>'<xsl:if test="position()!=last()">, </xsl:if>
</xsl:template>

<xsl:template match="Field" mode="names" >
'<xsl:value-of select="@name"/>'<xsl:if test="position()!=last()">, </xsl:if>
</xsl:template>

</xsl:stylesheet>
arusha khan
Greenhorn

Joined: Dec 08, 2004
Posts: 9
I am following the ibm link to insert xml data into the oracle database.

Does anyone have Sax example for doing that instead of DOM?

Thanx
AK
 
GeeCON Prague 2014
 
subject: inserting data from XML to database