The configuration template features a simple set of XML tags that allow you to integrate SQL queries and XML. The tags live in the http://orbeon.org/oxf/xml/sql namespace, which is usually mapped to the sql prefix. An configuration input has the following format:

The sql:datasource element specifies a datasource name under java:comp/env/jdbc. In the example above, the datasource named my-datasource is used. How the datasource is configured depends on the application server used. Please refer to the documentation of your application server for more information.

Alternatively, the sql:datasource element can be omitted. In that case, the datasource input of the SQL processor must be connected to an external datasource definition, which describes database connections without using JNDI names mapped by the container. This is an example of datasource definition:

The sql:config element can contain any number of user-defined content before and after the sql:connection element.

It is important to make sure that one and exactly one root element is output by the SQL processor. A good place to put such a root element is around the sql:connection element:

The sql:execute element controls the execution of a single SQL query, call or update (SQL update, insert or delete). It must start with either a sql:query, a sql:call or a sql:update element, which contains the SQL to execute. sql:query and sql:call can be followed by zero or more sql:result-set elements and an optional sql:no-results element. Any number of sql:execute elements can be used under a sql:connection element, in order to execute several queries or updates within a single connection declaration.

sql:query and sql:update encapsulate SQL statements. Like with JDBC, a distinction is made between queries (SQL select) and updates (SQL update, insert and delete):

It is possible to pass parameters to a query using the sql:param element. sql:param requires a type attribute that specifies the type of the parameter to set. The type system is borrowed from the XML Schema Part 2: Datatypes specification. The following types are supported:

• xs:string
• xs:int
• xs:boolean
• xs:decimal
• xs:float
• xs:double
• xs:dateTime
• xs:date
• xs:base64Binary
• odt:xmlFragment

xs:date and xs:dateTime should be in one of the following formats:

• CCYY-MM-DDThh:mm:ss.sss
• CCYY-MM-DDThh:mm:ss
• CCYY-MM-DD

Unless a getter is nested in the sql:param element (see the section about nested queries below), a select attribute is mandatory. Its content is evaluated as an XPath expression against the input XML document:

Any number of sql:param elements may be used.

sql:param supports an optional boolean replace attribute, that can take the value true or false (the default). When replace is set to true, the parameter is simply replaced in the query, instead of being set on a JDBC PreparedStatement. This however works only with the xs:int and odt:literalString types. This attribute is useful to dynamically generate parts of SQL queries, or to set parameters that do not allow being set via JDBC's setYyy methods. For example, with SQL Server:

sql:param supports an optional separator attribute. When that attribute is present, the result of the XPath expression in the select attribute is interpreted as a node-set. One query parameter is set for each element in the node set, separated by the characters in the separator. For example:

Assuming the input document contains:

The following equivalent query will be executed:

The sql:call element allows for calling stored procedures using the JDBC escape syntax. sql:call uses the JDBC CallableStatement interface. The following example calls a procedure called SalesByCategory with two parameters:

The brackets ("{" and "}") and the keyword call are part of the JDBC escape syntax which allows calling stored procedures without using a proprietary syntax.

These elements must be used only in conjunction with sql:query or sql:call.

A query or call may return multiple result-sets. It is possible to handle the result-sets returned by a query or call individually for each result-set, or globally for all result-sets, using the sql:result-set element. The optional result-sets attribute specifies how many result-sets are handled by a given sql:result-set element. If not specified, the default is one result-set. If the value is unbounded, the sql:result-set element handles all the remaining result-sets returned by the statement execution. Otherwise, a positive number of result-sets must be specified. For example:

If a sql:no-results element is present after the sql:result-sets elements, its template content executes only if none of the previous sql:result-set elements has returned rows. sql:no-results may contain user-defined elements and nested queries (see below).

sql:result-set may contain a sql:row-iterator element, which is evaluated once for every row of the result set. sql:row-iterator typically contains user-defined content, as well as column getters such as sql:get-column-value and sql:get-columns.

Assuming the result set contains two rows with "John Doe" and "Billy Smith", the above code produces the following XML fragment:

3.7.1. Retrieving Single Columns

3.7.2. Retrieving Multiple Columns

3.7.3. Result-Set Metadata

It is possible to dynamically generate new attributes with the sql:attribute element:

This will result in something like:

You can explicitly iterate over all the columns returned by a result-set with the sql:column-iterator element. A column iterator can be used under the sql:result-set element, or under the sql:row-iterator element. This allows for example easily extracting column metadata:

As explained in the section about sql-type above, when text data, in particular XML data, is large, it is best stored as a CLOB type or, in the case of XML, as a native database XML data type such as the Oracle XMLType data type.

Reading and writing XML data is supported to and from database CLOBs and, with Oracle 9, to and from XMLType. The odt:xmlFragment type must be used. To write XML data to a CLOB, use the odt:xmlFragment type as follows:

The XPath expression must return one element node. The result of the XPath expression specified in the select attribute is converted into a new XML document having as root element the selected element node. The document is then serialized to a character stream and stored as a CLOB.

To read a document from a CLOB column, use the odt:xmlFragment type as follows:

For each row returned, the character data stored in the CLOB column is read as text and parsed into an XML fragment. The fragment must be well-formed, otherwise an exception is thrown. The resulting fragment is then embedded into the SQL processor output.

With Oracle 9, it is also possible to write to the native Oracle XMLType data type:

Reading from an XMLType column is done the same way as with a CLOB column:

When setting a parameter of type xs:string or odt:xmlFragment, it is possible to specify an additional attribute on sql:param: sql-type. By default, text is written using the JDBC setString() method. In case the data must be stored in the database as a Character Large OBject (CLOB) or other database-specific types, it is necessary to tell Orbeon Forms that a different API must be used. For example, to write a string into a CLOB:

The same string can be written as a regular varchar type as follows:

varchar is actually the default, so you can simply omit the sql-type and write:

The following values are supported for sql-type:

• varchar (the default)
• clob
• xmltype (see Reading and Writing XML Documents below)

Reading and writing binary data is supported to and from database Binary Large OBjects (BLOBs) as well as binary types (BINARY, VARBINARY and LONGVARBINARY SQL types). The xs:base64Binary type (read and write) or the xs:anyURI type (write only) must be used. To write to a BLOB, use the xs:base64Binary type as follows:

The result of the XPath expression specified in the select attribute is converted into a character string, following the XPath semantics. That string is then interpreted as Base64-encoded data, before being written to the BLOB column. For example, the following input document:

Is converted to the following string when the expression /* is applied:

With xs:anyURI, the result of the XPath expression is converted into a string and interpreted as a URL. The URL is read, and the resulting data is stored into the BLOB column. For example:

To read a BLOB or BINARY column, use the xs:base64Binary type as follows:

This will produce the following result if the document above was written to the database first:

The sql:value-of and sql:copy-of elements have the same semantics as their XSLT 1.0 couterparts. They work against the SQL processor's input XML document.

Those elements support functions in the http://orbeon.org/oxf/xml/sql namespace. The only function supported for the moment is sql:row-position(), which returns, in a sql:row-iterator, the index of the current row in the result set, starting with row number 1. For example:

This generates the following results:

The sql:update element supports an optional select attribute. It is evaluated as an XPath expression against the input XML document. The expression must return a node-set (which may be empty). The update statement is executed once for every node returned. select attributes on nested sql:param elements are evaluated using the selected node as current node. With the following input XML document:

The following update inserts two rows in the database:

Consider the following three SQL tables organized in a tree. The level2 table references the level1 table, and the level3 table references the level2 table.

level1

level2

level3

A flat representation of the three tables joined on their respective foreign keys yields the following rows:

Often it is useful to group results in order to output certain values only once. In a table, this can look like the following:

A generalization of this consists in generating output of the form:

There are two ways of generating such results. The first way is to use nested queries. A first query returns all the rows in the level1 table. Then, for each row returned, a second query returns all rows in the level2 table referencing the current row's level1_id. Similarly, for each row returned by that second query, a new query is done to get the relevant level3 rows. The code would look as follows:

A nested query can access parameters from the input XML document like any regular query. It can also access results from outer queries by nesting a getter in a sql:param element. In that case, getters can take an optional ancestor attribute that specifies which level of outer query to access. If omitted, the ancestor attribute takes the value 1 when used in a sql:query or sql:update, which means the first outer query; it defaults to the value 0 when used in a sql:row-iterator, which means the query at the current level.

While nested queries have their uses, in the example above 3 queries have to be written and no less than 7 queries are executed to produce the final result. It can be elegantly rewritten using the sql:group and sql:member elements. sql:group has to be the first element under a sql:row-iterator or sql:member element. It takes a mandatory column-name attribute that specifies the name of the column on which grouping is done.

For every group, a header is output only once. Then, the content under the sql:member element is output for each row. Finally, the footer is output. The header and the footer can access columns There is no limit in as to how deep grouping can be done.

The code below generates with a single SQL query the same results as the example above:

Note that correct ordering of the rows in the SQL query is important because headers and footers are output when the columns on which grouping is done change, in the order returned by the result set.

Like in XSLT 1.0, text nodes in the configuration containing only whitespace characters are stripped. Text nodes that contain at least one non-whitespace character are not stripped and copied to the output.

To better control the output of text, the sql:text element is provided. It is similar to the xsl:text element. xsl:text encapsulate text that is output as is. In particular, it can encapsulate all whitespace characters.