sql:query

Executes a SQL query, returning the result as a map.

query($connection as javatype:java.sql.Connection, $table as xs:string, $columns as xs:string*) ➔ map(*)*

Arguments
	$connection	javatype:java.sql.Connection	A JDBC database connection established using sql:connect
	$table	xs:string	The name of a table in the database
	$columns	xs:string*	A list of required column names, or '*' to select all columns in the table
Result		map()

query($connection as javatype:java.sql.Connection, $table as xs:string, $columns as xs:string*, $predicate as xs:string) ➔ map(*)*

Arguments
	$connection	javatype:java.sql.Connection	A JDBC database connection established using sql:connect
	$table	xs:string	The name of a table in the database
	$columns	xs:string*	A list of required column names, or '*' to select all columns in the table
	$predicate	xs:string	A SQL expression suitable for inclusion in the WHERE clause
Result		map()

Namespace

http://saxon.sf.net/sql

Notes on the Saxon implementation

Introduced in Saxon 9.9. Designed to supersede the extension instruction sql:query. The saxon-sql-10.#.jar file, distributed alongside the main JAR file, must be added to the classpath when these SQL extension functions are used.

Details

The function executes a SQL query. For convenience the query is split across three arguments $table, $columns and $predicate but the function simply concatenates these together to form a SQL query which is then executed.

The 3-argument form of the function retrieves all the rows in a table (the effective predicate is TRUE).

The value returned by the function is a sequence of maps. Each row in the query result is represented by one map in this sequence. The map represents a row in the result using a string-valued key to represent the column name, and an appropriately-typed atomic value to represent the value of the column. A SQL NULL value is represented by an empty sequence.

Note that the result is a sequence rather than an array. It can be converted to an array (for example, for serializing in JSON) using the expression array{$result}.

Example:

<out> <xsl:for-each select="sql:query($connection, 'book', ('date', 'title'), 'author = ''J. K. Rowling''')"> <book date="{?date}" title="{?title}"/> </xsl:for-each> </out>

To avoid problems with nested quotation marks, you could also write:

<out> <xsl:variable name="condition" as="xs:string">author = 'J. K. Rowling'</xsl:variable> <xsl:for-each select="sql:query($connection, 'book', ('date', 'title'), $condition)"> <book date="{?date}" title="{?title}"/> </xsl:for-each> </out>