15 Java DOM API for XMLType

Example 15-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 15-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 15-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
}

Example 15-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 15-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 15-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));
}
Java DOM API for XMLType
Beta Draft Java DOM API for XMLType 15-11
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-13 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();