• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Devaka Cooray
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Knute Snortum
  • Bear Bibeault
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Piet Souris
  • Ganesh Patekar
Bartenders:
  • Frits Walraven
  • Carey Brown
  • Tim Holloway

inserting data from XML to database

 
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
 
author and deputy
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
 
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
 
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
 
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>
 
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
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!