Insert XML to XMLType Column - Java JDBC Example

In this example, we will explore the various options that are available to a Java developer, to insert XML data into an Oracle database table, on an XMLType column, with relevant JDBC examples. There are different approaches / methods available to insert XML data, these can be classified broadly as per the diagram below:

Different Options to Insert XMLType Data to Oracle through Java JDBC Program

Methods to Insert XMLType Data:

As per the diagram above, we will cover three approaches to insert XMLType data into a table column, through a Java program. You can either insert the XML data as a string, or as a Clob or as XMLType object.  XMLType object is a new approach, as you will understand when we describe these methods in detail.

Irrespective, of the methods you use, you need to perform some steps in your JDBC program as common steps. These steps are captured below:

Steps to Insert XMLType Data to Oracle From Java Program

We will cover all common steps once, and the step with a green glow (process XML insert, thrice, depending on various methods that is available). Let us get started.

Declare Connection Details

To insert XMLType data, you need to identify a target database where the table resides. You should also find a suitable driver through which you can connect to the database from Java. You use a Connection object java.sql.Connection to get a connection to the target database. In our case, we will use OracleDriver as the database is of type Oracle. Here is a code snippet through which you can connect to the database, to insert your data.

                Class.forName ("oracle.jdbc.OracleDriver"); 
                Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@//localhost:1521/orcl", "hr", "hr");

Create Table / Prepare Insert Statement

You should now have an active schema (you identified this earlier) and a table that has a XMLType column into which you can insert data. We will use the same table that we created in our read XMLType through Java/ JDBC tutorial. So, grab the DDL from that tutorial for the example to work.

Ok, so you got a table and a column of type XMLType. What next? Depending on the approach you want to take (1 for String, 2 for Clob and 3 for XMLType) [You will come to know why we have 1, 2 and 3 later] you will have to prepare different insert statements. I assume you know a bit of SQL here. So, we will write a small method in Java that will accept the type of insert you want to do, and return a suitable SQL INSERT statement back to you. The code segment is shown below:

        public String ReturnSQLStatement(String type) throws Exception {
                int requiredType=Integer.parseInt(type);
                String outputSQL;
                switch (requiredType) {
                case 1: outputSQL = "INSERT INTO JDBC_XML VALUES (XMLType(?))";//String
                break;
                case 2: outputSQL = "INSERT INTO JDBC_XML VALUES (XMLType(?))";//Clob
                break;
                case 3: outputSQL = "INSERT INTO JDBC_XML VALUES (?)"; //XMLTYPE
                break;
                default:outputSQL = "Invalid";
                break;
                }
                if (outputSQL.equals("Invalid")) {
                        throw new Exception("you entered an invalid input");
                };
                return outputSQL;
        }

As you can infer from the code, String and Clob approach to insert XML data, share same statement. XMLTYPE based insert in Java, takes a different statement.

Set Input XML Data

This is a very simple step. In this step, you identify the XML you want to insert into XMLType column as a string in Java. We will define a simple XML string for our example. We will parse the same string as CLOB / XMLType and insert them suitably.

                String insertString = "<test><set><id>1</id><names>EMP</names></set></test>";

Insert XML Data as String to XMLType Column

Let us take the easiest of the approaches first. In this method, you define the input XML Data as type string and use setString method in oracle.jdbc.OraclePreparedStatment class, to bind the string directly to the insert statement. We will provide the code later for this.  But this approach is really simple, and you can use this when your input XML is very small and can be held in a String easily.

Insert XML Data as Clob to XMLType Column

When your input data is big, you may want to get the input as a CLOB which you can insert directly to XMLType column. Clobs are defined using java.sql.Clob class. You cannot directly instantiate an object for this class as this is an interface class.  So, we first create an object of type clob, by using createClob method in Connection class. Then, you can invoke setString method in the Clob class to convert the XML String to Clob. Finally, you can attach the Clob XML to the OraclePreparedStatement class by using setClob method. In this way, you bind a clob XML data to your statement. Now, when you execute this statement, the clob data will be inserted directly to XMLType column.

Insert XML Data as XMLType

The new approach to  insert XMLType is to use oracle.xdb.XMLType class, available in xdb.jar file. This is a recommended approach to insert XMLType data via Java JDBC into Oracle.  To use this method, you invoke the static method createXML and pass the Connection object and the input XML string to it. This method returns an XMLType object back, which you can bind to your SQL statement, using setObject method.

The code snippet for all the three approaches is given below.

                switch (enteredInput) {
                case 1: stmt.setString(1,insertString); // insert XMLType As String
                break;
                case 2: myClob=conn.createClob();                       
                position=myClob.setString(1,insertString); //insert XMLType As Clob
                stmt.setClob(1,myClob);
                break;
                case 3: XMLType myXML = XMLType.createXML(conn, insertString);//insert XMLType Direct
                stmt.setObject(1,myXML);
                break;
                }

Execute Insert / Close Connection

Finally, you execute the insert statement using the execute method. This will insert the XML data to the XMLType column. And you close all connection objects as a good programmer.

Complete Java JDBC Program – Insert XMLType to Oracle

The complete Java program to insert XMLType data to Oracle through JDBC is provided below. This program requires you to specify what type of approach you want to use in the command line i.e. 1- string, 2 – Clob or 3-Xmltype. I have tested this code on all the approaches and it works fine. 

/* This program explains different approaches to insert XMLType Data into Oracle Table */
/* You can insert XMLType as String, Clob or directly as XMLType */
import java.io.*;
import java.sql.*;
import oracle.xdb.XMLType; 
import javax.sql.rowset.serial.SerialClob;
import oracle.jdbc.*;
public class insertXMLType {  
        public static void main(String[] args) throws Exception{
                Class.forName ("oracle.jdbc.OracleDriver"); 
                Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@//localhost:1521/orcl", "hr", "hr");
                insertXMLType invoker= new insertXMLType();
                String sql_statement=invoker.ReturnSQLStatement(args[0]);
                OraclePreparedStatement stmt = (OraclePreparedStatement) conn.prepareStatement(sql_statement);
                String insertString = "<test><set><id>1</id><names>EMP</names></set></test>";
                int enteredInput=Integer.parseInt(args[0]); // supplied at runtime
                int position;
                java.sql.Clob myClob;
                switch (enteredInput) {
                case 1: stmt.setString(1,insertString); // insert XMLType As String
                break;
                case 2: myClob=conn.createClob();                       
                position=myClob.setString(1,insertString); //insert XMLType As Clob
                stmt.setClob(1,myClob);
                break;
                case 3: XMLType myXML = XMLType.createXML(conn, insertString);//insert XMLType Direct
                stmt.setObject(1,myXML);
                break;
                }
                stmt.execute();
                /* Close all DB related objects */               
                stmt.close(); 
                conn.close();
        }    
        public String ReturnSQLStatement(String type) throws Exception {
                int requiredType=Integer.parseInt(type);
                String outputSQL;
                switch (requiredType) {
                case 1: outputSQL = "INSERT INTO JDBC_XML VALUES (XMLType(?))";//String
                break;
                case 2: outputSQL = "INSERT INTO JDBC_XML VALUES (XMLType(?))";//Clob
                break;
                case 3: outputSQL = "INSERT INTO JDBC_XML VALUES (?)"; //XMLTYPE
                break;
                default:outputSQL = "Invalid";
                break;
                }
                if (outputSQL.equals("Invalid")) {
                        throw new Exception("you entered an invalid input");
                };
                return outputSQL;
        }
}

You will need ojdbc6.jar, xdb.jar and xmlparserv2.jar to work this example for all the approaches properly. If you are missing xmlparserv2.jar, you will get a runtime exception as shown below:

Exception in thread "main" java.lang.NoClassDefFoundError: oracle/xml/parser/v2/XMLParseException
        at insertXMLType.main(insertXMLType.java:26)
Caused by: java.lang.ClassNotFoundException: oracle.xml.parser.v2.XMLParseException
        at java.net.URLClassLoader$1.run(Unknown Source)
        at java.net.URLClassLoader$1.run(Unknown Source)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(Unknown Source)
        at java.lang.ClassLoader.loadClass(Unknown Source)
        at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
        at java.lang.ClassLoader.loadClass(Unknown Source)
        ... 1 more

Try this code on your XML data. If you are getting a different exception, post it in the comments section, we will have a look.