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>