Oracle8i Application Developer's Guide - XML Release 3 (8.1.7) Part Number A86030-01 |
Library | Service | Contents | Index

Customizing Content with XML: Dynamic News Application, 13 of 16

## 2 Pull News Items from the Database

The following code, from `xmlnews.admin.AdminServlet.performGeneration` and `xmlnews.admin.AdminServlet.staticProcessingHtml`, shows how the application queries the database for news items in each available category and converts each result set to a XML document.

The database stores the XML for each category as a CLOB (Character Large OBject), so the application can handle very long lists.

    public void performGeneration(String p_user, String p_genType,
          HttpServletResponse p_response)
          throws ServletException, IOException {
    ...
          try {
          String l_fileSep = System.getProperty("file.separator");
          String l_message = ""; // Holds status message

          if (p_genType.equals("BATCH_GEN")) { // Batch Generation
          String l_htmlFile = "BatchGeneration";
          String l_xslFile  = "BatchGeneration";
          String l_xmlFile  = "BatchGeneration";

          // Generate the XML and HTML content and save it in a file
             this.staticProcessingHtml(
             m_dynNewsEnv.m_dynNewsHome+l_fileSep+l_htmlFile+".html",
             m_dynNewsEnv.m_dynNewsHome+l_fileSep+m_dynNewsEnv.m_batchGenXSL,
             m_dynNewsEnv.m_dynNewsHome+l_fileSep+l_xmlFile+".xml"
          );
          ...
        }
     ...
     }

The method `xmlnews.admin.AdminServlet.staticProcessingHtml` defines and executes a query to fetch the news items. Then it uses the Oracle XML-SQL Utility (XSU) to build an XML document from the result set and create an HTML page by applying an XSLT transformation.

    public void staticProcessingHtml(String p_htmlFile,String p_xslfile,
       String p_xmlfile) throws Exception {
       String l_query = "select a.id, a.title, a.URL, a.DESCRIPTION, " +
       " to_char(a.ENTRY_DATE, 'DD-MON-YYYY'), a.CATEGORY_ID, b.name,
                       a.SUB_CATEGORY_ID, c.name, a.Type_Id, d.name, " +
    " a.Submitted_By_Id, e.name, to_char(a.expiration_date, 'DD-MON-YYYY'),
                                                                a.approved_flag " +
    " from news_items a, categories b, sub_categories c, types d, users e where " +
    " a.category_id is not null and a.sub_category_id is not null and "+
    " a.type_id is not null and a.EXPIRATION_DATE is not null and "+
    " a.category_id = b.id  AND a.SUB_CATEGORY_ID = c.id AND a.Type_ID = d.id
                              AND " +
    " a.SUBMITTED_BY_ID = e.id AND "+
    " a.EXPIRATION_DATE > SYSDATE AND "+
    " a.APPROVED_FLAG = \'A\' ORDER BY b.name, c.name ";

     Statement l_stmt = m_connection.createStatement();
     ResultSet l_result = l_stmt.executeQuery(l_query);
    // Construct the XML Document using Oracle XML SQL Utility
       XMLDocument l_xmlDocument = m_xmlHandler.constructXMLDoc(l_result);
       l_stmt.close();

    // Get the HTML String by applying corresponding XSL to XML.
       String l_htmlString = m_xmlHandler.applyXSLtoXML(l_xmlDocument,p_xslfile);

     File l_file = new File(p_htmlFile);
     FileOutputStream l_fileout = new FileOutputStream(l_file);
     FileOutputStream l_xmlfileout = new FileOutputStream(new File(p_xmlfile));.
     l_fileout.write(l_htmlString.getBytes());
     l_xmlDocument.print(l_xmlfileout);

     l_fileout.close();
     l_xmlfileout.close();
    }

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index