Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

inserting data from XML to database

 
sirishree priya
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3150
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
how about this two tutorials ?
Using JDBC to insert data from XML into a database and Using JDBC to extract data into XML
 
sateesh arumbaka
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 995
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
sateesh arumbaka
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic