# XQuery and XQSE Developer’s Guide

# Oracle XQuery Scripting Extension (XQSE)

This chapter describes the Oracle XQuery Scripting Extension (XQSE) that enables you to add procedural constructs to XQuery-based data services. The chapter describes the language extensions and includes the statement grammar along with one or more examples.

This chapter includes the following topics:

Introducing the XQuery Scripting Extension Prolog and Query Body Procedure Declaration XQSE Function Declaration Value Statement and Procedure Call Block Set Statement | While Statement Return Statement Iterate Statement Try Statement If Statement Changed Element XQSE Grammar Summary

## Introducing the XQuery Scripting Extension

Oracle Data Service Integrator data services are based on the XQuery language, which enables you to use the structure of XML to express queries against data. The XQuery Scripting Extension builds on this base by adding procedural constructs, including basic statements, control flow, and user-defined procedures to XQuery.

XQSE is therefore a superset of XQuery, extending it with additional features that enable you to create richer and more complex data services while working within the context of XML and XQuery. You can think of XQSE as an extension to XQuery in the same way as Oracle PL/SQL is an extension of SQL.

## Prolog and Query Body

XQSE extends XQuery by adding procedural capabilities to the declarative query capabilities of XQuery. In XQuery, a query consists of a prolog followed by an XQuery expression. The prolog of a query sets up the environment for the expression by defining namespaces, external variables, and functions, among other information.

In XQSE, a prolog can also contain definitions of procedures and XQSE functions which are, respectively, side-effecting and non-side-effecting callable units of execution written in XQSE.

The following shows the grammar of the XQSE prolog and query body:

    Prolog ::= ((DefaultNamespaceDecl | Setter | NamespaceDecl | Import)
       Separator)* ((VarDecl | FunctionDecl | ProcedureDecl |
       XQSEFunctionDecl | OptionDecl) Separator)*

    QueryBody ::= Expr | Block

In XQSE, the body of a top-level query can be either an XQuery expression or an XQSE block. A block is a sequence of statements that are executed sequentially.

## Procedure Declaration

XQSE enables you to declare a procedure in the prolog of an Oracle Data Service Integrator data service. An XQSE procedure is similar to an XQuery function, but unlike a function, a procedure can have one or more side effects. Another difference is that XQuery functions are declarative; the body of an XQuery function is an XQuery expression.

The body of an XQSE procedure, in contrast, consists of a block, which is a list of statements executed in sequential order when the procedure is called. Alternatively, as in XQuery, you can declare a procedure as external, in which case it does not have a body and is implemented by Oracle Data Service Integrator by importing procedures from external data sources such as relational stored procedures or Web services.

The following shows the grammar of the XQSE procedure declaration:

    ProcedureDecl ::= "declare" "procedure" QName "(" ParamList? ")"
       ("as" SequenceType)? (Block | ("external") )

A procedure may, but is not required to, return a value. Individual XQSE statements do not have return values by themselves, so a procedure returns a value only when an explicit return statement is included in the body of procedure. If the return type of a procedure is not specified, its return value is of type `item()*` by default.

Note: | Since returning a value is optional, a return statement is not required in the body of a procedure. In the absence of a return statement, the return value of a procedure is the empty sequence and its return type is `empty()`.

You can use recursion in procedures. This is another difference between XQuery functions and XQSE procedures in Oracle Data Service Integrator.

Example:

    (: Procedure to delete an employee given just their employee ID :)
    (: Calls the default delete function on the data service after retrieving the employee object using the ID :)
    declare procedure tns:deleteByEmployeeID($id as xs:string?) as empty() {
       declare $emp as element(empl:Employee)? := tns:getByEmployeeID($id);
       tns:delete($emp);
    };

## XQSE Function Declaration

XQSE extends the XQuery function declaration syntax to enable you to declare XQSE-based functions in addition to procedures. An XQSE function is essentially a read-only procedure written in XQSE with no side effects.

As with a procedure, the body of an XQSE function consists of a block, which is a list of statements. The following shows the grammar of the XQSE function declaration:

    XQSEFunctionDecl ::= "declare" xqse "function" QName "(" ParamList? ")"
       ("as" SequenceType)? (Block | ("external") )

Since an XQSE function does not have any side-effects, you can call it from within an XQuery expression anywhere that a normal XQuery function can be called.

Since XQuery functions are declarative and therefore amenable to compile-time query optimization, you should write data service functions using XQuery instead of XQSE where possible. However, it is sometimes necessary (or at least conceptually more convenient) to express certain read-only computations procedurally. XQSE functions are appropriate in these cases.

For example, you could use an XQSE function to perform calculations that would otherwise require the use of tail recursion in XQuery. This is necessary since Oracle Data Service Integrator does not permit the use of recursion in XQuery functions.

Example:

    (: Procedure to compute the level of an employee in the org tree :)
    declare xqse function tns:distanceFromTop($id as xs:string?) as xs:integer? {
       declare $mgrCnt as xs:integer := 0;
       declare $curEmp as element(empl:Employee)? :=
          tns:getByEmployeeID($id);
       declare $mgrId as xs:string? := fn:data($curEmp/ManagerID);
       if (fn:empty($curEmp)) then return value ();
       while (fn:not(fn:empty($mgrId))) {
          set $mgrCnt := $mgrCnt + 1;
          set $curEmp := tns:getByEmployeeID($mgrId);
          set $mgrId  := fn:data($curEmp/ManagerID);
       };
       return value ($mgrCnt);
    };

## Value Statement and Procedure Call

XQSE offers the value statement and procedure call statement to distinguish between function and procedure calls.

Note: | You can call a function wherever an expression can be used, but procedures can only be called in certain parts of XQSE because they include side effects.

The following shows the grammar of the XQSE value statement and procedure call statement:

    ValueStatement := ExprSingle | ProcedureCall
    ProcedureCall ::= FunctionCall
    Statement := SimpleStatement | BlockStatement
    SimpleStatement ::= SetStatement | IfStatement | ReturnStatement |
       ProcedureCall

## Block

An XQSE block contains a list of statements. You can use a block to declare mutable variables (using declare clauses) and manipulate those variables in subsequent statements, which are executed in sequential order.

The following shows the grammar of the XQSE `block` statement:

    Block ::= {" ((BlockDecl ";")* StatementSequence "}"
    BlockDecl ::= "declare" "$" VarName TypeDeclaration?
       (":=" ValueStatement)?
    StatementSequence := ((SimpleStatement ";") | (BlockStatement (";")?))*
    BlockStatement := WhileStatement | IterateStatement | TryStatement |
       Block

While XQuery expressions have values, statements do not (with the exception of the `return` statement, described in Return Statement on page 6-7.). Therefore, a block does not have a return value since a block is itself a compound statement.

Every variable in a block must be declared before it can be used. If you declare a variable without explicitly specifying a type, the variable will have a default type of `item()*`.

You also need to initialize declared variables using a `set` statement or as part of the `BlockDecl` before they can be used. Referencing uninitialized variables (not appearing on the left hand side of an assignment statement) results in an error.

Note: | Variables in a block are mutable. Unlike `let` and `for` variables that appear in XQuery expressions, which are immutable bindings of names to values, variables in an XQSE block are assignable (similar to variables in Java and C++, among other languages).

You can define nested blocks, in which case regular scoping rules apply. For example, a variable with a specific fully-qualified name declared in an inner block will shadow (redefine and hide) variables in a containing outer block that has the identical fully-qualified name.

Example:

    (: Procedure to compute the level of an employee in the org tree :)
    declare xqse function tns:distanceFromTop($id as xs:string?)
       as xs:integer? {
       declare $mgrCnt as xs:integer := 0;
       declare $curEmp as element(empl:Employee)? :=
          tns:getByEmployeeID($id);
       declare $mgrId as xs:string? := fn:data($curEmp/ManagerID);
       if (fn:empty($curEmp)) then return value ();
       while (fn:not(fn:empty($mgrId))) {
          set $mgrCnt := $mgrCnt + 1;
          set $curEmp := tns:getByEmployeeID($mgrId);
          set $mgrId  := fn:data($curEmp/ManagerID);
       }
       return value ($mgrCnt);
    };

## Set Statement

The XQSE `set` statement sets the variable `VarName` to the value specified by `ValueStatement`. The following shows the grammar of the XQSE `set` statement:

    SetStatement ::= "set" "$" VarName ":=" ValueStatement

Before using the `set` statement, you must first declare the variable `VarName` using a `declare` statement. Only variables declared in this way are mutable and can therefore be changed using the `set` statement.

Note: | The `set` statement has copy semantics. Consider the following instance:

    set $z := ($x, $y)

If `$x` and `$y` are mutable variables and `$x` and `$y` are later changed, $z retains the originally copied values of `$x` and `$y`.

Example:

    (: Procedure to compute the level of an employee in the org tree :)
    declare xqse function tns:distanceFromTop($id as xs:string?)
          as xs:integer? {
       declare $mgrCnt as xs:integer := 0;
       declare $curEmp as element(empl:Employee)? :=
          tns:getByEmployeeID($id);
       declare $mgrId as xs:string? := fn:data($curEmp/ManagerID);
       if (fn:empty($curEmp)) then return value ();
       while (fn:not(fn:empty($mgrId))) {
          set $mgrCnt := $mgrCnt + 1;
          set $curEmp := tns:getByEmployeeID($mgrId);
          set $mgrId  := fn:data($curEmp/ManagerID);
       };
       return value ($mgrCnt);
    };

## While Statement

The XQSE `while` statement loops and performs the actions in the block while the effective boolean value of the condition evaluates to true. The following shows the grammar of the XQSE `while` statement:

    WhileStatement ::= "while" "(" Expr ")" Block

The while statement reevaluates the condition expression before each loop. Typically, the condition depends upon a mutable variable that is manipulated in the block. The loop therefore terminates when code in the block causes the effective boolean value of the condition to cease being true.

Example:

    (: Procedure to compute the level of an employee in the org tree :)
    declare xqse function tns:distanceFromTop($id as xs:string?)
          as xs:integer? {
       declare $mgrCnt as xs:integer := 0;
       declare $curEmp as element(empl:Employee)? :=
          tns:getByEmployeeID($id);
       declare $mgrId as xs:string? := fn:data($curEmp/ManagerID);
       if (fn:empty($curEmp)) then return value ();
       while (fn:not(fn:empty($mgrId))) {
          set $mgrCnt := $mgrCnt + 1;
          set $curEmp := tns:getByEmployeeID($mgrId);
          set $mgrId  := fn:data($curEmp/ManagerID);
       };
       return value ($mgrCnt);
    };

## Return Statement

The XQSE `return` statement computes the expression represented by `ValueStatement` and returns the resulting value while exiting from the current procedure.

The following shows the grammar of the XQSE `return` statement:

    ReturnStatement ::= "return" "value" ValueStatement

In the special case where a block containing a return statement is the body of the main query, the return statement returns the value to the invoking environment.

Example:

    (: Procedure to compute the level of an employee in the org tree :)
    declare xqse function tns:distanceFromTop($id as xs:string?)
          as xs:integer? {
       declare $mgrCnt as xs:integer := 0;
       declare $curEmp as element(empl:Employee)? :=
          tns:getByEmployeeID($id);
       declare $mgrId as xs:string? := fn:data($curEmp/ManagerID);
       if (fn:empty($curEmp)) then return value ();
       while (fn:not(fn:empty($mgrId))) {
          set $mgrCnt := $mgrCnt + 1;
          set $curEmp := tns:getByEmployeeID($mgrId);
          set $mgrId  := fn:data($curEmp/ManagerID);
       };
       return value ($mgrCnt);
    };

## Iterate Statement

XQSE offers an `iterate` statement that is equivalent to the XQuery `for` clause and enables you to perform data-driven looping over a block of XQSE statements. This enables you to iterate through the result of an XQuery expression, for example.

The following shows the grammar of the XQSE `iterate` statement:

    IterateStatement ::= "iterate" "$" VarName PositionalVar? "over"
       ValueStatement Block

The iteration variable `VarName` is bound to each item in the sequence produced by evaluating `ValueStatement`, which can be either an XQuery expression or a procedure call. Optionally, the `PositionalVar` variable represents the index of the current item in the sequence.

Note: | The `VarName` and `PositionalVar` variables are both mutable, though it is not advisable that you exploit this capability.

Examples:

    (: Procedure to allow only updates that don't violate the salary change business rules :)
    declare procedure tns:updateChecked($changedEmps as
          changed-element(empl:Employee)*) {
       iterate $sourceEmp over $changedEmps {
          if (tns:invalidSalaryChange($sourceEmp)) then
             fn:error(xs:QName("INVALID_SALARY_CHANGE"), ":
                Salary change exceeds the limit.");
       };
       tns:update($changedEmps);
    };

    (: Procedure to perform "lite ETL", copying and transforming data from one source to another :)
    declare procedure tns:copyAllToEMP2() as xs:integer {
       declare $backupCnt as xs:integer := 0;
       declare $emp2 as element(emp2:EMP2)?;
       iterate $emp1 over ens1:getAll() {
          set $emp2 := tns:transformToEMP2($emp1);
          emp2:createEMP2($emp2);
          set $backupCnt := $backupCnt + 1;
       }
       return value ($backupCnt);
    };

## Try Statement

The `try` statement enables you to perform procedural error handling in XQSE, such as those raised by the XQuery `fn:error` function. The `try` statement works in much the same way as traditional try/catch statements in languages such as Java or C++.

The following shows the grammar of the XQSE `try` statement:

    TryStatement ::= "try" Block CatchClauseStatement+

    CatchClauseStatement ::= catch "(" NameTest ("into" VarNameExpr (("," VarNameExpr)? "," VarNameExpr)?)? ")" Block

XQSE enables you to catch all XQuery errors. XQuery errors have an associated `QName`, enabling you to use the XQuery `NameTest` to restrict the errors handled by a specific `catch` clause. In addition, the following variables in the `catch` clause work similarly to the arguments of the XQuery `fn:error` function:

- `$error`—`xs:QName`
- `$description`—`xs:string`
- `$error-object`—`item()*`

Similar to exceptions in other languages, you can re-throw errors in XQSE using the `fn:error` function. When doing so, you need to ensure that all components of the error, including the name, description, and error-object, are properly passed to the new `fn:error` call.

Example:

    (: Procedure to create a replicated employee and return an appropriately specific error message if it fails :)
    declare procedure tns:create($newEmps as element(empl:Employee)*)
          as element(empl:ReplicatedEmployee_KEY)* {
       iterate $newEmp over $newEmps {
          declare $newEmp2 as element(emp2:EMP2)? :=
             bns:transformToEMP2($newEmp);
          try { tns:createEmployee($newEmp); }
          catch (* into $err, $msg) {
             fn:error(xs:QName("PRIMARY_CREATE_FAILURE"),
             fn:concat("Create failed on primary copy due to: ", $err,
                $msg));
          };

          try { emp2:createEMP2($newEmp2); }
          catch (* into $err, $msg) {
             fn:error(xs:QName("SECONDARY_CREATE_FAILURE"),
             fn:concat("Create failed on backup copy due to: ", $err,
                $msg));
          };
       }
    };

## If Statement

XQSE offers an `if` statement that is equivalent to the XQuery `IfExpr` expression. The XQSE `if` statement differs from the XQuery `IfExpr` expression in the following ways:

- The XQSE `if` statement is a control flow statement, and does not return a value.
- The else clause is optional in an XQSE `if` statement.

The following shows the grammar of the XQSE `if` statement:

    IfStatement ::= "if" "(" Expr ")" "then" Statement ("else" Statement)?

Example:

    (: Procedure to compute the level of an employee in the org tree :)
    declare xqse function tns:distanceFromTop($id as xs:string?)
          as xs:integer? {
       declare $mgrCnt as xs:integer := 0;
       declare $curEmp as element(empl:Employee)? :=
          tns:getByEmployeeID($id);
       declare $mgrId as xs:string? := fn:data($curEmp/ManagerID);
       if (fn:empty($curEmp)) then return value ();
       while (fn:not(fn:empty($mgrId))) {
          set $mgrCnt := $mgrCnt + 1;
          set $curEmp := tns:getByEmployeeID($mgrId);
          set $mgrId  := fn:data($curEmp/ManagerID);
       };
       return value ($mgrCnt);
    };

## Changed Element

The XQSE language extends the XQuery data model with information about elements that have been updated and resubmitted to Oracle Data Service Integrator. This enables XQSE to support SDO client updates along with associated server-side update logic.

An XML node that contains changes has the XQSE type `changed-element`. The following shows the grammar of the XQSE `changed-element` type:

    ItemType := AtomicType | KindTestType | <"item" "(" ")"> |
       ChangedElementType

    ChangedElementType := "changed-element" "(" ElementNameOrWildcard ")"

XQSE provides two built-in functions, `fn-bea:old-value` and `fn-bea:current-value`, to access the pre-update and post-update contents of the changed XML node respectively.

Note: | You can only pass instances of `changed-element` into XQSE as arguments to procedures and functions. Instances of `changed-element` cannot be created or incrementally modified within XQSE.

You can also use instances of `changed-element` in variable declarations and assignments.

Example:

    (: function to determine whether or not a given salary change is legal according to business rules :)
    declare function tns:invalidSalaryChange($emp as
          changed-element(empl:Employee)) as xs:boolean {
       let $newSalary := fn:data(fn-bea:current-value($emp)/Salary)
       let $oldSalary := fn:data(fn-bea:old-value($emp)/Salary)
       return (100.0 * fn:abs($newSalary - $oldSalary) div $oldSalary)
          gt 10.0
    };

## XQSE Grammar Summary

The following summarizes the XQSE grammar:

    Prolog ::= ((DefaultNamespaceDecl | Setter | NamespaceDecl | Import) Separator)*

    ((VarDecl | FunctionDecl | ProcedureDecl | XQSEFunctionDecl| OptionDecl) Separator)*

    XQSEFunctionDecl ::= "declare" xqse "function" QName "(" ParamList? ")" ("as" SequenceType)? (Block | ("external") )

    ProcedureDecl ::= "declare" "procedure" QName "(" ParamList? ")" ("as" SequenceType)? (Block | ("external") )

    QueryBody ::= Expr | Block

    Statement := SimpleStatement | BlockStatement

    SimpleStatement ::= SetStatement | IfStatement | ReturnStatement | ProcedureCall

    BlockStatement := WhileStatement | IterateStatement | TryStatement | Block

    ValueStatement := ExprSingle | ProcedureCall

    ReturnStatement ::= "return" "value" ValueStatement

    Block ::= {" ((BlockDecl ";")* StatementSequence "}"

    StatementSequence := ((SimpleStatement ";") | (BlockStatement (";")?))*

    BlockDecl ::= "declare" "$" VarName TypeDeclaration? (":=" ValueStatement)?

    SetStatement ::= "set" "$" VarName ":=" ValueStatement

    WhileStatement ::= "while" "(" Expr ")" Block

    IterateStatement ::= "iterate" "$" VarName PositionalVar? "over" ValueStatement Block

    ProcedureCall ::= FunctionCall

    TryStatement ::= "try" Block CatchClauseStatement+

    CatchClauseStatement ::= catch "(" NameTest ("into" VarNameExpr (("," VarNameExpr)? "," VarNameExpr)?)? ")" Block

    IfStatement ::= "if" "(" Expr ")" "then" Statement ("else" Statement)?

    ItemType := AtomicType | KindTestType | <"item" "(" ")"> | ChangedElementType

    ChangedElementType := "changed-element" "(" ElementNameOrWildcard ")"