Processors - SQL

Documentation‎ > ‎II. Developer and Administrator Guide‎ > ‎

Processors - SQL

Introduction

The SQL processor provides an XML interface to any SQL database accessible through JDBC. It allows to easily query databases and produce XML outputs readily usable by other processors. Conversely, the SQL processor allows to perform updates, insertions and deletions from XML data generated by other processors.

Inputs and outputs

Type	Name	Purpose	Mandatory
Input	`config`	Configuration template	Yes
Input	`data`	Source XML data	No
Input	`datasource`	Datasource configuration	No
Output	`data`	Result XML data	No

If the data input is not connected, the SQL processor configuration must not use XPath expressions operating on it. If this condition is not satisfied, the SQL processor generates an error at runtime. It is typically useful to omit the data input when the SQL processor only reads data from the database and generates an output XML document.

If the data output is not connected, the output of the SQL processor is simply ignored. It is typically useful to omit the data output when the SQL processor only updates or insert data into the database from an input XML document.

Configuration template

Configuration

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:

<sql:config>
    <!-- Optional user content -->
    <sql:connection>
        <!--  Datasource name (can also be specified with optional "datasource" input)  -->
        <sql:datasource>my-datasource</sql:datasource>
        <!-- ... -->
    </sql:connection>
    <!-- Optional user content -->
</sql:config>

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:

<datasource>
    <!--  Specify the driver for the database  -->
    <driver-class-name>org.hsqldb.jdbcDriver</driver-class-name>
    <!--  This causes the use of the embedded database  -->
    <uri>jdbc:hsqldb:file:orbeondb</uri>
    <!--  Optional username and password  -->
    <username>sa</username>
    <password/>
</datasource>

WARNING: External datasource definitions do not use connection pooling at the moment. Because creating database connections is usually an expensive operation, they should be used only for development or demo purposes.

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

Root 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:

<sql:config>
    <employees>
        <sql:connection>
            <!-- ... -->
        </sql:connection>
    </employees>
</sql:config>

sql:execute

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.

<!-- Optional user content -->

<sql:execute>
    <sql:query>
        <!-- ... -->
    </sql:query>
    <!-- Optional user content -->
    <sql:result-set>
        <!-- ... -->
    </sql:result-set>
    <sql:no-results>
        <!-- ... -->
    </sql:no-results>
</sql:execute>

<!-- Optional user content -->

<sql:execute>
    <sql:call>
        <!-- ... -->
    </sql:call>
    <!-- Optional user content -->
    <sql:result-set>
        <!-- ... -->
    </sql:result-set>
    <sql:no-results>
        <!-- ... -->
    </sql:no-results>
</sql:execute>

<!-- Optional user content -->

<sql:execute>
    <sql:update>
        <!-- ... -->
    </sql:update>
</sql:execute>

<!-- Optional user content -->

sql:query and sql:update

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):

<sql:query>
  select * from employee
</sql:query>

<sql:update>
  insert into employees values ('John', 'Doe')
</sql:update>

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

NOTE: By default, the xs and odt prefixes must be declared, as is customary in XML. For backward compatiblity with versions of Orbeon Forms where the prefixes did not have to be declared, set the legacy-implicit-prefixes property as follows:

<property
  as="xs:boolean"
  processor-name="oxf:sql"
  name="legacy-implicit-prefixes"
  value="true"/>

When this property is missing or set to false, type prefixes must be mapped as is customary for XML vocabularies. Add the following namespace declarations: xmlns:xs="http://www.w3.org/2001/XMLSchema" and xmlns:odt="http://orbeon.org/oxf/xml/datatypes". Doing so then allows using data types as before, for example xs:string or odt:xmlFragment.

xs:date and xs:dateTime must be in XML Schema ISO format (see dateTime and date), for example:

2012-05-29T19:35:04.123Z
2012-05-29T19:35:04.123-07:00
2012-05-29T19:35:04.123
2012-05-29T19:35:04
2012-05-29T19:35:04-07:00
2012-05-29T19:35:04
CCYY-MM-DDZ
CCYY-MM-DD-07:00
CCYY-MM-DD

If no timezone is specified, the System's default timezone is used.

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:

<sql:query>
    select first_name, last_name
      from employee
     where employee_id in (<sql:param type="xs:int" select="/query/employee-id[1]"/>,
                           <sql:param type="xs:int" select="/query/employee-id[2]"/>)
</sql:query>

Any number of sql:param elements may be used.

sql:param supports an optional boolean null attribute, which contains a boolean XPath expression. If the expression returns true, then the query parameter is set to the null value:

<sql:query>
    select *
      from employee
     where foobar = <sql:param type="xs:int" select="/query/foobar"
                        null="normalize-space(/query/foobar) = ''"/>
</sql:query>

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 oxf: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:query>
    select top <sql:param type="xs:int" select="/query/max-rows" replace="true"/> *
      from employee
</sql:query>

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:

<sql:query>
   select * from book
    where book_id in (<sql:param type="xs:int" select="/query/book-id" separator=","/>)
</sql:query>

Assuming the input document contains:

<query>
    <book-id>5</book-id>
    <book-id>7</book-id>
    <book-id>11</book-id>
    <book-id>13</book-id>
</query>

The following equivalent query will be executed:

<sql:query>
   select * from book
    where book_id in (5, 7, 11, 13)
</sql:query>

Logging

The SQL processor can log the SQL statement it executes. Do enable this, add a debug attribute on the <sql:update> or <sql:query>.

The value if the debug attribute is a message of your choosing. That message is logged along the executed SQL statement, to make the log more readable. This logging is done by org.orbeon.oxf.processor.sql.SQLProcessor at info level.

Example of use:

<sql:query debug="my query">
…
</sql:query>

Example of query logged:

PreparedStatement (debug="my query"):

select
last_modified,
t.xml.getClobVal() xml
from orbeon_form_definition t
where app = ?
and form = ?
and last_modified = (
select max(last_modified) from orbeon_form_definition
where app = ?
and form = ?
)
and deleted = 'N'

Calling stored procedure with sql:call

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:

<sql:call>
    { call SalesByCategory(<sql:param type="xs:string" select="/*/category"/>,
                           <sql:param type="xs:int" select="/*/year"/>) }
</sql:call>

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

NOTE: OUT and INOUT parameters are not yet supported.

Handling result-sets with sql:result-set and sql:no-results

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:

<!-- Handle the first two result-sets -->

<sql:result-set result-sets="2">
    <my-first-result-sets>
        <sql:row-iterator>
            <row>
                <sql:get-columns format="xml"/>
            </row>
        </sql:row-iterator>
    </my-first-result-sets>
</sql:result-set>

<!-- Handle All the remaining result-sets -->

<sql:result-set result-sets="unbounded">
    <my-other-result-sets>
        <sql:row-iterator>
            <row>
                <sql:get-columns format="xml"/>
            </row>
        </sql:row-iterator>
    </my-other-result-sets>
</sql:result-set>

<!-- This will be executed if no row was returned by any result-set -->

<sql:no-results>
    <there-are-no-results/>
</sql:no-results>

NOTE: The body of the sql:result-set element is not executed if the result-set does not contain at least one row.

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.

<sql:result-set>
    <sql:row-iterator>
        <employee>
            <first-name><sql:get-column-value type="xs:string" column="first_name"/></first-name>
            <last-name><sql:get-column-value type="xs:string" column="last_name"/></last-name>
        </employee>
    </sql:row-iterator>
</sql:result-set>

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

<employee>
    <first-name>John</first-name>
    <last-name>Doe</last-name>
</employee>

<employee>
    <first-name>Billy</first-name>
    <last-name>Smith</last-name>
</employee>

Getting column values

Retrieving single columns

sql:get-column-value takes a mandatory column-name attribute and an optional type attribute. If specified, the attribute must be compatible with the SQL type of the column being read. 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

NOTE: The same remark applies to types used on sql:param: the prefixes must be declared unless the legacy-implicit-prefixes specifies otherwise.

xs:dateTime returns a date in the following format: CCYY-MM-DDThh:mm:ss.sss.

xs:date returns a date in the following format: CCYY-MM-DD.

odt:xmlFragment is a special type that gets the column as a string, parses it as an XML fragment, and embeds the resulting XML in the SQL processor output.

NOTE:

For compatibility with XPath 1.0, xs:float and xs:double do not return values in the exponential notation. For example, instead of 1.2E10, 12000000000 is returned.

Retrieving multiple columns

When the number of columns returned is large, it is convenient to use sql:get-columns, which automatically determines what columns are available and generates elements accordingly. sql:get-columns takes an optional prefix attribute specifying the output namespace prefix to use for all the elements, and an optional format attribute specifying how column names are converted. It also supports any number of embedded exclude elements that specify columns to exclude from the result.

sql:get-columns supports an all-elements attribute. If set to true, an element is output for a column even if that column returns a null value. If missing or set to false, no element is output for a null column.

The namespace prefix, if specified, must have been mapped to a namespace URI.

If no format is specified, the original column names are used. Specifying the xml format converts all column names to lower case and transforms "_" into "-".

This example:

<sql:result-set>
    <sql:row-iterator>
        <employee>
            <sql:get-columns format="xml"/>
        </employee>
    </sql:row-iterator>
</sql:result-set>

Produces these results:

<employee>
    <first-name>John</first-name>
    <last-name>Doe</last-name>
</employee>

<employee>
    <first-name>Billy</first-name>
    <last-name>Smith</last-name>
</employee>

Not specifying the xml format generates the following results:

<employee>
    <first_name>John</first_name>
    <last_name>Doe</last_name>
</employee>

<employee>
    <first_name>Billy</first_name>
    <last_name>Smith</last_name>
</employee>

It is possible to exclude the first_name column as follows:

<sql:result-set>
    <sql:row-iterator>
        <employee>
            <sql:get-columns format="xml">
                <sql:exclude>first_name</sql:exclude>
            </sql:get-columns>
        </employee>
    </sql:row-iterator>
</sql:result-set>

This generates the following results:

<employee>
    <last_name>Doe</last_name>
</employee>

<employee>
    <last_name>Smith</last_name>
</employee>

Result-set metadata

It is possible to retrieve result-set metadata. The following elements must be used within a sql:column-iterator element, unless a column-name or column-index attribute is explicitly specified:

sql:get-column-index: retrieves the current column index.
sql:get-column-name: retrieves the current column name.
sql:get-column-type: retrieves the current column type name as returned by result-set metadata.

Outputting attributes with sql:attribute

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

<my-element>
    <sql:attribute name="index">
        <sql:get-column-index/>
    </sql:attribute>
    ...
</my-element>

This will result in something like:

<my-element index="3">
    ...
</my-element>

Iterating over result-set columns with sql:column-iterator

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:

<sql:result-set>
    <metadata>
        <sql:column-iterator>
            <column>
                <sql:attribute name="index">
                    <sql:get-column-index/>
                </sql:attribute>
                <sql:attribute name="name">
                    <sql:get-column-name/>
                </sql:attribute>
                <sql:attribute name="type">
                    <sql:get-column-type/>
                </sql:attribute>
                <index><sql:get-column-index/></index>
                <name><sql:get-column-name/></name>
                <type><sql:get-column-type/></type>
            </column>
        </sql:column-iterator>
    </metadata>
</sql:result-set>

Reading and writing xml documents

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:

<sql:execute>
    <sql:update>
        insert into test_clob_table (clob_column)
        values (<sql:param select="/*" type="odt:xmlFragment" sql-type="clob"/>)
    </sql:update>
</sql:execute>

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:

<sql:execute>
    <sql:query>
        select clob_column from test_clob_table
    </sql:query>
    <sql:result-set>
        <rows>
            <sql:row-iterator>
                <row>
                    <sql:get-column-value type="odt:xmlFragment" column="clob_column"/>
                </row>
            </sql:row-iterator>
        </rows>
    </sql:result-set>
</sql:execute>

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:

<sql:execute>
    <sql:update>
        insert into test_xmltype_table (xmltype_column)
        values (<sql:param select="/*" type="odt:xmlFragment" sql-type="xmltype"/>)
    </sql:update>
</sql:execute>

NOTE:

The benefit of using the Oracle XMLType data type is that XML is stored in a structured way in the database. This allows creating indexes on XML data, doing partial document updates, etc. This however requires creating an XML schema. For more information, please refer to the Oracle XML DB Developer's Guide.

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

<sql:execute>
    <sql:query>
        select xmltype_column from test_xmltype_table
    </sql:query>
    <sql:result-set>
        <rows>
            <sql:row-iterator>
                <row>
                    <sql:get-column-value type="odt:xmlFragment" column="xmltype_column"/>
                </row>
            </sql:row-iterator>
        </rows>
    </sql:result-set>
</sql:execute>

WARNING:

Writing to CLOB columns, as well as writing and reading to and from XMLType columns, is currently only supported with the following application server / database combinations:

Tomcat 4.1 and Oracle 9
WebLogic 8.1 and Oracle 9

Please contact Orbeon to inquire about supporting additional systems.

Reading from CLOB columns on the other hand is supported with all JDBC drivers that support the CLOB API.

Specifying a sql type

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:

<sql:execute>
    <sql:update>
        insert into test_clob_table (clob_column)
        values (<sql:param select="/document/text" type="xs:string" sql-type="clob"/>)
    </sql:update>
</sql:execute>

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

<sql:execute>
    <sql:update>
        insert into test_table (varchar_column)
        values (<sql:param select="/document/text" type="xs:string" sql-type="varchar"/>)
    </sql:update>
</sql:execute>

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

<sql:execute>
    <sql:update>
        insert into test_table (varchar_column)
        values (<sql:param select="/document/text" type="xs:string"/>)
    </sql:update>
</sql:execute>

NOTE:

The disadvantage of using database columns of type varchar is that those are severely limited in size, for example 4000 bytes in the case of Oracle 9. The maximum size of CLOB columns is usually much larger, for example up to 4 GB with Oracle 9. In order to store large strings or large XML documents, it is therefore necessary to use the CLOB type.

The following values are supported for sql-type:

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

WARNING:

Using the clob and xmltype SQL types is currently only supported with the following application server / database combinations:

Tomcat 4.1 and Oracle 9
WebLogic 8.1 and Oracle 9

Please contact Orbeon to inquire about supporting additional systems.

Reading from CLOB columns on the other hand is supported with all JDBC drivers that support the CLOB API.

Reading and writing binary data

Reading and writing binary data is supported to and from database Binary Large OBjects (BLOBs) as well as binary types (BINARY, VARBINARYand 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:

<sql:execute>
    <sql:update>
        insert into test_blob_table (blob_column)
        values (<sql:param select="/*" type="xs:base64Binary"/>)
    </sql:update>
</sql:execute>

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:

<root>
/9j/4AAQSkZJRgABAQEBygHKAAD/2wBDAAQDAwQDAwQEBAQFBQQFBwsHBwYGBw4KCggLEA4R
...
KKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooA//2Q==
</root>

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


/9j/4AAQSkZJRgABAQEBygHKAAD/2wBDAAQDAwQDAwQEBAQFBQQFBwsHBwYGBw4KCggLEA4R
...
KKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooA//2Q==

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:

<sql:execute>
    <sql:update>
        insert into test_blob_table (blob_column)
        values (<sql:param select="/my/uri" type="xs:anyURI"/>)
    </sql:update>
</sql:execute>

NOTE:

XForms file uploads typically generate URLs in XForms instances if the type chosen for the uploaded file in the XForms model is xs:anyURI. The advantage of using xs:anyURI is that large resources do not have to reside entirely in memory.

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

<sql:execute>
    <sql:query>
        select blob_column from test_blob_table
    </sql:query>
    <sql:result-set>
        <rows>
            <sql:row-iterator>
                <row>
                    <sql:get-column-value type="xs:base64Binary" column="blob_column"/>
                </row>
            </sql:row-iterator>
        </rows>
    </sql:result-set>
</sql:execute>

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

<rows>
    <row>
/9j/4AAQSkZJRgABAQEBygHKAAD/2wBDAAQDAwQDAwQEBAQFBQQFBwsHBwYGBw4KCggLEA4R
...
KKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooA//2Q==
    </row>
</rows>

NOTE:

Base64-encoded binary documents are widely used in Orbeon Forms, in particular in the following cases:

Request generator: as the result of certain types of HTML form submissions (typically file uploads) or request body submission, Base64-encoded representation of the uploaded files may be stored into the request document.
XForms upload: as the result of a file upload, a Base64-encoded representation of the uploaded file may be stored into the XForms instance.
URL generator: can read binary documents and produce Base64-encoded output according to the standard binary document format.
HTTP serializer: can convert Base64-encoded input according to the binary document format into a binary stream.
SQL processor: as described in this document, it is able to read and write Base64-encoded binaries.

WARNING:

Writing to BLOB columns is currently only supported with the following application server / database combinations:

Tomcat 4.1 and Oracle 9
WebLogic 8.1 and Oracle 9

Please contact Orbeon to inquire about supporting additional systems.

Reading from BLOB columns on the other hand is supported with all JDBC drivers that support the BLOB API.

sql:value-of and sql:copy-of

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:

<sql:result-set>
    <sql:row-iterator>
        <employee>
            <position><sql:value-of select="sql:row-position()"/></position>
            <first-name><sql:get-column-value type="xs:string" column="first_name"/></first-name>
            <last-name><sql:get-column-value type="xs:string" column="last_name"/></last-name>
        </employee>
    </sql:row-iterator>
</sql:result-set>

This generates the following results:

<employee>
    <position>1</position>
    <first-name>John</first-name>
    <last-name>Doe</last-name>
</employee>

<employee>
    <position>2</position>
    <first-name>Billy</first-name>
    <last-name>Smith</last-name>
</employee>

Multiple updates

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:

<employees>
    <employee>
        <first-name>John</first-name>
        <last-name>Doe</last-name>
    </employee>
    <employee>
        <first-name>Billy</first-name>
        <last-name>Smith</last-name>
    </employee>
</employees>

The following update inserts two rows in the database:

<sql:update select="/employees/employee">
insert into employee (first_name, last_name)
       values (<sql:param type="xs:string" select="first-name"/>,
               <sql:param type="xs:string" select="last-name"/>)
</sql:update>

Nested queries

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

level1_id	value
1	a
2	b

level2

level2_id	level1_id	value
1	1	a
2	1	b
3	2	c
4	2	d

level3

level2_id	value
1	a
1	b
2	c
2	d
3	e
3	f
4	g
4	h

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

level1.value	level2.value	level3.value
a	a	a
a	a	b
a	b	c
a	b	d
b	c	e
b	c	f
b	d	g
b	d	h

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

level1.value	level2.value	level3.value
a	a	a
	a	b
	b	c
	b	d
b	c	e
	c	f
	d	g
	d	h

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

<result>
    <group1>
        <group1-header><level1-value>a</level1-value></group1-header>
        <group1-members>
            <group2>
                <group2-header><level2-value>a</level2-value></group2-header>
                    <group2-members>
                        <level3-value>a</level3-value>
                        <level3-value>b</level3-value>
                    </group2-members>
                <group2-footer><level2-value>a</level2-value></group2-footer>
            </group2>
            <group2>
                <group2-header><level2-value>b</level2-value></group2-header>
                    <group2-members>
                        <level3-value>c</level3-value>
                        <level3-value>d</level3-value>
                    </group2-members>
                <group2-footer><level2-value>b</level2-value></group2-footer>
            </group2>
        </group1-members>
        <group1-footer><level1-value>a</level1-value></group1-footer>
    </group1>
    <group1>
        <group1-header><level1-value>b</level1-value></group1-header>
        <group1-members>
            <group2>
                <group2-header><level2-value>c</level2-value></group2-header>
                    <group2-members>
                        <level3-value>e</level3-value>
                        <level3-value>f</level3-value>
                    </group2-members>
                <group2-footer><level2-value>c</level2-value></group2-footer>
            </group2>
            <group2>
                <group2-header><level2-value>d</level2-value></group2-header>
                    <group2-members>
                        <level3-value>g</level3-value>
                        <level3-value>h</level3-value>
                    </group2-members>
                <group2-footer><level2-value>d</level2-value></group2-footer>
            </group2>
        </group1-members>
        <group1-footer><level1-value>b</level1-value></group1-footer>
    </group1>
</result>

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:

<sql:config>
    <results>
        <sql:connection>
            <sql:datasource>my-datasource</sql:datasource>
            <sql:execute>
                <sql:query>
select level1.value value, level1.level1_id id
  from level1
 order by level1.value
                </sql:query>
                <sql:result-set>
                    <sql:row-iterator>
                        <group1>
                            <group1-header>
                                <level1-value><sql:get-column-value type="xs:string" column="value"/></level1-value>
                            </group1-header>
                            <group1-members>
                                <sql:execute>
                                    <sql:query>
select level2.value value, level2.level2_id id
  from level2
 where level2.level1_id = <sql:param type="xs:int"><sql:get-column-value type="xs:int" column="id"/></sql:param>
 order by level2.value
                                    </sql:query>
                                    <sql:result-set>
                                        <sql:row-iterator>
                                            <group2>
                                                <group2-header>
                                                    <level2-value><sql:get-column-value type="xs:string" column="value"/></level2-value>
                                                </group2-header>
                                                <group2-members>
                                                    <sql:execute>
                                                        <sql:query>
select level3.value value
  from level3
 where level3.level2_id = <sql:param type="xs:int"><sql:get-column-value type="xs:int" column="id"/></sql:param>
 order by level3.value
                                                        </sql:query>
                                                        <sql:result-set>
                                                            <sql:row-iterator>
                                                                <level3-value><sql:get-column-value type="xs:string" column="value"/></level3-value>
                                                            </sql:row-iterator>
                                                        </sql:result-set>
                                                    </sql:execute>
                                                </group2-members>
                                                <group2-footer>
                                                    <level2-value><sql:get-column-value type="xs:string" column="value"/></level2-value>
                                                </group2-footer>
                                            </group2>
                                        </sql:row-iterator>
                                    </sql:result-set>
                                </sql:execute>
                            </group1-members>
                            <group1-footer>
                                <level1-value><sql:get-column-value type="xs:string" column="value"/></level1-value>
                            </group1-footer>
                        </group1>
                    </sql:row-iterator>
                </sql:result-set>
            </sql:execute>
        </sql:connection>
    </results>
</sql:config>

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.

Grouping with sql:group and sql:member

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:

<sql:config>
    <results>
        <sql:connection>
            <sql:datasource>my-datasource</sql:datasource>
            <sql:execute>
                <sql:query>
select level1.value v1, level2.value v2, level3.value v3
  from level1, level2, level3
 where level1.level1_id = level2.level1_id
   and level2.level2_id = level3.level2_id
 order by level1.value, level2.value, level3.value
                </sql:query>
                <sql:result-set>
                    <sql:row-iterator>
                        <sql:group column="v1">
                            <group1>
                                <group1-header>
                                    <level1-value><sql:get-column-value type="xs:string" column="v1"/></level1-value>
                                </group1-header>
                                <group1-members>
                                    <sql:member>
                                        <sql:group column="v2">
                                            <group2>
                                                <group2-header>
                                                    <level2-value><sql:get-column-value type="xs:string" column="v2"/></level2-value>
                                                </group2-header>
                                                <group2-members>
                                                    <sql:member>
                                                        <level3-value><sql:get-column-value type="xs:string" column="v3"/></level3-value>
                                                    </sql:member>
                                                </group2-members>
                                                <group2-footer>
                                                    <level2-value><sql:get-column-value type="xs:string" column="v2"/></level2-value>
                                                </group2-footer>
                                            </group2>
                                        </sql:group>
                                    </sql:member>
                                </group1-members>
                                <group1-footer>
                                    <level1-value><sql:get-column-value type="xs:string" column="v1"/></level1-value>
                                </group1-footer>
                            </group1>
                        </sql:group>
                    </sql:row-iterator>
                </sql:result-set>
            </sql:execute>
        </sql:connection>
    </results>
</sql:config>

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.

Text and whitespace handling

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.

Transactions management

Orbeon Forms executes each HTTP request in its own transaction. If a request fails for any reason, the SQL Processor rolls back the transaction. The transaction is committed only when the pipeline execution in complete.

Site map

Comments? Feedback?

Recent site activity

Processors - SQL

Introduction

Inputs and outputs

Configuration template

Configuration

Root element

sql:execute

sql:query and sql:update

Logging

Calling stored procedure with sql:call

Handling result-sets with sql:result-set and sql:no-results

Getting column values

Retrieving single columns

Retrieving multiple columns

Result-set metadata

Outputting attributes with sql:attribute

Iterating over result-set columns with sql:column-iterator

Reading and writing xml documents

Specifying a sql type

Reading and writing binary data

sql:value-of and sql:copy-of

Multiple updates

Nested queries

Grouping with sql:group and sql:member

Text and whitespace handling

Transactions management