Creating a Glossary
A useful function in any FAQ site is to have an online glossary that displays definitions of important technical terms when users click on the corresponding words. You can implement this feature in your FAQ site by marking up terms as <KEYWORD> elements and then creating links dynamically through XSLT to retrieve their definitions from a glossary table in the database. In this example, we have already created a glossary file in XML format that can be used.
Creating the Glossary Schema
First you need to create a glossary table in the database in which to store the definitions. It is not important that the glossary data is stored as XML; therefore, you can create a simple table as follows:
CREATE TABLE GLOSSARY(
ID NUMBER,
NAME VARCHAR2(30),
CONTENT VARCHAR2(4000)
);
CREATE SEQUENCE glossary_seq START WITH 100;
CREATE or replace TRIGGER glossary_insert
BEFORE INSERT ON glossary
FOR EACH ROW
BEGIN
select glossary_seq.nextval into :new.id from dual;
END;
/
show errors;
This SQL script creates a simple three-column table to store the ID, NAME, and CONTENT for each term. It also creates a sequence and a trigger to update the sequence so that the IDs are generated automatically every time a new entry is inserted.
Loading the Glossary
The supplied glossary file is called glossary.xml and will be loaded directly into the table using the DBMS_XMLSave package available in the database. The XML format for the glossary data is provided in the required canonical form:
<ROW num="1">
<NAME>API</NAME>
<CONTENT>(Application Program Interface) A set of public programmatic
interfaces that consist of a language and message format to communicate
with an operating system or other programmatic environment such as
databases, Web servers, JVMs, etc. These messages typically call
functions and methods available for application development.
</CONTENT>
</ROW>
All of these entries are contained within a <ROWSET> root element that will permit the entire document to be inserted at one time. The following PL/SQL script performs the insert:
DECLARE
insCtx DBMS_XMLSave.ctxType;
rows NUMBER;
BEGIN
insCtx := DBMS_XMLSave.newContext('GLOSSARY');
rows := DBMS_XMLSave.insertXML(insCtx, FileToClob('glossary.xml'));
DBMS_XMLSave.closeContext(insCtx);
DBMS_OUTPUT.put_line(rows||' of data in glossary have been updated.');
END;
/
show errors;
Note that even though a num=" attribute is included in the file, this is not used to index the entry. This is because attributes are not mapped by DBMS_XMLSAVE nor by DBMS_XMLSTORE or oracle.xml.sql.query.OracleXMLQuery() for data insertions. The CREATE SEQUENCE command ends up creating the ID to retrieve the item. In order to do the insertion, you need to first set the context to your table, in this case GLOSSARY. Then you can populate the rows in the table by using insertXML(), which accepts a CLOB that was populated using FiletoCLOB() to load glossary.xml. All that is left to do is to close the context, thus committing the changes.
Linking to the Glossary
Now that the glossary is loaded, you just need to be able to associate keywords within <KEYWORD> elements in your FAQs with the glossary and insert links. To do this, you need to query the retrieved FAQ answer for keywords. This is done in showAnswer.xsql with the following code:
<page>
<GLOSSARY>
<xsql:query>
<![CDATA[
SELECT name, id FROM glossary
WHERE name in (
SELECT extractValue(value(e), 'KEYWORD') FROM faq p,
table(xmlsequence(p.extract('/FAQ//KEYWORD'))) e
WHERE p.xmldata.id={@id}) ]]>
</xsql:query>
</GLOSSARY>
</page>
The query first uses the EXTRACT('/FAQ//KEYWORD') function to get the <KEYWORD> elements in the FAQ. Then using XMLSEQUENCE() and TABLE(), a table is created containing the set of <KEYWORD> elements in XMLType. This table is then used to create a collection of values of keywords using EXTRACTVALUE() and only the NAME and ID for each glossary item are returned by the query.Note that you are only trying to retrieve the set of IDs associated with the keywords found anywhere in the FAQ, as specified by the double-slash (//) in front of KEYWORD in p.extract(). Don’t confuse the two IDs specified in the query: the one in the SELECT is the glossary ID and the one in the predicate is the FAQ ID. An example of the actual query output is as follows:
<GLOSSARY>
<ROWSET>
<ROW num="1">
<NAME>CLASSPATH</NAME>
<ID>113</ID>
</ROW>
</ROWSET>
</GLOSSARY>
In this case, one keyword was found and it has 113 as its ID. Only those keywords that have glossary entries are returned.With the keyword IDs retrieved, the remaining task is to create a link in the HTML page. This is done in a template in showAnswer.xsl as follows:
<xsl:template match="KEYWORD">
<xsl:variable name="content">
<xsl:value-of select="./text()"/>
</xsl:variable>
<xsl:variable name="glossary">
<xsl:value-of select="/page/GLOSSARY/ROWSET/ROW[NAME/text()=
$content]/ID"/>
</xsl:variable>
<xsl:choose>
<xsl:when test="$glossary">
<a href=">
<xsl:value-of select="."/>
</a>
</xsl:when>
<xsl:otherwise>
<b>
<xsl:value-of select="."/>
</b>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
Because this template is complex, we will examine it closely. First, variables are used, because you need to pass in the ID dynamically to the link. These variables are used together. The content variable is simply the keyword. This is then reused in the glossary variable to create the XPath that selects the ID.The functional portion of the template is a choice between two formatting actions. If there is an instance of the glossary variable, then the test in <xsl:when> returns true and a link is created that passes the ID to a new page, showGlossary.xsql, to display the glossary item. If the test fails, then the <xsl:otherwise> section is executed, formatting the keyword in bold. This formatting flags these keywords for inclusion in the glossary.
Displaying the Glossary Definitions
Now that the links to keywords in the FAQ answers are added, we can turn to examine how the page displaying the definition is created. As specified by the following example link, this is once again performed by an XSQL page:
http://localhost:8988/xdkus/app_faq/showGlossary.xsql?id=100
The showglossary.xsql page is very simple, as shown here:
<?xml version="1.0" encoding='UTF-8'?>
<?xml-stylesheet type="text/xsl" href=" ?>
<page id="1" connection="xdkus" xmlns:xsql="urn:oracle-xsql">
<xsql:query>
<![CDATA[
SELECT NAME, CONTENT
FROM GLOSSARY
WHERE id={@id}
]]>
</xsql:query>
</page>
This page simply executes one query to retrieve the NAME and CONTENT from the GLOSSARY table and apply the showGlossary.xsl stylesheet to convert to HTML. The following is the XML output for CLASSPATH:
<?xml version="1.0" encoding="UTF-8" ?>
<?xml-stylesheet type="text/xsl" href=" ?>
<page id="1">
<ROWSET>
<ROW num="1">
<NAME>CLASSPATH</NAME>
<CONTENT>The operating system environmental variable that the JVM uses
to find the classes it needs to run applications.</CONTENT>
</ROW>
</ROWSET>
</page>
This stylesheet incorporates the same types of transformations discussed previously for showAnswer.xsl in the section “Displaying the FAQ and Answers.”