Loading TOC...
JavaScript xdmp.sql

xdmp:sql

xdmp:sql(
   $sql as xs:string,
   [$options as xs:string*]
) as item()*

Summary

Executes an ad hoc SQL query. This function is for testing your SQL views when data modeling; it is not intended to be used directly in applications.

Parameters
sql The SQL statement to be executed.
options Options. 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.

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

xdmp:to-json(xdmp:sql("select title,author from books limit 4", "map"))
==>
[
 {"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

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

Stack Overflow iconStack Overflow: Get the most useful answers to questions from the MarkLogic community, or ask your own question.