Insert XML data into Database.

The simplest way to read an XML and insert them into the database is to use an XML parser and then write them into the database through insert statements or through hibernate mapping. But that would be time consuming while dealing with huge XML files.

In this topic, lets explore a more efficient way of doing it by passing an XML into a Stored Procedure.


Java, XML, XSL, Oracle Stored Procedure.

Step 1: Execute the Stored Procedure

–DBMS_XMLSave is used before Oracle 10g. DBMS_XMLSTORE is the new PL/SQL package was introduced in Oracle Database 10g
–This package knows how to insert the data from a Canonical XML into an Oracle TABLE.

create or replace
updCtx DBMS_XMLSave.ctxType;
updCtx := DBMS_XMLSave.newContext(tableName);
DBMS_XMLSave.setBatchSize(updCtx, records);
ROWS := DBMS_XMLSave.updateXML(updCtx,xmlDoc);

NOTE: To INSERT, use  DBMS_XMLSave.insertXML remove DBMS_XMLSave.setKeyColumn(…);

Step 2: Transform your XML file to a canonical XML format and then to a String Object. outWriter = new;
javax.xml.transform.Result result = new javax.xml.transform.Stream.StreamResult(outWriter);
javax.xml.transform.TransformerFactory factory = javax.xml.transform.TransformerFactory.newInstance();
Source xsl = new StreamSource(“your xsl File”);
Transformer transformer = factory.newTransformer(xsl);
transformer.setOutputProperty(OutputKeys.INDENT, “yes”);
transformer.transform(xml, result);
String xmlString = outWriter.toString();

//The canonical XML format would look like this, for example. The ROWSET & ROW tags are mandatory and
//the other tags represent the field name of the target table.


Step 3: Insert XML into the database via Stored procedure.

//java.sql.Connection connection = <getYourConnection()>

CallableStatement cstmt = connection.prepareCall(“{call UPDATEPROC(?,?,?,?)}”);
cstmt.setString(1, xmlString);
cstmt.setString(2, <Target Table Name>);
Integer records = getNoOfRecords(xmlString);
cstmt.setInt(3, records);
cstmt.setString(4, “<your Key column>”); // Only for Update

//Function to find the number of records in the Canonical XML

private Integer getNoOfRecords(String xmlString) throws Exception{
javax.xml.parsers.DocumentBuilder builder = javax.xml.parsers.DocumentBuilderFactory.newInstance().newDocumentBuilder();
org.w3c.dom.Document document = builder.parse(new InputSource(new StringReader(xmlString)));
org.w3c.dom.Element docElement = document.getDocumentElement();
org.w3c.dom.NodeList list = docElement.getElementsByTagName(“ROW”);
return list.getLength();


Build and Test a Java Web Service.


Let’s quickly write a Java Web Service, deploy and test.


OS: Windows
IDE: Eclipse Galileo
Java: Jdk 1.6
Server : JBoss

Step1: Set up your environment – Install Softwares & Configure

1. Download Jdk from the link above and extract. Set your PATH & CLASSPATH variables.

2. Download Eclipse from the link above and extract and copy the folder ‘eclipse to C:\

From within eclipse, Install new software (form help menu) and point to to install JBoss tools for eclipse. This will install JBoss 5.1 Runtime Environment server for eclipse.

3. Download JBoss Server from the link above and extract to C:\jboss-5.1.0.GA

Set JBOSS_HOME environment variable to point to installed C:\jboss-5.1.0.GA folder

4. Download and Install Metro Web Services

  1. Download “ and extract it.
  2. Copy to
  3. You need to have Apache ant installed in your system for next steps to work. if not please download latest ant from and install. Set PATH to <ANT-HOME>/bin.
  4. Edit file and change jboss510.home=C:\\jboss-5.1.0.GA
  5. comment out other jboss501.home etc in file.
  6. Form command line, go to the directory where you have and run “ant deploy-jboss510″ to patch your JBoss with Metro web services.

5. Start your JBoss from Eclipse

  1. Open Eclipse | Open Java EE perspective | Click on Servers Tab | Rt. Click | New | Server | Select JBos 5.x | Finish. Server will be added now.
  2. Double click on the server and change the timeout to a higher number 200 or so.
  3. Rt. Click on the server and Start.
  4. Check http://localhost:8080/ to see the server running.

Step2: Write your Web Service

  1. Eclipse | New | Dynamic Web Project
  2. Name your project as MyWebServices & Select JBoss as your ‘Target Runtime’ | Finish
  3. This will create the following folders into your workspace
  4. Create a package com.test withing src and create your as follows. Since you are using the JBoss Runtime in Eclipse, you don’t need to import any Jars.
  5. package com.test;
    import javax.jws.WebMethod;
    import javax.jws.WebParam;
    import javax.jws.WebService;
    import javax.jws.soap.SOAPBinding;
    @WebService(name = “TestWs”)
    style = SOAPBinding.Style.DOCUMENT,
    use = SOAPBinding.Use.LITERAL,
    parameterStyle = SOAPBinding.ParameterStyle.WRAPPED
    public class TestWs
    public String greet( @WebParam(name = “name”)
    String name )
    return “Hello” + name;

  6. Project | Build automatically and see your class file in build folder.
  7. Now open WebContent\WEB-INF\web.xml and add the following

Step3: Deploy your Web Service

Your Server is already Started.

Go to the Servers tab and Rt. click | Add and Remove | Select your Project ‘MyWebServices’ and move it to the ‘Configured’ box | Finish

This step will deploy your webservice and you will get a message in your console as follows

Buildfile: C:\eclipse\plugins\org.eclipse.jst.server.generic.jboss_1.6.1.v200904151730\buildfiles\jboss323.xml
[jar] Building jar: C:\TWorkspace\.metadata\.plugins\org.eclipse.wst.server.core\tmp0\MyWebServices.war
[move] Moving 1 file to C:\jboss-5.1.0.GA\server\default\deploy

To view the WSDL follow the link http://localhost:8080/MyWebServices/TestWs?wsdl

Great! your Web Service is up and running.

Step4: Set up your WebService Client WorkSpace & Write Your Web Service Client.

  1. Create a Java Project to create your Client – Eclipse | New | Project | Java Project. Name your Project as ‘TestWsClient’
  2. Create the WebService Stubs using JBoss tool wsconsume.
    Go to C:\jboss-5.1.0.GA\bin
    Run wsconsume -k http://localhost:8080/MyWebServices/TestWs?wsdl
  3. This will generate the following Java files and their class files in C:\jboss-5.1.0.GA\bin\output\com\test directory.
  4. Create a package in your ‘TestWsClient’ project and copy these java files there.
  5. Create a package src.client and create your Client Class as follows.
    package client;

    import com.test.TestWs;
    import com.test.TestWsService;

    public class TestWsClient {
    @WebServiceRef (wsdlLocation=”http://localhost:8080/MyWebServices/TestWs?wsdl&#8221;)
    static TestWsService service;

    public static void main(String[] args) throws Exception {
    service = new TestWsService();
    TestWs test = service.getTestWsPort();
    System.out.println(test.greet(” Bob! “));

Step6: Test Your WebService

Build your project (set Project | Build Automatically)

Right Click your, Run As Java Application

Dang! it gets your service named ‘greet’ and says ‘Hello Bob!’