15 Java DOM API for XMLType

Example 15-6 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 15-7 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 15-8 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.exuecuteQuery();

// Now, we can 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)
{
   // we know it is an XMLtype
}

Example 15-9 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
);

/*
   DESCRIPTION
    Example for oracle.xdb.XMLType
   NOTES
   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)
  {
     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.createXML(orset.getOPAQUE(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);
    }
  }
}

---------------------- 
-- list PurchaseOrders
----------------------
SELECT x.xpo.getCLOBVal() FROM po_xml x;

Example 15-10 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 15-11 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()){
      xml = orset.getOPAQUE(1);
      oracle.xdb.XMLType xt = oracle.xdb.XMLType.createXML(xml);
      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 15-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();