xdmp:sql

xdmp:sql(
   $sql as xs:string,
   [$options as xs:string*],
   [$bindings as map:map?],
   [$query as cts:query?]
) as item()*

Summary

Executes an ad hoc SQL query. This function is for testing your SQL views when data modeling.

Parameters
sql	The SQL statement to be executed.
options	Options as a sequence of string values. Available options are: "array" Return the result as a sequence of array values (`json:array`). "format" Return the results as formatted strings. "map" Return the result as a sequence of map values, where the key is the column name. "prepare" Parse and optimize the query, caching the result. No execution is performed. Default is false. "optimize=N" Sets the optimization level to use. Levels of 0 (off), 1, and 2 are recognized. The default is 1. "any" Values from any fragment should be included. "document" Values from document fragments should be included. "properties" Values from properties fragments should be included. "locks" Values from locks fragments should be included. "checked" Word positions should be checked when resolving the query. "unchecked" Word positions should not be checked when resolving the query.
$bindings	A map containing initial values for variables from the query, or the empty sequence if no query variables are to be initially bound. This is a way to parameterize the query. One of the benefits of parameterizing the query is that you can reuse the same query multiple times with different values plugged in. In this case, the SQL engine will not have to analyze the query each time it runs and instead use the cached query plan for increased speed. You should always using bindings, rather than string concatenation, to parameterize queries. As shown in the example below, bindings are passed in as a `map:map` of values. The keys for the map are either a string representation of the ordinal number of the `?` dynamic parameter in the SQL query, or the name of a named SQL parameter using the `:name`, `@name`, or `$name` syntax. Ordinal parameters can also be expressed as `?NNN`where `NNN` is an explicit ordinal number rather than an implicitly asigned one as with `?` by itself.
query	Only include triples in fragments selected by the `cts:query`. The triples do not need to match the query, but they must occur in fragments selected by the query. The fragments are not filtered to ensure they match the query, but instead selected in the same manner as "unfiltered" `cts:search` operations. If a string is entered, the string is treated as a `cts:word-query` of the specified string.

Required Privileges

http://marklogic.com/xdmp/privileges/xdmp-sql

Usage Notes

Only one of the "map" and "array" options may be specified. If neither is specified, the default is "array". If the "format" option is specified, the output will be formatted, regardless of whether "array" or "map" was selected.

The first tuple returned will always be one consisting of the column names.

Example

xdmp:sql("select title,author from books limit 4", "format")
==>
| title| author|
| The C++ Programming Language| Bjarne Stroustrup|
| Modern Information Retrieval| Ricardo Baeza-Yates|
| Modern Information Retrieval| Berthier Ribeiro-Neto|
| Unicode Demystified| Richard Gillam|

Example

xdmp:to-json(xdmp:sql("select title,author from books limit 4", "array"))
==>
[["title", "author"],
 ["The C++ Programming Language", "Bjarne Stroustrup"],
 ["Modern Information Retrieval", "Ricardo Baeza-Yates"],
 ["Modern Information Retrieval", "Berthier Ribeiro-Neto"],
 ["Unicode Demystified", "Richard Gillam"]
]

Example

for $row in xdmp:sql("select title,author from books limit 4")
return fn:concat("Title=",$row[1],"; Author=",$row[2])
==>
Title=title, Author=author
Title=The C++ Programming Language; Author=Bjarne Stroustrup
Title=Modern Information Retrieval; Author=Ricardo Baeza-Yates
Title=Modern Information Retrieval; Author=Berthier Ribeiro-Neto
Title=Unicode Demystified; Author=Richard Gillam

Example

let $bindings := map:new(map:entry("num", 1))
return (
xdmp:sql("SELECT * FROM main.employees
          where EmployeeID > @num
          order by LastName, EmployeeID",("format","locking=read-write"), $bindings))
==>
| main.employees.EmployeeID| main.employees.FirstName| main.employees.LastName| main.employees.Position|
| 4| Debbie| Goodall| Senior Widget Researcher|
| 4| Debbie| Goodall| Senior Widget Researcher|
| 2| Jane| Lead| Manager of Widget Research|
| 2| Jane| Lead| Manager of Widget Research|
| 3| Steve| Manager| Senior Technical Lead|
| 3| Steve| Manager| Senior Technical Lead|