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"); } } } |