Oracle XMLTYPE - java-sql-SQLXML - Example

In this post, we will see the benefits of using java.sql.SQLXML to write XMLTYPE data into an Oracle 11g database. We have seen scenarios where you can treat XML as a string and insert into XMLTYPE column directly via smart SQLs. Some of them were covered earlier as per below;

Read XMLTYPE as Clob in Java
Insert data into XMLTYPE Column using Java
Simple read - XMLTYPE data in Java

New JDBC drivers have introduced a mapping interface that support SQL / XML data types. This is defined in java.sql.SQLXML. Through this interface it will be very easy and simpler to handle XML data and manage them in Oracle database. You don't have to manage XML data via strings any more. So, in this tutorial we will discuss how to manage XML data from XMLTYPE column in Oracle using SQLXML based approach.

Insert XML Data from File using SQLXML / Java / JDBC

import java.sql.*;
import java.util.Properties;
import oracle.jdbc.pool.OracleDataSource;
import org.apache.commons.io.IOUtils;
import java.io.*;
 public class InsertXML
 { 
  public static void main(String[] args) throws Exception
  {  
          /* Step -1 : Fetch the XML file on disk in FileInputStream */
          FileInputStream fis = new FileInputStream("test.xml");  
         /* Step-2: Convert the input file to string */
         String xml = IOUtils.toString(fis);  
         /* Step-3: Setup Oracle Data Source */
         OracleDataSource ods = new OracleDataSource();
         ods.setURL("jdbc:oracle:thin:@//localhost:1521/ORCL");
         ods.setUser("hr");
         ods.setPassword("hr");
         Connection jdbc_connection = ods.getConnection();
         /* Step-4: You have to set the below property to false, otherwise the return type for createSQLXML would not be 
         an object of type SQLXML */
         System.setProperty("oracle.jdbc.getObjectReturnsXMLType", "false");
         /* Step -5: Insert XML data into table - prepare statement */
         PreparedStatement psStatement = jdbc_connection.prepareStatement("insert into x values (?)");     
         /* Step-6: Create SQLXML object */
         SQLXML x = jdbc_connection.createSQLXML();
         /* Step -7: Set input XML from File */
         x.setString(xml);
         /* Step-8: Bind input to the insert statement and execute */
         psStatement.setSQLXML(1, x);
         psStatement.execute();
         /* Step -9: Release object and close connections */
         x.free();     
         psStatement.close();
         jdbc_connection.close();
  }
}

Here are a few common exceptions that you can get while running / compiling this code and how to resolve them;

java.sql.SQLException: Unsupported feature

java.sql.SQLException: Unsupported feature
        at oracle.jdbc.driver.PhysicalConnection.createSQLXML(PhysicalConnection
.java:9408)

The above error comes when you use old version of JAR files - ojdbc6.jar. Make sure you have a latest copy of them in your classpath.

oracle.xdb.XMLType cannot be cast to java.sql.SQLXML

Exception in thread "main" java.lang.ClassCastException: oracle.xdb.XMLType cann
ot be cast to java.sql.SQLXML
        at oracle.jdbc.driver.PhysicalConnection.createSQLXML(PhysicalConnection
.java:9750)
        at SQLXMLTest.main(SQLXMLTest.java:34)

I got around this error through Step-4 of the program above. You would want to add it if you get a similar issue.

XDB JAR File

If you miss this JAR file in your classpath, you will get the following exception

Exception in thread "main" java.lang.NoClassDefFoundError: oracle/xdb/XMLType
        at oracle.jdbc.driver.PhysicalConnection.createSQLXML(PhysicalConnection
.java:9750)       
Caused by: java.lang.ClassNotFoundException: oracle.xdb.XMLType
        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)
        ... 2 more

XML Parser JAR File

If you miss the JAR file xmlparserv2.jar in your classpath, you will the exception as shown below

Exception in thread "main" java.lang.NoClassDefFoundError: oracle/xml/parser/v2/
XMLParseException
        at oracle.jdbc.driver.PhysicalConnection.createSQLXML(PhysicalConnection
.java:9750)
        
Caused by: java.lang.ClassNotFoundException: oracle.xml.parser.v2.XMLParseExcept
ion
        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)
        ... 2 more

java.sql.SQLException: ORA-31011: XML parsing failed


ORA-19202: Error occurred in XML processing

LPX-00253: missing required version number in XML declaration

Error at line 1
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.
java:208)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedSta
tement.java:1046)

        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme
nt.java:1336)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePrep
aredStatement.java:3613)
        at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStat
ement.java:3714)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePrepa
redStatementWrapper.java:1378)

Finally, if the target table does not exist you get an ORA exception as shown below

java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.
java:208)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedSta
tement.java:1046)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme
nt.java:1336)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePrep
aredStatement.java:3613)
        at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStat
ement.java:3714)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePrepa
redStatementWrapper.java:1378)
        at SQLXMLTest.main(SQLXMLTest.java:37)

You may also get similar exceptions if your input XML is not well formed, so make sure it is well formed before executing the code. See below for an example

java.sql.SQLException: ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00253: missing required version number in XML declaration
Error at line 1

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.
java:208)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedSta
tement.java:1046)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme
nt.java:1336)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePrep
aredStatement.java:3613)
        at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStat
ement.java:3714)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePrepa
redStatementWrapper.java:1378)