• XQuery Cheatsheet
  • XQuery 3.1 BASICS
• Common Patterns
  • List of unique Values
  • Sorting
    • Sorted List of Unique Values (with a natural order)
    • Sorted Unique Values (standard sort order)
  • List Scripts containing a search term (in script tree order)
  • Filter results using RegEx

XQuery Cheatsheet

Lets get up to speed with XQuery!

XQuery is a stunningly powerful language to query and transform structured data XML data or even jSON.

It’s a full blown language with variables, functions, control structures, types and modules.

Whereas in FileMaker ‘everything is text’, in XQuery ‘everything is a sequence’.

AI produced, so no guarantees here!

XQuery 3.1 BASICS

Comments

(: comment :)

XPath

//BaseTable[UUID/@userName="R.Watson"]/@name // get name of all tables changed by R.Watson

XML

<BaseTable name="fmCheckMate" UUID="..." userName="R.Watson"/>

Strings

"hello" → hello // double quotes

'world' → world // single quotes

'hello "world"' → hello "world" // use what fits best

"MrWatson's ""world""" → MrWatson's "world" // double up to escape quote

'MrWatson''s "world"' → MrWatson's "world" // double up to escape quote

"MrWatson's "world"" → MrWatson's "world" // use character references!

"hello" || " " || "world" → hello world // String concatenation

'
'→ LF // literal newline character

codepoints-to-string(10)→ LF // generated newline character

"no\nbackslash\rescapes
 "XQuery"" → no\nbackslash\rescapes¶in "XQuery"

Sequences

(1,2,3) // a sequences is an ordered list of items

("hello","world") // of any type

(1,"hello",<xml>world</xml>) // of mixed types

1 to 5 → (1,2,3,4,5) // range = sequence of numbers

((1,2),("hello")) → (1,2,"hello") // sequences are always flat / flatened!

//Book → («Book1»,«Book2»,«Book3») // sequences are everywhere in XQuery!

(1,2,3) → 1 2 3 // sequences are ouput with spaces

("hello","world") → hello world

(<hello/>,<world/>) → <hello/><world/> // xml sequences are ouput without spaces `

(1,("hello"),<xml>world</xml>) → 1 helloworld // not the mixed delimiters!

(1)=1 → true // a single value in a sequence (a ‘singleton’) is equivalent to itself!

Filters

('one','two','three')[2] → two // [n] = a simple positional filter (1-based)

(1 to 10)[. mod 2 = 0] → (2,4,6,8,10) // filter can be a function (use . to reference item)

Types

xs:string xs:int xs:dateTime node() item()

Operators…

…Maths

+ - * div idiv mod

…Comparison

= != < > <= >= (value)

…General operators

eq ne lt gt

…Logical operators

and or not()

…Concatenation ops

|| string concat, , sequence concat

…Bang operators

(1,2) ! (. * 2)

…Arrow operators

"Hello" => upper-case() => substring(1,3)

…Set operators

union intersect except

Note: these remove duplicates and sort the result!

(3,2,1,2) union () → (1,2,3)

Control Structures…:

…let

let $hello := "world" return $hello → world

let $x := 42 return $x*2 → 84

let $xml := <value>some</value> return $xml/value → some

let $hello := "world" return <hello>{$hello}</hello> → <hello>world</hello> // use {} inside XML to refer to vars/switch back to XQuery

F or

L et

W here

O rder by

R eturn

…FLWOR

for $i in 1 to 5 let $sq := $i*$i where $sq > 10 order by $sq return $sq → (16,25)

…Conditionals

if ($x > 0) then "positive" else "non-positive"

…Switch

switch($day) case "Sat" return "weekend" "Sun" return "weekend" default return "weekday"

…Typeswitch

typeswitch($x) case xs:string return "string" case xs:integer return "integer" default return "other"

Note: Typeswitch first becomes really useful with schema-aware XQuery (more expensive licence)

…Mapping

(1,2,3) ! (. * 2) → (2,4,6)

…Arrow

"Hello World" => substring(1,5) => upper-case() → HELLO

…Predicates

(//book)[2] → second book element

(1 to 10)[. mod 2 = 0] → (2,4,6,8,10)

("A","B","C","D","E","F","G","H","I")[position() = (1,3,5,9)] → (A,C,E,I)

…Quantifiers

some $n in 1 to 10 satisfies $n mod 2 = 0 → true

every $n in 1 to 10 satisfies $n > 0 → true

Try/Catch

try { 1 div 0 } catch * { "division by zero" } → division by zero

Built-ins

concat(), substring(), upper-case()

count(), empty(), distinct-values()

sum(), avg(), min(), max()

Constructors

element(), attribute(), text(), comment(), document(), processing-instruction()`

Functions

declare function local:f($x){$x*$x};

Modules

module namespace m="http://ex";

declare function m:f($x){$x*$x};

import module namespace m="http://ex" at "m.xqm";

Maps/Arrays

map{"a":1,"b":2} => map:get("a")

["x","y"][2]

Higher-order

(1 to 5)! (function($n){$n*2})

text() vs data()

<a>hi<b>yo</b></a>/text() → “hi”

data(<a year="2025">42</a>/@year) → “2025”

Common Patterns

List of unique Values

List of Variable names used in a file

distinct-values(//Step[@name='Set Variable']/Name)
  => string-join("
")

Sorting

Sorting in XQuery 3.1 is a bit tricky

• The default sort order is not so useful (unicode codepoint order)
  • You can remedy this by using declare default collation (see below)
• The order by clause requires a collation «url» clause (which is a bit unwieldly)
• moreover it only supports a static url and thus is not replaceable by a short variable.
• On the other hand, the sort function does support dynamic collations.
• Happily this is all fixed in XQuery 4.0! 😃

Sorted List of Unique Values (with a natural order)

Sorted list of Variable names used in a file (with a natural order)

declare default collation "http://www.w3.org/2005/xpath-functions/collation/html-ascii-case-insensitive";
for $name in distinct-values(//Step[@name='Set Variable']/Name)
order by $name
return $name
  => string-join("
")

returns

$aardvark
$Apple
$banana
$Cat
$dog

Sorted Unique Values (standard sort order)

• For more natural sorting you need to use a collation

for $name in distinct-values(//Step[@name='Set Variable']/Name)
order by $name
return $name
  => string-join("
")

returns

$Apple
$Cat
$aardvark
$banana
$dog

List Scripts containing a search term (in script tree order)

(
let $search := 'Set'
for $script in //Script
let $script_name := $script/data(@name)
where contains($script,$search)
return $script_name
) => string-join('
')

Filter results using RegEx

• the filter function applies a function to each item in the sequence and if the function returns true the item is kept.
• the match function tests a regular expression and returns true if it matches.

List Variable names used in a file, sorted, filtered to only those containing ‘Anzahl’ or ‘count’ (case insensitive), but not account.

(
for $name in distinct-values(//Step[@name='Set Variable']/Name)
order by $name
return $name
)
=> filter(matches(?, '([Aa]nzahl|(C|[^c]c)ount)'))  (: keep only matching results :)
=> string-join('
')