XML Features of Oracle 8i and 9i (3/7) - exploring XML

XML Features of Oracle 8i and 9i

XSU via Java API

The following basic code shows a connection to the database, use of OracleXMLQuery class and how to output that as a string:

import oracle.jdbc.driver.*;
import oracle.xml.sql.query.OracleXMLQuery;
import java.lang.*;
import java.sql.*;
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
DriverManager.getConnection("jdbc:oracle:thin:@myserver.mydomain:1521:MY_SID",
"scott","tiger");
OracleXMLQuery qry = new OracleXMLQuery(conn, "select * from emp");
String str = qry.getXMLString();
System.out.println(" The XML output is:\n"+ str);
qry.close();

In practice you would normally want the data in DOM form:

XMLDocument domDoc = qry.getXMLDOM();

For web applications, you would usually need to write code to deal with a recordset, specifying how many records should be output at a time and being able to paginate backwards and forwards. This is done by using a recordset object and moving with rset.absolute(startRow); rset.relative(10);

With the data as XML, you would normally use the Oracle XML parser, for example to apply an XSL stylesheet before final output:

/* example code simplified/non-functional - only for illustration */
import oracle.xml.parser.v2.*;
// instantiate a stylesheet
XSLStylesheet xsl = new XSLStylesheet(xsldoc, xslURL);
XSLProcessor processor = new XSLProcessor();
// Process XSL
DocumentFragment result = processor.processXSL(xsl, xml);
// create an output document to hold the result
out = new XMLDocument();
// create a dummy document element for the output document
Element root = out.createElement("root");
out.appendChild(root);
// append the transformed tree to the dummy document element
root.appendChild(result);

For updates and deletes, use the OracleXMLSave class. The KeyColumnList property identifies which columns are used for the key.

import oracle.xml.sql.dml.OracleXMLSave;
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
DriverManager.getConnection("jdbc:oracle:thin:@myserver.mydomain:1521:MY_SID",
"scott","tiger");
String [] keyColNames = new String[1];
keyColNames[1] = "EMPNO";
sav.setKeyColumnList(keyColNames);
sav.updateXML(XMLfilename);
sav.close();

With the following XML as the input file:

<ROWSET>
  <ROW num="1">
    <EMPNO>7369</EMPNO>
    <SAL>1800</SAL>
    <DEPTNO>30</DEPTNO>
  </ROW>
  <ROW>
    <EMPNO>2290</EMPNO>
    <SAL>2000</SAL>
    <HIREDATE>12/31/1992</HIREDATE>
  </ROW>
  <!-- additional rows ... -->
</ROWSET>

would apply these two updates:

update scott.emp SET SAL = 1800 and DEPTNO = 30 WHERE EMPNO = 7369;
update scott.emp SET SAL = 2000 and HIREDATE = 12/31/1992 WHERE EMPNO = 2290;

As mentioned above, PL/SQL also has an API for XSU with the same functionality.

On to XSQL...

Produced by Michael Claßen

URL: https://www.webreference.com/xml/column81/3.html
Created: May 10, 2003
Revised: May 10, 2003