The Saxon distribution includes a set of extension elements providing access to SQL databases. These are not intended as being necessarily a production-quality piece of software (there are many limitations in the design), but more as an illustration of how extension elements can be used to enhance the capability of the processor.
To use the SQL extension elements in a stylesheet, you need to define a namespace prefix (for example "sql") in the extension-element-prefixes attribute of the xsl:stylesheet element, and to map this prefix to namespace URI that ends in "/net.sf.saxon.sql.SQLElementFactory".
This extension defines five new stylesheet elements: sql:connect, sql:query, sql:insert, sql:column, and sql:close, described in the following sections.
sql:connect creates a database connection. It has attributes
all of which are attribute value templates (so the values can be passed in as parameters).
driver attribute names the JDBC driver class to be used. The
database name must be a name that JDBC can associate with an actual database.
sql:query performs a query, and writes the results of the query to
the result tree, using elements to represent the rows and columns. If you want to process
the results in the stylesheet, you can write the results to a temporary tree by using
sql:query instruction as a child of
The attributes are as follows:
|table||The table to be queried (the contents of the FROM clause of the select statement). This is mandatory, the value is an attribute value template.|
|column||The columns to be retrieved (the contents of the SELECT clause of the select statement). May be "*" to retrieve all columns. This is mandatory, the value is an attribute value template.|
|where||The conditions to be applied (the contents of the WHERE clause of the select statement). This is optional, if present the value is an attribute value template.|
|row-tag||The element name to be used to contain each row. Must be a simple name (no colon allowed). Default is "row".|
|column-tag||The element name to be used to contain each column. Must be a simple name (no colon allowed). Default is "col".|
|disable-output-escaping||Allowed values are "yes" or "no", default is "no". The value "yes" causes the content of all rows/columns to be output as is, without converting special characters such as "<" to "<". This is useful where the database contains XML or HTML markup that you want to be copied into the result document. Use this option with care, however, since it applies to all the columns retrieved, not only to those that contain XML or HTML. An alternative is to use the saxon:parse() extension function to process the contents of an XML column.|
sql:insert performs an SQL INSERT statement. This causes a row to be added to the table identified by the "table" attribute.
sql:column is used as a child element of sql:insert, and identifies the name and value of a column to be included in the INSERT statement. The name of the column is identified by the "name" attribute, the value may be indicated either by evaluating the expression contained in the "select" attribute, or as the expanded contents of the sql:column element. The value is always interpreted as a String. (Remember this is purely a demonstration of extensibility, in a real system there would be a need to cater for SQL columns of other data types).
sql:close closes the database connection.
A specimen stylesheet that uses these XSL extension is books-sql.xsl. This loads the contents of the books.xml file into a database table, To use it, you need to create a database database containing a table "Book" with three character columns, "Title", "Author", and "Category"
Here is the stylesheet:
To run this stylesheet you will need to do the following:
java net.sf.saxon.Transform data\books.xml books-sql.xsl
The database will be populated with data from the
Michael H. Kay
20 March 2002