how to read data from XML file & store it in database(MySql)

[kiranrredy]

Hi,

I need to get the data from XML file and store it in MySql database.
How can i do it,could you please tell me.



Regards,
KiranRredy

[prakash ravindran]

hi buddy,

ref this page u ll find solution for this :

http://xml.apache.org/xerces-c/


regards

Prakash Ravindran

[mharrison]

What techniques have you tried already? With java, there are 2 main ways of reading XML documents:

1. DOM - Document Object Model interface. This loads the XML document into main memory. With the Document Object Model, you can build documents, navigate their structure, and add, modify, or delete elements and content. Basically the DOM specifies an abstract datatype for XML logical structure. In Java the API for this is known as JAXP

http://java.sun.com/j2se/1.5.0/docs/api/org/w3c/dom/package-summary.html

An example of reading in a doc could be:

public void readDoc() {
  try {
   
   DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
   DocumentBuilder builder = factory.newDocumentBuilder();
   doc = builder.parse( new File("myXMLFile.xml") );
   
  }
  catch (FactoryConfigurationError fce){
    System.err.println("Could not create DocumentBuilderFactory");
  }
  catch (ParserConfigurationException pce) { 
    System.out.println("Could not locate a JAXP parser"); 
  }
  catch (SAXException se) {
    System.out.println("XML file is not well-formed.");
  }
  catch (IOException ioe) { 
    System.out.println(
    "Due to an IOException, the parser could not read the XML file"
    ); 
  }
}

doc is the object which holds the xml document in.

obviously you'd need to import all relevant packages etc.

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.FactoryConfigurationError;
import javax.xml.parsers.ParserConfigurationException;

import org.xml.sax.SAXException;
import org.xml.sax.SAXParseException;

import java.io.File;
import java.io.IOException;

import org.w3c.dom.*;

2. The second way in java is to use the SAXP API, which doesn't load the XML file into memory. This is quicker than JAXP, and is useful when you know the structure of the xml file isn't going to change etc.

http://java.sun.com/j2se/1.5.0/docs/api/javax/xml/parsers/SAXParser.html

Finally, to send it to mySQL, you need to open a connection String to the mySQL database, and then run whatever queries you want. You can use a system ODBC driver, or Sun's own JDBC driver if you haven't got the first one.

This isn't comprehensive, but should give you enough to start.....

mharrison

[robbinma]

If you are familiar with SQL then you could try using Stels-XML JDBC driver and hibernate together.
I have used the combination successfully on a project.
Stels-XML is a commercial product but it allows you to read an XML file using SQL commands.
Hibernate takes Java objects and writes them to the database by generating the SQL for you.

Links:
Stels Stels-XML
Hibernate hibernate

As with the other solutions you'll also need a JDBC driver.
JDBC driver for MySQL

Best of luck.