Example: XML and XQuery support in Java (JDBC) procedure

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.

Prerequisites

Before working with this Java procedure example you might want to read the following topics:

• Java routines
• Routines
• Building Java routine code

The examples below makes 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))@
 

Procedure

Use the following example as references when making your own Java procedures:

• Table 1
• Table 2

  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
              ...
   }

  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 ;
  }