Read XMLType XML Data Oracle Java JDBC Example

In this tutorial, we will discuss how to read / query XML data from a XMLTYPE column in Oracle in JDBC Java with an example program. We will need quite a few JAR files for the tutorial to work, which we will cover as we move on. The high level steps for this tutorial are provided below:

Read XMLType Column in Oracle through Java JDBC - Example Program - Steps

A brief description of each of these steps is provided below:

• Create XMLType Data: In this step, we will create a test table in Oracle 11g R2 database XMLTYPE column and populate it with XML Data.
• Copy Required JAR Files: In this step, we will identify the required JAR files for the Java JDBC code to work and copy them to the classpath.
• Write Java JDBC Code: We will write the JDBC code in this step, and take you through the segments, step by step.
• Run Code in Database: We will compile and execute the code in this step. 
• Verify Output: Finally, we will check if we were able to query  XML data from the XMLType column and examine the output produced by the Java Program.

We will cover each of these steps in detail as we move on. Let us get started now.

Create XMLType Data:

In this step, we will create a table in Oracle with a XMLTYPE column. Following this, we will execute an INSERT statement against the table and load XML data into the XMLTYPE column. Finally, we will query the table back to see if the XML Data is ready to be read by the Java program. A very simple step, here is the SQL script that you need to use for this step:

CREATE TABLE JDBC_XML
(
TESTFIELD XMLTYPE
)


INSERT INTO JDBC_XML(TESTFIELD) VALUES('<test><set><id>123</id><names>emp</names></set></test>')

COMMIT

SELECT a.TESTFIELD.getstringval() FROM JDBC_XML a

The table and the associated XML data is shown in the screenshot below. We are now ready to move to the next step.

Oracle table with XMLType Data

Copy Required JAR Files

For the example to work, you will have to copy quite a few JAR files to your classpath. You need to use the right ojdbc.jar file depending on your database version. We are running this example against 11g R2 database, so we will use ojdbc6.jar. You will also need xdb.jar to handle XMLTYPE columns. You will need xmlparserv2.jar to handle parser exceptions at runtime. Make sure you have these two files in your classpath.

Java JDBC Code – Read / Query XMLTYPE Column

We are now ready to write the JDBC code to read the XMLTYPE column data. For example purposes, we will read the value of “id” tag. We will also print the root element name of the document. The high level steps involved in reading XMLTYPE column in an Oracle database through a Java JDBC program is captured below:

• Declare connection details
• Use OraclePreparedStatement and write the query to read XMLType column
• Use OracleResultSet object to loop through the results returned by the query
• Use OracleResultSet.getOPAQUE method to read the XMLTYPE column( through XMLType.createXML method) into  oracle.xdb.XMLType object.
• Use XMLType.getDocument() method to read the column data as org.w3c.dom.Document object.
• Parse the Document object and print the tags required in the output.
• Close all connection objects.

The complete Java program to read / parse data from XMLType column of Oracle through JDBC is provided below:

import java.io.*;
import java.sql.*;
import org.w3c.dom.*;
import oracle.xdb.XMLType; 
import oracle.jdbc.*;
        public class jdbcXmltype {  
        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");
                OraclePreparedStatement stmt = (OraclePreparedStatement) conn.prepareStatement("SELECT A.TESTFIELD FROM JDBC_XML A");
                ResultSet rset = stmt.executeQuery(); 
                OracleResultSet orset = (OracleResultSet) rset; 
                Document xml_column=null;
                while (orset.next()) {
                                XMLType poxml = XMLType.createXML(orset.getOPAQUE("TESTFIELD"));
                                //print the full xmltype
                                System.out.println(poxml.getStringVal());                               
                                //below method is deprecated
                                //xml_column = (Document)poxml.getDOM();
                                xml_column = (Document)poxml.getDocument();
                                }
                /* We have only one row in the XMLTYPE column, so the while loop will get executed once */
                /* Let us now try to print the value of id tag */
                
                /* Print the root element */
                System.out.println("Root element: " + xml_column.getDocumentElement().getNodeName());
                /* Print ID tag. You should try and use XPath approaches rather */
                NodeList nodeList = xml_column.getElementsByTagName("set");
                /* Loop through the "set" node */
                for (int i = 0; i < nodeList.getLength(); i++) { 
                Node node = nodeList.item(i);                
                Element element = (Element) node;
                NodeList nodeList_1 = element.getElementsByTagName("id").item(0).getChildNodes();
                Node node_2 = nodeList_1.item(0);
                System.out.println(node_2.getNodeValue());                
                }                
                /* Close all DB related objects */
                orset.close();
                stmt.close(); 
                conn.close();
        }
}

Run Code Against Database / Verify Output

For the code provided above, here is a screen dump of the output. We are able to read a XMLTYPE column and print the value against a tag. !!

java -classpath .;xdb.jar;ojdbc6.jar;xmlparserv2.jar jdbcXmltype

<test><set><id>123</id><names>emp</names></set></test>

Root element: test

123

That completes our tutorial on reading XMLType column values from a table in Oracle, through Java JDBC program. If you have any questions on the content of this blog, you can post it in the comments section. Here are some references that will be useful for further reading:


References:

• API Doc – org.w3c.dom.Document – explains how to work with Document objects.
• API Doc – OracleResultSet - oracle.jdbc.driver.OracleResultSet – getOPAQUE method signature
• API Doc – XMLType - oracle.xdb.XMLType – Methods to deal with XMLType data
• Oracle Documentation – Handling XMLType Data in Java