xdmp.sql( sql as String, [options as String[]], [bindings as Object?], [query as cts.query?] ) as Sequence
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:
|
bindings |
An object 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 an |
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.
|
http://marklogic.com/xdmp/privileges/xdmp-sql
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.
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|
xdmp.toJSON(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"] ]
var res = new Array(); for (var row of xdmp.sql("select title,author from books limit 4")) { res.push(fn.concat("Title=", row[0],"; Author=", row[1])); }; ==> 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
'use strict' var bindings = {"num": 1} ; 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|
Stack Overflow: Get the most useful answers to questions from the MarkLogic community, or ask your own question.