13 Java DOM API for XMLType

Example 13-5 Updating XMLType Data Using SQL UPDATE with Constructor XMLType

OraclePreparedStatement stmt = 
    (OraclePreparedStatement) conn.prepareStatement(
                                "UPDATE po_xml_tab SET poDoc = XMLType(?)");
// the second argument is a string..
String poString = "<PO><PONO>200</PONO><PNAME>PO_2</PNAME></PO>";
// now bind the string..
stmt.setString(1,poString);
stmt.execute();

Example 13-6 Updating XMLType Data Using SQL UPDATE with setObject()

import oracle.xdb.XMLType;
...
OraclePreparedStatement stmt = 
  (OraclePreparedStatement) conn.prepareStatement(
                              "UPDATE po_xml_tab SET poDoc = ?");
// the second argument is a string
String poString = "<PO><PONO>200</PONO><PNAME>PO_2</PNAME></PO>";
XMLType poXML = XMLType.createXML(conn, poString);
// now bind the string..
stmt.setObject(1,poXML);
stmt.execute();

Example 13-7 Retrieving Metadata about XMLType Data Using JDBC

import oracle.sql.*;
import oracle.jdbc.*;
...
OraclePreparedStatement stmt = 
    (OraclePreparedStatement) conn.prepareStatement(
        "SELECT poDoc FROM po_xml_tab");
OracleResultSet rset = (OracleResultSet)stmt.executeQuery();
// Get the resultset metadata
OracleResultSetMetaData mdata = 
        (OracleResultSetMetaData)rset.getMetaData();
// Describe the column = the column type comes out as OPAQUE
// and column type name comes out as XMLTYPE
if (mdata.getColumnType(1) == OracleTypes.OPAQUE && 
    mdata.getColumnTypeName(1).compareTo("SYS.XMLTYPE") == 0)
{
   // It is an XMLtype instance
}

Example 13-8 Updating an Element in an XMLType Column Using JDBC

-- Create po_xml_hist table to store old PurchaseOrders
CREATE TABLE po_xml_hist (xpo XMLType);
/* NOTE: You must have xmlparserv2.jar and xdb.jar in CLASSPATH */
import java.sql.*;
import java.io.*;
import oracle.xml.parser.v2.*;
import org.xml.sax.*;
import org.w3c.dom.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;
import oracle.xdb.XMLType;
public class tkxmtpje
{
  static String conStr = "jdbc:oracle:oci8:@";
  static String user = "QUINE";
  static String pass = "CURRY";
  static String qryStr =
  "SELECT x.poDoc from po_xml_tab x " +
  "WHERE XMLCast(XMLQuery('/PO/PONO/text()'" +
  " PASSING x.poDoc RETURNING CONTENT)" +
  " AS NUMBER)" +
  " = 200";
  static String updateXML(String xmlTypeStr)
  Java DOM API for XMLType
  Beta Draft Java DOM API for XMLType 15-7
  {
    System.out.println("\n===============================");
    System.out.println("xmlType.getStringVal():");
    System.out.println(xmlTypeStr);
    System.out.println("===============================");
    String outXML = null;
    try
    {
      DOMParser parser = new DOMParser();
      parser.setValidationMode(false);
      parser.setPreserveWhitespace (true);
      parser.parse(new StringReader(xmlTypeStr));
      System.out.println("xmlType.getStringVal(): xml String is well-formed");
      XMLDocument doc = parser.getDocument();
      NodeList nl = doc.getElementsByTagName("DISCOUNT");
      for(int i=0;i<nl.getLength();i++)
      {
        XMLElement discount = (XMLElement)nl.item(i);
        XMLNode textNode = (XMLNode)discount.getFirstChild();
        textNode.setNodeValue("10");
      }
      StringWriter sw = new StringWriter();
      doc.print(new PrintWriter(sw));
      outXML = sw.toString();
      //print modified xml
      System.out.println("\n===============================");
      System.out.println("Updated PurchaseOrder:");
      System.out.println(outXML);
      System.out.println("===============================");
    }
    catch (Exception e)
    {
      e.printStackTrace(System.out);
    }
    return outXML;
  }
}
public static void main(String args[]) throws Exception
{
  try
  {
    System.out.println("qryStr=" + qryStr);
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    Connection conn =
    DriverManager.getConnection("jdbc:oracle:oci8:@", user, pass);
    Statement s = conn.createStatement();
    OraclePreparedStatement stmt;
    ResultSet rset = s.executeQuery(qryStr);
    OracleResultSet orset = (OracleResultSet) rset;
    while(orset.next())
    {
      //retrieve PurchaseOrder xml document from database
      XMLType xt = (XMLType)orset.getObject(1);
      //store this PurchaseOrder in po_xml_hist table
      stmt = (OraclePreparedStatement)conn.prepareStatement(
      "INSERT INTO po_xml_hist VALUES(?)");
      stmt.setObject(1,xt); // bind the XMLType instance
      stmt.execute();
      //update "DISCOUNT" element
      String newXML = updateXML(xt.getStringVal());
      // create a new instance of an XMLtype from the updated value
      xt = XMLType.createXML(conn, newXML);
      // update PurchaseOrder xml document in database
      stmt = (OraclePreparedStatement)conn.prepareStatement(
      "UPDATE po_xml_tab x SET x.poDoc =? WHERE " +
      "XMLCast(XMLQuery('/PO/PONO/text()'" +
      " PASSING value(xmltab) RETURNING CONTENT)" +
      " AS NUMBER)" +
      "= 200");
      stmt.setObject(1,xt); // bind the XMLType instance
      stmt.execute();
      conn.commit();
      System.out.println("PurchaseOrder 200 Updated!");
    }
    //delete PurchaseOrder 1001
    s.execute("DELETE FROM po_xml x WHERE" +
              "XMLCast(XMLQuery('/PurchaseOrder/PONO/text()'" +
              " PASSING value(xmltab) RETURNING CONTENT)" +
              " AS NUMBER)" +
              "= 1001");
    System.out.println("PurchaseOrder 1001 deleted!");
  }
  catch(Exception e)
  {
    e.printStackTrace(System.out);
  }
}
SELECT x.xpo.getCLOBVal() FROM po_xml x;

Example 13-9 Updated Purchase-Order Document

<?xml version = "1.0"?>
<PurchaseOrder>
  <PONO>200</PONO>
  <CUSTOMER>
   <CUSTNO>2</CUSTNO>
   <CUSTNAME>John Nike</CUSTNAME>
   <ADDRESS>
    <STREET>323 College Drive</STREET>
    <CITY>Edison</CITY>
    <STATE>NJ</STATE>
    <ZIP>08820</ZIP>
   </ADDRESS>
   <PHONELIST>
    <VARCHAR2>609-555-1212</VARCHAR2>
    <VARCHAR2>201-555-1212</VARCHAR2>
   </PHONELIST>
  </CUSTOMER>
  <ORDERDATE>20-APR-97</ORDERDATE>
  <SHIPDATE>20-MAY-97 12.00.00.000000 AM</SHIPDATE>
  <LINEITEMS>
   <LINEITEM_TYP LineItemNo="1">
    <ITEM StockNo="1004">
     <PRICE>6750</PRICE>
     <TAXRATE>2</TAXRATE>
    </ITEM>
    <QUANTITY>1</QUANTITY>
    <DISCOUNT>10</DISCOUNT>
   </LINEITEM_TYP>
   <LINEITEM_TYP LineItemNo="2">
    <ITEM StockNo="1011">
     <PRICE>4500.23</PRICE>
     <TAXRATE>2</TAXRATE>
    </ITEM>
    <QUANTITY>2</QUANTITY>
    <DISCOUNT>10</DISCOUNT>
   </LINEITEM_TYP>
  </LINEITEMS>
  <SHIPTOADDR>
   <STREET>55 Madison Ave</STREET>
   <CITY>Madison</CITY>
   <STATE>WI</STATE>
   <ZIP>53715</ZIP>
  </SHIPTOADDR>
</PurchaseOrder>

Example 13-10 Manipulating an XMLType Column Using JDBC

import java.sql.*;
import java.io.*;
import java.net.*;
import java.util.*;
import oracle.xml.parser.v2.*;
import oracle.xml.parser.schema.*;
import org.xml.sax.*;
import org.w3c.dom.*;
import oracle.xml.sql.dataset.*;
import oracle.xml.sql.query.*;
import oracle.xml.sql.docgen.*;
import oracle.xml.sql.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;
import oracle.xdb.XMLType;
public class tkxmtpk1
{
  static String conStr = "jdbc:oracle:oci8:@";
  static String user = "tpjc";
  static String pass = "tpjc";
  static String qryStr = "select x.resume from t1 x where id<3";
  static String xslStr =
    "<?xml version='1.0'?> " +
    "<xsl:stylesheet version='1.0' xmlns:xsl='http://www.w3.org/1" +
    "999/XSL/Transform'> " +
    "<xsl:template match='ROOT'> " +
    "<xsl:apply-templates/> " +
    "</xsl:template> " +
    "<xsl:template match='NAME'> " +
    "<html> " +
    " <body> " +
    " This is Test " +
    " </body> " +
    "</html> " +
    "</xsl:template> " +
    "</xsl:stylesheet>";
  static void parseArg(String args[])
  {
    conStr = (args.length >= 1 ? args[0]:conStr);
    user = (args.length >= 2 ? args[1].substring(0, args[1].indexOf("/")):user);
    pass = (args.length >= 2 ? args[1].substring(args[1].indexOf("/")+1):pass);
    qryStr = (args.length >= 3 ? args[2]:qryStr);
  }
  /**
  * Print the byte array contents
  */
  static void showValue(byte[] bytes) throws SQLException
  {
    if (bytes == null)
    System.out.println("null");
    else if (bytes.length == 0)
    System.out.println("empty");
    else
    {
      for(int i=0; i<bytes.length; i++)
        System.out.print((bytes[i]&0xff)+" ");
      System.out.println();
    }
  }
  public static void main(String args[]) throws Exception
  {
    tkxmjnd1 util = new tkxmjnd1();
    try
    {
      if(args != null)
        parseArg(args);
      // System.out.println("conStr=" + conStr);
      System.out.println("user/pass=" + user + "/" +pass );
      System.out.println("qryStr=" + qryStr);
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      Connection conn = DriverManager.getConnection(conStr, user, pass);
      Statement s = conn.createStatement();
      ResultSet rset = s.executeQuery(qryStr);
      OracleResultSet orset = (OracleResultSet) rset;
      OPAQUE xml;
      while(orset.next())
      {
        oracle.xdb.XMLType xt = (oracle.xdb.XMLType)(orset.getObject(1));
        System.out.println("Testing getDOM() ...");
        Document doc = xt.getDOM();
        util.printDocument(doc);
        System.out.println("Testing getBytesValue() ...");
        showValue(xt.getBytesValue());
        System.out.println("Testing existsNode() ...");
        try
        {
          System.out.println("existsNode(/)" + xt.existsNode("/", null));
        }
        catch (SQLException e)
        {
          System.out.println("Thin driver Expected exception: " + e);
        }
        System.out.println("Testing extract() ...");
        try
        {
          XMLType xt1 = xt.extract("/RESUME", null);
          System.out.println("extract RESUME: " + xt1.getStringVal());
          System.out.println("should be Fragment: " + xt1.isFragment());
        }
        catch (SQLException e)
        {
          System.out.println("Thin driver Expected exception: " + e);
        }
        System.out.println("Testing isFragment() ...");
        try
        {
          System.out.println("isFragment = " + xt.isFragment());
        }
        catch (SQLException e)
        {
          System.out.println("Thin driver Expected exception: " + e);
        }
        System.out.println("Testing isSchemaValid() ...");
        try
        {
          System.out.println("isSchemaValid(): " +
                             xt.isSchemaValid(null,"RES UME"));
        }
        catch (SQLException e)
        {
          System.out.println("Thin driver Expected exception: " + e);
        }
        System.out.println("Testing transform() ...");
        System.out.println("XSLDOC: \n" + xslStr + "\n");
        try
        {
          /* XMLType xslDoc = XMLType.createXML(conn, xslStr);
             System.out.println("XSLDOC Generated");
             System.out.println("After transformation:\n" +
                                (xt.transform(xslDoc,
                                 null)).getStringVal());
           */
           System.out.println("After transformation:\n" +
                              (xt.transform(null,
                               null)).getStringVal());
        }
        catch (SQLException e)
        {
          System.out.println("Thin driver Expected exception: " + e);
        }
        System.out.println("Testing createXML(conn, doc) ...");
        try
        {
          XMLType xt1 = XMLType.createXML(conn, doc);
          System.out.println(xt1.getStringVal());
        }
        catch (SQLException e)
        {
          System.out.println("Got exception: " + e);
        }
      }
    }
    catch(Exception e)
    {
      e.printStackTrace(System.out);
    }
  }
}

Example 13-13 Policy File Granting Permissions for Java DOM API

grant codeBase "file:c:\myworkspace" {
  permission java.lang.RuntimePermission "loadLibrary.orageneric12";
  permission java.lang.RuntimePermission "loadLibrary.oraxml12";
}
 
grant codeBase "file:c:\myworkspace\xdb6.jar" {
  permission java.lang.RuntimePermission "loadLibrary.orageneric12";
  permission java.lang.RuntimePermission "loadLibrary.oraxml12";
}
 
grant codeBase "file:c:\myworkspace\ojdbc6.jar" {
  permission java.lang.RuntimePermission "loadLibrary.orageneric12";
  permission java.lang.RuntimePermission "loadLibrary.oraxml12";
}

Example 13-14 Creating a DOM Object with the Java DOM API

import oracle.xdb.XMLType;
...
OraclePreparedStatement stmt = 
    (OraclePreparedStatement) conn.prepareStatement(
                                "update po_xml_XMLTypetab set poDoc = ? ");
// the second argument is a string
String poString = "<PO><PONO>200</PONO><PNAME>PO_2</PNAME></PO>";
XMLType poXML = XMLType.createXML(conn, poString);
Document poDOM = (Document)poXML.getDOM();
Element rootElem = poDOM.createElement("PO");
poDOM.insertBefore(poDOM, rootElem, null);
// now bind the string..
stmt.setObject(1,poXML);
stmt.execute();