Once the basics of Java™ procedures, programming in Java using the JDBC application programming interface (API), and XQuery are understood, you can start creating and using Java procedures that query XML data.
The following examples make use of a table named xmlDataTable defined and containing data as follows:
CREATE TABLE xmlDataTable
(
num INTEGER,
xdata XML
)@
INSERT INTO xmlDataTable VALUES
(1, XMLPARSE(DOCUMENT '<doc>
<type>car</type>
<make>Pontiac</make>
<model>Sunfire</model>
</doc>' PRESERVE WHITESPACE)),
(2, XMLPARSE(DOCUMENT '<doc>
<type>car</type>
<make>Mazda</make>
<model>Miata</model>
</doc>' PRESERVE WHITESPACE)),
(3, XMLPARSE(DOCUMENT '<doc>
<type>person</type>
<name>Mary</name>
<town>Vancouver</town>
<street>Waterside</street>
</doc>' PRESERVE WHITESPACE)),
(4, XMLPARSE(DOCUMENT '<doc>
<type>person</type>
<name>Mark</name>
<town>Edmonton</town>
<street>Oak</street>
</doc>' PRESERVE WHITESPACE)),
(5, XMLPARSE(DOCUMENT '<doc>
<type>animal</type>
<name>dog</name>
</doc>' PRESERVE WHITESPACE)),
(6, NULL),
(7, XMLPARSE(DOCUMENT '<doc>
<type>car</type>
<make>Ford</make>
<model>Taurus</model>
</doc>' PRESERVE WHITESPACE)),
(8, XMLPARSE(DOCUMENT '<doc>
<type>person</type>
<name>Kim</name>
<town>Toronto</town>
<street>Elm</street>
</doc>' PRESERVE WHITESPACE)),
(9, XMLPARSE(DOCUMENT '<doc>
<type>person</type>
<name>Bob</name>
<town>Toronto</town>
<street>Oak</street>
</doc>' PRESERVE WHITESPACE)),
(10, XMLPARSE(DOCUMENT '<doc>
<type>animal</type>
<name>bird</name>
</doc>' PRESERVE WHITESPACE))@
The example shows a Java procedure implementation. The example consists of two parts: the CREATE PROCEDURE statement and the external Java code implementation of the procedure from which the associated Java class can be built.
using System;
import java.lang.*;
import java.io.*;
import java.sql.*;
import java.util.*;
import com.ibm.db2.jcc.DB2Xml;
public class stpclass
{ ...
// Java procedure implementations
...
}
The Java class file imports are indicated at the top of the file. The com.ibm.db2.jcc.DB2Xml import is required if any of the procedures in the file contain parameters or variables of type XML will be used.
It is important to note the name of the class file and JAR name that contains a given procedure implementation. These names are important, because the EXTERNAL clause of the CREATE PROCEDURE statement for each procedure must specify this information so that DB2® database systems can locate the class at run time.
CREATE PROCEDURE xmlProc1 ( IN inNUM INTEGER,
IN inXML XML as CLOB (1K),
OUT out1XML XML as CLOB (1K),
OUT out2XML XML as CLOB (1K)
)
DYNAMIC RESULT SETS 0
DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
MODIFIES SQL DATA
FENCED
THREADSAFE
DYNAMIC RESULT SETS 0
PROGRAM TYPE SUB
NO DBINFO
EXTERNAL NAME 'myJar:stpclass.xmlProc1'@
//*************************************************************************
// Stored Procedure: XMLPROC1
//
// Purpose: Inserts XML data into XML column; queries and returns XML data
//
// Parameters:
//
// IN: inNum -- the sequence of XML data to be insert in xmldata table
// inXML -- XML data to be inserted
// OUT: out1XML -- XML data to be returned
// out2XML -- XML data to be returned
//
//*************************************************************************
public void xmlProc1(int inNum,
DB2Xml inXML ,
DB2Xml[] out1XML,
DB2Xml[] out2XML
)
throws Exception
{
Connection con = DriverManager.getConnection("jdbc:default:connection");
// Insert data including the XML parameter value into a table
String query = "INSERT INTO xmlDataTable (num, inXML ) VALUES ( ?, ? )" ;
String xmlString = inXML.getDB2String() ;
stmt = con.prepareStatement(query);
stmt.setInt(1, inNum);
stmt.setString (2, xmlString );
stmt.executeUpdate();
stmt.close();
// Query and retrieve a single XML value from a table using SQL
query = "SELECT xdata from xmlDataTable WHERE num = ? " ;
stmt = con.prepareStatement(query);
stmt.setInt(1, inNum);
ResultSet rs = stmt.executeQuery();
if ( rs.next() )
{ out1Xml[0] = (DB2Xml) rs.getObject(1); }
rs.close() ;
stmt.close();
// Query and retrieve a single XML value from a table using XQuery
query = "XQUERY for $x in db2-fn:xmlcolumn(\"xmlDataTable.xdata\")/doc
where $x/make = \'Mazda\'
return <carInfo>{$x/make}{$x/model}</carInfo>";
stmt = con.createStatement();
rs = stmt.executeQuery( query );
if ( rs.next() )
{ out2Xml[0] = (DB2Xml) rs.getObject(1) ; }
rs.close();
stmt.close();
con.close();
return ;
}