Example 6-39: Programmatically transforming ROWSET/ROW query results

import java.sql.*;
import oracle.xml.sql.query.*;
import oracle.xml.parser.v2.*;
import java.net.URL;
import java.io.*;
import XMLDocuments;
import URLUtils;
import Examples;

public class EnrollmentWriter {
  // Cache the stylesheet for the duration of the session
  private static XSLStylesheet sheet = null;
  private Connection conn = null;
  String  schoolId = null;
  public EnrollmentWriter(String schoolId,Connection conn) {
    this.schoolId = schoolId;
    this.conn     = conn;
  }
  // Print out the XML for an Enrollment by schoolid
  public void printXML(PrintWriter output) throws Exception {
    // Use a CURSOR() expression to get master/detail, nested results
    // of distinct courses and the students in each course
    String query = "SELECT course, CURSOR(SELECT name,age "+
                   "                        FROM course_assignments b"+
                   "                       WHERE b.course = a.course"+
                   "                     ) AS students"+
                   "  FROM course_assignments a"+
                   " WHERE school_id = "+ schoolId +
                   " GROUP BY course"+
                   " ORDER BY course";
    // Create an instance of the OracleXMLQuery object
    OracleXMLQuery q = new OracleXMLQuery(conn,query);
    // Set some of its XML Generation options
    q.useLowerCaseTagNames();
    q.setRowsetTag("courses");
    // Retrieve the results as an in-memory XMLDocument
    XMLDocument xmldoc = (XMLDocument)q.getXMLDOM();
    // If the stylesheet is null, go set it up the first time
    if (sheet == null) setupStylesheet();
    // Set the top-level stylesheet parameter named "School"
    // Note that the value needs to be quoted!
    sheet.setParam("School","'"+schoolId+"'");
    // Transform the XML document using the stylesheet
    // Writing the output to System.out, then close the connection
    XSLProcessor xslt = new XSLProcessor();
    xslt.processXSL(sheet,xmldoc,output);
  }
  // Setup and cache XSLT stylesheet
  private void setupStylesheet() throws Exception {
    URL stylesheetURL = null;
    // If we're inside Oracle8i, read Enrollment.xsl from xml_documents table
    if (Examples.insideOracle8i()) {
      XMLDocuments.enableXMLDocURLs();
      stylesheetURL = new URL("xmldoc:/transforms/Enrollment.xsl");
    }
    // If we're outside Oracle8i, read Enrollment.xsl from current directory
    else stylesheetURL = URLUtils.newURL("Enrollment.xsl");
    try {
      // Construct the stylesheet from the XML coming in from the reader
      sheet = new XSLStylesheet(stylesheetURL,stylesheetURL);
    }
    catch (Exception e) {
      throw new RuntimeException("Failed to read Enrollment.xsl");
    }
  }
}