Example 13-5 Updating an XMLType Column Using SQL Constructor XMLType and Java String

PreparedStatement statement =
    connection.prepareStatement("UPDATE po_xml_tab SET poDoc = XMLType(?)");

String poString = "<PO><PONO>200</PONO><PNAME>PO_2</PNAME></PO>";

// Bind the string
statement.setString(1,poString);
statement.execute();

Example 13-6 Updating an XMLType Column Using SQLXML

PreparedStatement statement =
    connection.prepareStatement("UPDATE po_xml_tab SET poDoc = ?");

String xmlString = "<PO><PONO>200</PONO><PNAME>PO_2</PNAME></PO>";
SQLXML sqlXml = connection.createSQLXML();
sqlXml.setString(xmlString);

// Bind the SQLXML
statement.setSQLXML(1, sqlXml);
statement.execute();

Example 13-7 Retrieving Metadata About an XMLType Column Using JDBC

PreparedStatement statement =
    connection.prepareStatement("SELECT poDoc FROM po_xml_tab");
ResultSet resultSet = statement.executeQuery();

// Get the resultSet metadata
ResultSetMetaData mdata = (ResultSetMetaData)resultSet.getMetaData();

// The column type is SQLXML
if (mdata.getColumnType(1) == java.sql.Types.SQLXML)
{
  // It is a SQLXML instance
}

Example 13-8 Updating an XMLType Column Using JDBC

public class UpdateXMLType
{
  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(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("XML string is well-formed");
      XMLDocument document = parser.getDocument();
      NodeList nl = document.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();
      document.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
    {
      PreparedStatement statement = connection.createStatement();
      ResultSet resultSet = statement.executeQuery(qryStr);

      while(orset.next())
      {
        //retrieve PurchaseOrder xml document from database
        SQLXML sqlXml = resultSet.getSQLXML(1);

        //store this PurchaseOrder in po_xml_hist table
        statement = connection.prepareStatement("INSERT INTO po_xml_hist VALUES(?)");
        statement.setSQLXML(1,sqlXml); // bind the SQLXML instance
        statement.execute();

        //update "DISCOUNT" element
        String newXML = updateXML(sqlXml.getString());
        // create a new instance of an XMLtype from the updated value
        SQLXML sqlXml2 = connection.createSQLXML();
        sqlXml2.setString(newXml);
        
        // update PurchaseOrder xml document in database
        statement = connection.prepareStatement(
                       "UPDATE po_xml_tab x SET x.poDoc =? WHERE " +
                       "XMLCast(XMLQuery('/PO/PONO/text()'" +
                       " PASSING value(xmltab) RETURNING CONTENT)" +
                       " AS NUMBER)" +
                       "= 200");

        statement.setSQLXML(1, sqlXml2); // bind the XMLType instance
        statement.execute();
        connection.commit();
        System.out.println("PurchaseOrder 200 Updated!");
      }

      //delete PurchaseOrder 1001
      statement.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);
    }
  }
}

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 Inserting an XMLType column using JDBC

private void insertXML(Connection connection, String xmlString)
{
  OracleClob clob = null;
  try
  {
    String query = "INSERT INTO potable (purchaseOrder) VALUES (XMLType(?)) ";

    // Get the statement Object
    PreparedStatement statement = connection.prepareStatement(query);

    // Get the OracleClob instance from xmlString
    clob = getOracleClob(connection, xmlString);
    statement.setObject(1, clob);

    // Execute the prepared statement
    if (statement.executeUpdate () == 1)
    {
      System.out.println ("Successfully inserted a Purchase Order");
    }
  }
  catch(Exception exp)
  {
    exp.printStackTrace();
  }
  finally 
  {
    if(clob !=null)
      clob.close();
  }
}

Example 13-11 Converting an XML String to an OracleClob Instance

private OracleClob getOracleClob(Connection connection, String xmlString) throws SQLException
{
    OracleClob clob =(OracleClob) connection.createClob();
    clob.setString(1, xmlString);
    return clob;
}

Example 13-12 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-13 Creating a DOM Object with the Java DOM API

PreparedStatement statement = connection.prepareStatement(
                                "update po_xml_XMLTypetab set poDoc = ? ");                              
String xmlString = "<PO><PONO>200</PONO><PNAME>PO_2</PNAME></PO>";

OracleClob clob = (OracleClob)connection.createClob();
clob.setString(1, xmlString);
SQLXML sqlXml    = clob.toSQLXML();

DOMSource domSource = sqlXml.getSource(DOMSource.class);
Document  document  = (Document) domSource.getNode();
Element   rootElem  = document.createElement("PO");
document.insertBefore(document, rootElem, null);

SQLXML sqlXml2 = clob.toSQLXML();

DOMResult domResult = sqlXml2.setResult(DomResult.class);
domResult.setNode(document);
statement.setSQLXML(1, sqlXml2);
statement.execute();

Example 13-14 Using the Java DOM API with a Binary XML Column

class PrintBinaryXML
{
  public static void printBinXML() throws SQLException, BinXMLException
  {
    // Create datasource to connect to local database
    OracleDataSource ods = new OracleDataSource();
    ods.setURL("jdbc:oracle:kprb");
    
    System.out.println("Starting Binary XML Java Example");

    // Create data connection
    Connection connection = ods.getConnection();
    // Create binary XML metadata context, using connection pool
    DBBinXMLMetadataProvider repos =
      BinXMLMetadataProviderFactory.createDBMetadataProvider();
    repos.setConnectionPool(ods);

    // Associate metadata context with data connection
    repos.associateDataConnection(connection);

    // Query XML data stored in SQLXML column as binary XML
    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery("SELECT doc FROM po_binxmltab");

    // Get the SQLXML object
    while (resultSet.next())
    {
      SQLXML sqlXml = resultSet.getSQLXML(1);

      // Convert SQLXML to a String
      String xmlString = sqlXml.getString();
      System.out.println(xmlString);
    }

    resultSet.close();
    statement.close();
    connection.close();

    System.out.println("Completed Binary XML Java Example");
  }
}