Search code examples
sqlxqueryibm-integration-busextended-sql

What is the XQuery equivalent of the ESQL COALESCE function?

I'm trying convert WMB 7 mapping nodes to IIB 9 nodes. The auto-convert process turns some ESQL functions to XQuery functions.

Specifically, it turns the ESQL function 

COALESCE (var0, var1)

(which returns the first non-null value, as in if var0 = null then var1 else var0) into 

XQUERY (var0,var1)

  1. Is it a correct conversion?

  2. If it is, can someone provide a link to API? I couldn't find this on XQuery syntax and operators manuals.

Solution

  • XQuery is not an API, but a standard, and the full syntax can be found online: XQuery 1.0 and XQuery 3.0 (there is no 2.0). You'll also find many manuals, tutorials etc.

    XQuery relies on XPath, which is even wider used than XQuery and can be found in libraries for almost every general purpose language.

    Your expression is correct XQuery, in that it considers everything a sequence, and the comma concatenates (and flattens) two sequences.

    XPath does not know NULL, but it knows xsi:nil and (), the latter being the empty sequence. An empty sequence is removed from the result.

    I am not sure what XQuery processor is used underneath, but the correct expression should be ($var0, $var1)[1]2, which works the same way as your COALESCE operation1. In XPath and XQuery, variables are referenced with the $ sign. The number of variables or expressions separated by the comma is unbounded. If all are the empty sequence (null), the result is the empty sequence.

    Without [1], it will return all items that are non-null and discard the rest. You can use another index, like [3] to get the third non-null value. If no such value exists, it will return null (empty sequence).

    1 which behaves not exactly the way you described it. I believe it behaves like if var0 == null then var1 else var0, it selects the first non-null value (I've updated the OP).

    2 as Florent has explained in the comments, a warning with this expression is in place. If you have $var1 := (1, 2) and $var2 := (3, 4), the expression $var1, $var2)[1] will return 1, not (1, 2), because sequences cannot contain subsequences, and indexing a sequence with [x] will return the xth value of the flattened sequence. You can safe-guard your expression with (zero-or-one($var1), zero-or-one($var2))[1].