|
|
The World Wide Web Consortium (W3C) defines a set of language features and functions for XQuery. The Oracle XQuery engine fully supports these language features with one exception (modules) and also supports a robust subset of functions and adds a number of implementation-specific functions and language keywords.
This chapter describes the function and language implementation and extensions in the XQuery engine.
The chapter includes the following topics:
Oracle Data Service Integrator supports the W3C Working Draft “XQuery 1.0 and XPath 2.0 Functions and Operators” dated 23 July 2004 (http://www.w3.org/TR/2004/WD-xpath-functions-
20040723/). In addition, Oracle Data Service Integrator supports a number of functions that are enhancements to the XQuery specification, which you can recognize by their extended function prefix fn-bea:. For example, the full XQuery notation for an extended function is: fn-bea:function_name.
This section describes the Oracle XQuery function extensions, and contains the following topics:
Table 2-1 provides an overview of the Oracle XQuery function extensions.
Returns either the full result of the primary expression, or the full result of the alternate expression in cases when the primary XQuery expression fails.
|
||
Oracle Data Service Integrator uses the role-base security policies of the underlying WebLogic platform to control access to data resources. A security policy is a condition that must be met for a secured resource to be accessed. If the outcome of condition evaluation is false — given the policy, requested resource, and user context — access to the resource is blocked and associated data is not returned.
Once the security policies have been configured using the Oracle Data Service Integrator Administration Console, you can use the security function extensions described in this section to determine:
This section describes the following Oracle Data Service Integrator access control function extensions to the Oracle implementation of XQuery:
The fn-bea:is-access-allowed() function checks whether a user associated with the current request context can access the specified resource, which is denoted by a resource name and a data service identifier. The function has the following signature:
fn-bea:is-access-allowed($resource as xs:string, $data\service as xs:string) as xs:boolean
where $resource is the name of the resource, and $dataservice is the resource identifier.
This function makes a call to the WebLogic security framework to check access for the specified resource. An example is shown below.
if (fn-bea:is-access-allowed("CustomerProfile/ssn",
"ld:DataServices/CustomerProfile.ds"))
then fn:true()
The fn-bea:is-user-in-group() function checks whether the current user is in the specified group. This function analyzes the WebLogic authenticated subject for appropriate group membership.
This function has the following signature:
fn-bea:is-user-in-group($group as xs:string) as xs:boolean
where $group is the group to test against the current user.
| Note: | This operation is not automatically authenticated. |
The fn-bea:is-user-in-role() function checks whether the current user is in the specified global role. This function obtains a list of roles from the WebLogic security framework.
The function has the following signature:
fn-bea:is-user-in-role($role as xs:string) as xs:boolean
where $role is the role to test against the current user.
| Note: | This operation is not automatically authenticated. |
The fn-bea:userid() function returns the identifier of the user making the request for the protected resource.
The function has the following signature:
This section describes the following duration, date, and time function extensions to the Oracle implementation of XQuery:
The fn-bea:date-from-dateTime() function converts a dateTime to a date, and returns the date part of the dateTime value.
The function has the following signature:
fn-bea:date-from-dateTime($dateTime as xs:dateTime?) as xs:date?
where $dateTime is the date and time.
The fn-bea:date-from-string-with-format() function returns a new date value from a string source value according to the specified pattern.
The function has the following signature:
fn-bea:date-from-string-with-format($format as xs:string?, $dateString as xs:string?) as xs:date?
where $format is the pattern and $dateString is the date. For more information about specifying patterns, see Date and Time Patterns.
fn-bea:date-from-string-with-format("yyyy-MM-dd G", "2005-06-22 AD") returns the specified date in the current time zone.fn-bea:date-from-string-with-format("yyyy-MM-dd", "2002-July-22") generates an error because the date string does not match the specified format.fn-bea:date-from-string-with-format(“yyyy-MMM-dd”, “2005-JUL-22”) returns the specified date in the current time zone.
The fn-bea:date-to-string-with-format() function returns a date string with the specified pattern.
The function has the following signature:
fn-bea:date-to-string-with-format($format as xs:string?, $date as xs:date?) as xs:string?
where $format is the pattern and $date is the date. For more information about specifying patterns, see Date and Time Patterns.
The fn-bea:dateTime-from-string-with-format() function returns a new dateTime value from a string source value according to the specified pattern.
The function has the following signature:
fn-bea:dateTime-from-string-with-format($format as xs:string?, $dateTimeString as xs:string?) as xs:dateTime?
where $format is the pattern and $dateTimeString is the date and time. For more information about specifying patterns, see Date and Time Patterns.
fn-bea:dateTime-from-string-with-format("yyyy-MM-dd G", "2005-06-22 AD") returns the specified date, 12:00:00AM in the current time zone.fn-bea:dateTime-from-string-with-format("yyyy-MM-dd 'at' hh:mm", "2005-06-22 at 11:04") returns the specified date, 11:04:00AM in the current time zone.fn-bea:dateTime-from-string-with-format("yyyy-MM-dd", "2005-July-22") generates an error because the date string does not match the specified format.fn-bea:dateTime-from-string-with-format(“yyyy-MMM-dd”, “2005-JUL-22”) returns 12:00:00AM in the current time zone.
The fn-bea:dateTime-to-string-with-format() function returns a date and time string with the specified pattern.
The function has the following signature:
fn-bea:dateTime-to-string-with-format($format as xs:string?, $dateTime as xs:dateTime?) as xs:string?
where $format is the pattern and $dateTime is the date and time. For more information about specifying patterns, see Date and Time Patterns.
The fn-bea:time-from-dateTime() function returns the time from a dateTime value.
The function has the following signature:
fn-bea:time-from-dateTime($dateTime as xs:dateTime?) as xs:time?
where $dateTime is the date and time.
The fn-bea:time-from-string-with-format() function returns a new time value from a string source value according to the specified pattern.
The function has the following signature:
fn-bea:time-from-string-with-format($format as xs:string?, $timeString as xs:string?) as xs:time?
where $format is the pattern and $timeString is the time. For more information about specifying patterns, see Date and Time Patterns.
The fn-bea:time-to-string-with-format() function returns a time string with the specified pattern.
The function has the following signature:
fn-bea:time-to-string-with-format($format as xs:string?, $time as xs:time?) as xs:string?
where $format is the pattern and $time is the time. For more information about specifying patterns, see Date and Time Patterns.
You can construct date and time patterns using standard Java class symbols. Table 2-2 outlines the pattern symbols you can use.
Repeat each symbol to match the maximum number of characters required to represent the actual value. For example, to represent 4 July 2002, the pattern is d MMMM yyyy. To represent 12:43 PM, the pattern is hh:mm a.
This section describes the following Oracle Data Service Integrator execution control function extensions to the Oracle implementation of XQuery:
The fn-bea:async() function evaluates an XQuery expression asynchronously, using a buffer to control data flow between threads of execution.
The function has the following signature:
fn-bea:async($expression as item()*) as item()*
where $expression is the XQuery expression to evaluate asynchronously.
The fn-bea:async function enables asynchronous execution of Web services to reduce problems caused by the latency of these services.
| Note: | Asynchronous web services do not propagate the transaction context to other threads, regardless of the transaction settings. Asynchronous operations are likewise unable to start new transactions. |
In the following example, CUSTOMER is a database table while the getCreditScore functions are Web services offered by two credit rating agencies.
for $cust in db:CUSTOMER()
where $cust/ID eq $param
return
let $score1:= fn-bea:async(exper:getCreditScore($cust/SSN), 2),
$score2:= fn-bea:async(equi:getCreditScore($cust/SSN), 2)
return
if (fn:abs($score1 - $score2) < $threshold)
then fn:avg(($score1, $score2))
else fn:max(($score1, $score2))
The fn-bea:fence() function enables you to define optimization boundaries, dividing queries into islands within which optimizations should occur while preventing optimizations across boundaries. You might consider using the fn-bea:fence function when building a query incrementally.
The function has the following signature:
fn-bea:fence($expression as item()*) as item()*
where $expression is the input expression.
The fn-bea:fence function is a pass-through function that does not change the input stream, but indicates to the optimizer that global rewritings should not occur across itself. Specifically, the fn-bea:fence function stops the following rewritings: view unfolding, loop unrolling, constant folding, and Boolean optimizations.
The timeout functions return either of the following:
Timeout functions are designed to be highly configurable. In the case of an error condition, the function can return either a single $alt expression or it can return more detailed information as $timeout and $failure.
The difference between the two functions fn-bea-timeout( ) and fn-bea-timeout-with-label( ) is that the latter returns $label along with other auditing information when an error condition is encountered.
The fn-bea:timeout() function has the following signature:
fn-bea:timeout($seq as item()*,
$millisec as xs:integer,
$timeout as item()*,
$failure as item()*) as item()*
where $seq is the primary XQuery expression to evaluate, $millisec is the timeout value in milliseconds, $timeout is returned if the evaluation of $seq takes more than $millis milliseconds to execute. $failure is returned if the evaluation of $seq raises an error.
Alternatively, you can replace the $timeout and $failure parameters with a single $alt parameter. The result of $alt will then be returned if a timeout or other error occurs.
The fn-bea:timeout-with-label() function has the following signature:
fn-bea:timeout-with-label($seq as item()*,
$millisec as xs:integer,
$timeout as item()*
$failure as item(),
$label as xs:string) as item()*
where $label represents information provided to the audit record.
Both functions return the result of evaluating $seq if the evaluation of $seq:
If an error does occur or the millisecond limit is exceeded, the alternate expression is returned along with the audit record.
If the evaluation of $millis or $alt raises an error, the error is reported in the usual way. That is, neither of the functions attempts to handle the returned error.
If — for a specific instance of one of these functions in a query — the evaluation of $seq raises an error or “times out”, all subsequent evaluations of this instance during the same query evaluation will return $timeout and $failure (or $alt). No attempt to re-evaluate $seq is made in such a case.
You can use the timeout functions in the following ways:
Note that the timeout functions immediately return the alternative expression in cases when accessing the data source causes an error.
Here is an example where $param is a external parameter:
for $cust in db:CUSTOMER()
where $cust/ID eq $param
return
fn-bea:timeout(exper:getCreditScore($cust/SSN), 200,
fn-bea:timeout(equi:getCreditScore($cust/SSN), 200,
fn:error()
)
)
The fn:bea:fail-over() and fn:bea:fail-over-with-label() functions return the result of evaluating $seq if the evaluation of $seq does not raise an exception. If it does raise an exception, $alt is returned. Both functions are polymorphic and their static return type is the union of the static types of $seq and $alt.
The functions have the following signatures:
fn-bea:fail-over($seq as item()*,
$alt as item()*) as item()*
fn-bea:fail-over-with-label($seq as item()*,
$alt as item()*,
$label as xs:string) as item()*
If $alt is returned the audit record contains:
If the evaluation of $seq raises an exception, all subsequent evaluations of this instance during the same query evaluation will return $alt. No attempt to re-evaluate $seq is made. If the evaluation of $alt raises an exception, it is simply reported. No attempt is made to handle the error.
The fn:bea:fail-over-retry and fn:bea:fail-over-retry-with-label functions return the result of evaluating $seq if the evaluation of $seq does not raise an exception. If it does raise an exception, $alt is returned.
In contrast to the fn:bea:fail-over and fn:bea:fail-over-with-label functions, however, the fn:bea:fail-over-retry and fn:bea:fail-over-retry-with-label functions re-evaluate $seq for each subsequent evaluation even if the evaluation of $seq raises an error.
The fn:bea:fail-over-retry and fn:bea:fail-over-retry-with-label functions have the following signatures:
fn-bea:fail-over-retry($seq as item()*,
$alt as item()*) as item()*
fn-bea:fail-over-retry-with-label($seq as item()*,
$alt as item()*,
$label as xs:string) as item()*
The fn-bea:fail-over( ) functions can be used in two ways:
This section describes the following numeric function extensions to the Oracle implementation of XQuery:
The fn-bea:format-number() function converts a double to a string using the specified format pattern.
The function has the following signature:
fn-bea:format-number($number as xs:double, $pattern as xs:string) as xs:string
where $number represents the double number to be converted to a string, and $pattern represents the pattern string. The format of this pattern is specified by the JDK 1.5.0 DecimalFormat class. (For information on DecimalFormat and other JDK 1.5.0 Java classes see:
http://java.sun.com/j2se/1.5.0.)
The fn-bea:decimal-round() function returns a decimal value rounded to the specified precision (scale) or to the nearest whole number.
The function has the following signatures:
fn-bea:decimal-round($value as xs:decimal?, $scale as xs:integer?) as xs:decimal?
fn-bea:decimal-round($value as xs:decimal?) as xs:decimal?
where $value is the decimal value to round and $scale is the precision with which to round the decimal input. A scale value of 1 rounds the input to tenths, a scale value of 2 rounds it to hundreths, and so on.
The fn-bea:decimal-truncate() function returns a decimal value truncated to the specified precision (scale) or to the nearest whole number.
The function has the following signatures:
fn-bea:decimal-truncate($value as xs:decimal?, $scale as xs:integer?) as xs:decimal?
fn-bea:decimal-truncate($value as xs:decimal?) as xs:decimal?
where $value is the decimal value to truncate and $scale is the precision with which to truncate the decimal input. A scale value of 1 truncates the input to tenths, a scale value of 2 truncates it to hundreths, and so on.
This section describes the following function extensions to the Oracle implementation of XQuery:
The fn-bea:get-property() function enables you to write data services that can change behavior based on external influence. This is an implicit way to parameterize functions.
The function first checks whether the property has been defined using the Oracle Data Service Integrator Administration Console. If so, it returns this value as a string. In cases when the property is not defined, the function returns the default value.
The function has the following signature:
fn-bea:get-property($propertyName as xs:string, $defaultValue as xs:string) as xs:string
where $propertyName is the name of the property, and $defaultValue is the default value returned by the function.
The fn-bea:inlinedXML() function parses textual XML and returns an instance of the XQuery 1.0 Data Model.
The function has the following signature:
fn-bea:inlinedXML($text as xs:string) as node()*
where $text is the textual XML to parse.
The fn-bea:rename() function renames an element or a sequence of elements.
The function has the following signature:
fn-bea:rename($oldelements as element()*, $newname as element()) as element()*)
where $oldelements is the sequence of elements to rename, and $newname is an element from which the new name and type are extracted.
For each element in the original sequence, the fn-bea:rename function returns a new element with the following:
for $c in CUSTOMER()
return
<CUSTOMER>
{fn-bea:rename($c/FIRST_NAME, <FNAME/>)}
{fn-bea:rename($c/LAST_NAME, <LNAME/>)}
</CUSTOMER>
In the above, if CUSTOMER() returns:
<CUST><FIRST_NAME>John</FIRST_NAME><LAST_NAME>Jones</LAST_NAME></CUST>
<CUSTOMER><FNAME>John</FNAME><LNAME>Jones</LNAME></CUSTOMER>
This section describes the following QName function extensions to the Oracle implementation of XQuery:
The fn-bea:QName-from-string() function creates an xs:QName and uses the value of $param as its local name without a namespace.
The function has the following signature:
fn-bea:QName-from-string($name as xs:string) as xs:QName
where $name is the local name.
This section describes the following sequence function extensions to the Oracle implementation of XQuery:
The fn-bea:interleave() function interleaves the specified arguments. The function has the following signature:
fn-bea:interleave($item1 as item()*, $item2 as xdt:anyAtomicType) as item()*
where $item1 and $item2 are the items to interleave.
For example, fn-bea:interleave((<a/>, <b/>, </c>), " ") returns the following sequence:
This section describes the following string function extensions to the Oracle implementation of XQuery:
The fn-bea:match() function returns a list of two integers specifying the characters in the string input that match the input regular expression (or an empty list, if none found). When the function returns a match, the first integer represents the index of (the position of) the first character of the matching substring and the second integer represents the number of matching characters. The function has the following signature:
fn-bea:match($source as xs:string?, $regularExp as xs:string?) as xs:int*
where $source is the input string and $regularExp uses is the regular expression.
Regular expression use standard java.util.regex.Pattern class patterns. Currently the following link to regular expression constructs is valid:
The fn-bea:sql-like() function tests whether a string contains the specified pattern. Typically, you can use this function as a condition for a query, similar to the SQL LIKE operator used in a predicate of SQL queries. The function returns TRUE if the pattern is matched in the source expression; otherwise the function returns FALSE.
The function has the following signatures:
fn-bea:sql-like($source as xs:string?, $pattern as xs:string?, $escape as xs:string?) as xs:boolean?
fn-bea:sql-like($source as xs:string?, $pattern as xs:string?) as xs:boolean?
where $source is the string to search, $pattern is the pattern specified using the syntax of the SQL LIKE clause, and $escape is the character to use to escape a wildcard character in the pattern.
You can use the following wildcard characters to specify the pattern:
You can include the “%” or “_” character in the pattern by specifying an escape character and preceding the “%” or “_” character in the pattern with this character. The function then reads the character literally, instead of interpreting it as a special pattern-matching character.
The $escape character has to be exactly one character in length and cannot be either the percent (“%”) or underscore (“_”) character.
fn-bea:sql-like($RTL_CUSTOMER.ADDRESS_1/FIRST_NAME,"H%","\") returns TRUE for all FIRST_NAME elements in $RTL_CUSTOMER.ADDRESS that start with the character H.fn-bea:sql-like($RTL_CUSTOMER.ADDRESS_1/FIRST_NAME,"_a%","\") returns TRUE for all FIRST_NAME elements in $RTL_CUSTOMER.ADDRESS that start with any character and have a second character of the letter a.fn-bea:sql-like($RTL_CUSTOMER.ADDRESS_1/FIRST_NAME,"H\%%","\") returns TRUE for all FIRST_NAME elements in $RTL_CUSTOMER.ADDRESS that start with the characters H%.
The fn-bea:trim() function removes the leading and trailing white space.
The function has the following signature:
fn-bea:trim($source as xs:string?) as xs:string?
where $source is the string to trim. In cases when $source is an empty sequence, the function returns an empty sequence. Oracle Data Service Integrator generates an error when the parameter is not a string.
The fn-bea:trim-left() function removes the leading white space.
The function has the following signature:
fn-bea:trim-left($input as xs:string?) as xs:string?
where $input is the string to trim.
The fn-bea:trim-right() function removes the trailing white space.
This function has the following signature:
fn-bea:trim-right($input as xs:string?) as xs:string?
where $input is the string to trim.
The fn-bea:pad-left() functions add padding characters to the left of a string to create a fixed-length string. There are two variations of the function:
If the input string exceeds the requested length, only a substring as long as the length is returned.
The function has the following signature:
fn-bea:pad-left($str as xs:string?, $size as xs:integer?) as xs:string?
where string ($str) is returned with a specified number ($size) of characters (ASCII 32) prepended to the left of the string. The result is a string of length $size. It consists of $str prepended with $size - fn:length($str) space characters.
fn-bea:pad-left(“abcd”, 6) prepends spaces to the string up to the maximum 6 specified. The returned string is: “ abcd”.fn-bea:pad-left(“abcd”, 2) returns only “ab” because characters are only prepended to the complete string. In addition, only the first two characters are returned since that is the setting of $size.This function has the following signature:
fn-bea:pad-left($str as xs:string?, $size as xs:integer?, $pad as xs:string?) as xs:string?
where string ($str) is returned with an arbitrary number ($size) of prepended characters with the pad string ($pad) replicated as many times as necessary.
fn-bea:pad-left(“abcd”, 6, “01”) prepends a pad string to the string up to the maximum 6 specified. The returned string is: “01abcd”.fn-bea:pad-left(“abcd”, 2, “01”) returns only “ab” because characters are only prepended to a complete string. In addition, only the first two characters are returned since that is the setting of $size.fn-bea:pad-left(“abc”, 6, “01”) returns “010abc”. Note that the prepended string is returned completely once and then partially up to the length ($size) specified.
The fn-bea:pad-right() functions add padding characters to the right of a string to create a fixed-length string. There are two variations of the function:
If the input string exceeds the requested length, only a substring as long as the length is returned.
The function has the following signature:
fn-bea:pad-right($str as xs:string?, $size as xs:integer?) as xs:string?
where string ($str) is returned with a specified number ($size) of characters (ASCII 32) appended to the string. The result is a string of length $size. It consists of $str appended with $size - fn:length($str) space characters.
fn-bea:pad-right(“abcd”, 6) appends spaces to the string up to the maximum 6 specified. The returned string is: “abcd ”.fn-bea:pad-right(“abcd”, 2) returns only “ab” because characters are only appended to a complete string. In addition, only the first two characters are returned since that is the setting of $size.This function has the following signature:
fn-bea:pad-right($str as xs:string?, $size as xs:integer?, $pad as xs:string?) as xs:string?
where string ($str) is returned with an arbitrary number ($size) of appended characters with the pad string ($pad) replicated as many times as necessary.
fn-bea:pad-right(“abcd”, 6, “01”) prepends a pad string to the string up to the maximum 6 specified. The returned string is: “abcd01”.fn-bea:pad-right(“abcd”, 2, “01”) returns only “ab” because characters are only appended to a complete string. In addition, only the first two characters are returned since that is the setting of $size.fn-bea:pad-right(“abc”, 6, “01”) returns only “abc010”. Note that the appended string is returned completely once and then partially up to the length ($size) specified.Oracle Data Service Integrator includes functions to support the Extended XQuery Data Model (XXDM). The XXDM represents instances of the XQuery Data Model (XDM) along with information about changes to the instances.
This section describes functions that you can use to convert XXDM instances to XDM instances.
The fn-bea:current-value() function returns an XDM instance representing the current value of the specified argument (discarding information about applied changes).
The function has the following signature:
fn-bea:current-value($changed as changed-element()) as element()?
where $changed is the XXDM instance.
The fn-bea:old-value() function returns an XDM instance representing the value of the specified argument prior to modification.
The function has the following signature:
fn-bea:old-value($changed as changed-element()) as element()?
where $changed is the XXDM instance.
Both the fn-bea:current-value and fn-bea:old-value functions are polymorphic.
The following function returns the salary difference for a customer before and after modification.
declare function salaryDifference($cus as changed-element
(cus:customer)) as xs:decimal {
fn:data(fn-bea:get-current-value($cus)/salary - fn:data(fn-
bea:get-old-value($cus)/salary)
}
The function does this by accessing the current and old versions of the customer element, extracting the salaries, and subtracting to determine the difference.
Oracle Data Service Integrator offers several built-in XQuery functions that are useful for manipulating and applying changes to XML element instances. Using these new mutator functions, it is possible to create update data service operations that take as input scalar values that represent changes to be applied to data.
For example, you could create an update data service operation called updatePhoneNumber() which takes a string value containing the new phone number to be updated for a customer data service. In previous versions of Oracle Data Service Integrator, this type of update was not possible and instead required exposing an entire service data object (SDO) instance.
From a technical perspective, Oracle Data Service Integrator includes a set of built-in mutator functions that you can use to create and modify XXDM instances from within XQuery and XQuery Scripting Extensions (XQSE) programs. Mutator functions accept an XXDM instance, along with parameters specifying the modification, and return the modified XXDM instance.
This enables you to modify XXDM instances from within XQuery and XQSE programs without having to drop down to Java programming. Oracle Data Service Integrator uses the Extended XQuery Data Model (XXDM), an extension of the XQuery Data Model (XDM), to represent instances of the XDM along with information about changes to the instances.
You can use the mutator functions to create and modify XXDM instances from within XQuery and XQuery Scripting Extensions (XQSE) programs.
To use the mutator functions, do the following:
For example, the following call declares $co to be an unmodified changed-element of type orders for the element represented by $o.
declare $co as changed-element(orders) := changed-element($o);
fn-bea:insert-into() function.fn-bea:delete() function.fn-bea:replace-value() function.\This section describes the following built-in mutator functions available in Oracle Data Service Integrator:
The fn-bea:changed-element() function allows you to promote an element to an unmodified changed-element. You can then modify the resulting changed-element using the built-in mutator functions.
The fn-bea:changed-element() function has the following signature:
fn-bea:changed-element($e as element()) as changed-element()
The following call declares $cc to be an unmodified changed-element of type customer for the element represented by $c.
declare $cc as changed-element(customer) := fn-bea:changed-element($c);
The fn-bea:insert-into() function enables you to insert a node into an XXDM instance at a specified XPath location.
The fn-bea:insert-into() function has the following signature:
fn-bea:insert-into($ce as changed-element(), $path as xs:string, $value as node()) as changed-element()
Consider the changed-element customer that has the following value bound to the variable $cc:
<customer ssn="XXX-XX-XXX">
<name>
<first>Thomas</first>
<last>Smith</last>
</name>
<address>...</address>
<address>...</address>
<orders>
<order>...</order>
<order>...</order>
<orders>
<customer>
Example—Inserting a New Element
set $cc := fn-bea:insert-into($cc, "name", <middle>Paul</middle>);
Assuming that the schema for customer permits an element for the middle name (middle) as a child of name and between the elements first and last, the call inserts the middle element as follows:
<customer ssn="XXX-XX-XXX">
<name>
<first>Thomas</first>
<middle>Paul</middle>
<last>Smith</last>
</name>
...
<customer>
Example—Inserting an Additional Element
set $cc := fn-bea:insert-into($cc, ".", <address>...<city>York</city></address>);
The call inserts an additional address element into the customer element, as follows:
<customer ssn="XXX-XX-XXX">
<name>...</name>
<address>...</address>
<address>...<city>York</city></address>
<address>...</address>
<orders>...</orders>
<customer>
Example—Inserting an Attribute
set $cc := fn-bea:insert-into($cc, "orders/order[2]", attribute paid { fn:true() });The call inserts the paid attribute into the second order, as shown by the following.
<customer ssn="XXX-XX-XXX">
...
<orders>
<order>...</order>
<order paid="true">...</order>
<orders>
<customer>
The fn-bea:delete() function enables you to delete a node at a specified XPath location in an XXDM instance. The function returns a copy of the instance that does not contain the specified descendant.
The fn-bea:delete() function has the following signature:
fn-bea:delete($ce as changed-element(), $path as xs:string) as changed-element()
Assume that the variable $cust is bound to the value presented in the earlier example.
set $cc := fn-bea:delete($cc, "address[2]");
The call deletes the second address child element of $cust, resulting in the value shown in the following:
<customer ssn="XXX-XX-XXX">
<name>...</name>
<address>...</address>
<orders>...<orders>
<customer>
set $cc := fn-bea:delete($cc, "@ssn");
The call deletes the ssn attribute of $cust, resulting in the value shown in the following:
<customer>
<name>...</name>
<address>...</address>
<address>...</address>
<orders>...<orders>
<customer>
The fn-bea:replace-value() function enables you to replace the value of a node in an XXDM instance at a specified XPath location. The function returns a copy of the instance containing the replaced value.
The fn-bea:replace-value() function has the following signature:
fn-bea:replace-value($ce as changed-element(), $path as xs:string, $value as xdt:anyAtomicType*) as changed-element()
| Note: | The function raises an error if the target node does not have simple content. |
Assume that the variable $cc is bound to the value presented in the earlier example.
Example—Replacing the Contents of an Element
set $cc := fn-bea:replace-value($cc, "name/last", "Jones");
The call changes the content of the last child of name from Smith to Jones, as shown in the following:
<customer ssn="XXX-XX-XXX">
<name>
<first>Thomas</first>
<last>Jones</last>
</name>
...
<customer>
Example—Replacing an Attribute Value
set $cc := fn-bea:replace-value($cc, "@ssn", "YYY-YY-YYYY");
The call changes the content of the ssn attribute from XXX-XX-XXX to YYY-YY-YYYY, as shown in the following:
<customer ssn="YYY-YY-YYYY">
<name>...</name>
<address>...</address>
<address>...</address>
<orders>...<orders>
<customer>
Example—Removing the Contents of an Element
set $cc := fn-bea:replace-value($cc, "name/first", ());
The call removes the content of the first child element of name, as shown in the following:
<customer ssn="XXX-XX-XXX">
<name>
<first/>
<last>Smith</last>
</name>
...
<customer>
If the element first is nillable, the result would be nilled, as shown in the following:
<customer ssn="XXX-XX-XXX">
<name>
<first xsi:nil="true"/>
<last>Smith</last>
</name>
...
<customer>
This section provides several examples showing how to use the built-in mutator functions to perform common update operations.
This example shows a data service update operation that accepts two simple input parameters, the customer ID (cid) and the Social Security Number (ssn).
declare procedure tns:update($cid as xs:string, $ssn as xs:string) {
declare $c as element(customer) := getCustomerByCustID($cid);
declare $cc as changed-element(customer) := fn-bea:changed-element($c);
set $cc := fn-bea:replace-value($cc, "ssn", $ssn);
tns:updateCUSTOMER($cc);
}
The update() operation retrieves the customer data by invoking the getCustomerByCustID() operation and updates the Social Security Number based on the ssn parameter. The example then updates the data source by calling the updateCUSTOMER() data service operation.
This example shows a data service update operation that accepts complex input parameters. The example uses the following input parameters:
<p_customer>
<customer_id>1</customer_id>
<ssn>545-54-5445</ssn>
</p_customer >
<p_address>
<address_id>1</address_id>
<street1>1108 Delmas Ave</street1>
<street2></street2>
<address_type>HOME</address_type>
</p_address>
The getCustomerByCustID() operation returns the following data:
getCustomerByCustID() Return Data
<customer>
<customer_id>1</customer_id>
<first_name>John</first_name>
<last_name>Deer</last_name>
<ssn>123-12-1234</ssn>
<address>
<address_id>1</address_id>
<customer_id>1</customer_id>
<city>San Jose</city>
<street1>123 Main Street</street1>
<street2>Apt 808</street2>
<address_type>HOME</address_type>
</address>
</customer>
The following example retrieves the customer data by invoking the getCustomerByCustID() operation, and returns the data in a hierarchical shape with a top-level node and multiple child nodes. The example then updates the Social Security Number (ssn) element using data supplied through the first parameter and updates the street element based on data in the second parameter.
Finally, the example updates the data source by calling the updateCUSTOMER() data service operation.
declare procedure tns:update($p1 as element(p_customer),
$p2 as element(p_address)) {
declare $c as element(customer) := getCustomerByCustID($p1/customer_id);
declare $cc as changed-element(customer) := fn-bea:changed-element($c);
set $cc := fn-bea:replace-value($cc, "ssn", fn:data($p1/ssn));
iterate $a at $i over $c/address {
if ($a/address_type eq $p2/address_type) {
declare $path1 as xs:string :=
concat("address[", $i, "]/street1");
declare $path2 as xs:string :=
concat("address[", $i, "]/street2");
set $cc := fn-bea:replace-value($cc, $path1,
data($p2/street1));
set $cc := fn-bea:replace-value($cc, $path2,
data($p2/street2));
}
}
tns:updateCustomer($cc);
}
The following example retrieves the customer data by invoking the getCustomerByCustID() operation, and returns the data in a hierarchical shape with a top-level node and multiple child nodes. The example then declares a new address element, populates the fields, and uses the insert-into() built-in function to insert the address into the customer element.
Finally, the example updates the data source by calling the updateCUSTOMER() data service operation.
declare procedure tns:update($p1 as element(p_customer),
$p2 as element(p_address)) {
declare $c as element(customer) := getCustomerByCustID($p1/customer_id);
declare $cc as changed-element(customer) := fn-bea:changed-element($c);
declare $addr as element(AddressType):=
<address>
<address_id>{data($p2/address_id)}</address_id>
<customer_id>{data($p1/customer_id}</customer_id>
<city>{data($p2/city)}</city>
<street1>{data($p2/street1)}</street1>
<street2>{data($p2/street1)}</street2>
<address_type>{data($p2/address_type)}</address_type>
</address>
set $cc := fn-bea:insert-into($cc, ".", $addr);
(: this call updates Customer and inserts new Address :)
tns:updateCustomer($cc);
}
This example shows a data service update operation that accepts a complex parameter as input, where the parameter structure matches the return type defined for the given data service.
The example uses the following input parameter:
<p_customer>
<customer_id>1</customer_id>
<ssn>545-54-5445</ssn>
<address>
<address_id>1</address_id>
<city>San Jose</city>
<street1>1108 Delmas Ave</street1>
<street2></street2>
<address_type>HOME</address_type>
</address>
<address>
<address_id>2</address_id>
<city>San Jose</city>
<street1>1108 First St.</street1>
<street2></street2>
<address_type>WORK</address_type>
</address>
</p_customer>
The getCustomerByCustID() operation returns the following data:
getCustomerByCustID() Return Data
<customer>
<customer_id>1</customer_id>
<first_name>John</first_name>
<last_name>Smith</last_name>
<ssn>123-12-1234</ssn>
<address>
<address_id>1</address_id>
<customer_id>1</customer_id>
<city>Santa Clara</city>
<street1>350 El Camino Real</street1>
<street2>Test Street</street2>
<address_type>HOME</address_type>
</address>
</customer>
The following example invokes the updateCustomer() operation in the data service to replace all values in the underlying data source (the update is performed based on the primary key). This is possible because the structure of the input parameter matches the return type defined for the data service.
| Note: | Note that the example includes a helper procedure that replaces the value of each child element of $ce with the value of the corresponding child element (using the same name) in $va. |
declare procedure tns:replace-values($ce as changed-element(),
$path as xs:string,
$va as element()) as changed-element() {
declare $parent as changed-element() := $ce;
declare $child as element() := fn-bea:current-value($ce);
(: manual navigation according to $path :)
iterate $step over tokenize($path, "/|[") {
if (starts-with($step, "[") {
(: this is a filter (assuming positional), so just shave :)
(: the square brackets off and apply the same filter :)
set $index as xs:string :=
substring($step, 2, string-length($step) - 2);
set $child := $child[xs:integer($index)]
} else {
set $child := $child/[local-name() eq $step]
}
}
iterate $leaf over $child/* {
declare $vaChild as element() := $va/*[local-name() eq local-name($leaf)];
if (exists($vaChild)) {
declare $cpath = concat($path, "/", local-name($leaf));
set $parent :=
fn-bea:replace-value($parent, $cpath, data($vaChild));
}
}
return value $parent;
}
declare procedure tns:update($p1 as element(p_customer)) {
declare $cust as element(customer) := getCustomerByCustID($p1/customer_id);
declare $ucust as changed-element(customer) := fn-bea:changed-element($cust);
iterate $addr at $i over $p1/address {
declare $uaddr as element(AddressType) :=
<address>
<address_id>{ data($addr/address_id) }</address_id>
<customer_id>{ data($addr/customer_id) }</customer_id>
<city>{ data($addr/city) }</city>
<street1>{ data($addr/street1) }</street1>
<street2>{ data($addr/street1) }</street2>
<address_type>{ data($addr/address_type) }</address_type>
</address>
if (exists($cust/address[$i])) {
declare $path as xs:string := concat("address[", $i , "]");
set $ucust := tns:replace-values($ucust, $path, $uaddr);
} else {
set $ucust := fn-bea:insert-into($ucust, ".", $uaddr);
}
}
tns:updateCustomer($uCustomer);
}
These examples show how to perform an update operation based on two complex parameters, one containing old values and the other containing potentially new values.
The example uses the following input parameters:
customer Parameter (New Values)
<customer>
<customer_id>1</customer_id>
<first_name>John</first_name>
<last_name>Smith</last_name>
<ssn>345-43-4988</ssn>
</customer>
customer Parameter (Old Values)
<customer>
<customer_id>1</customer_id>
<first_name>Johnny</first_name>
<last_name>Smithline</last_name>
<ssn>345-43-4988</ssn>
</customer>
The following example compares the two input parameters (the first containing old values and the second containing potentially new values) for differences using a series of if statements. The example then calls the updateCustomer() operation in the data service to update the data in the underlying data source if changes have been identified.
declare procedure tns:update($p_old as element(customer),
$p_new as element(customer)) {
declare $cust as changed-element(customer) := fn-bea:changed-element($p_old);
declare $modified as xs:boolean := false();
if (data($p_old/customer_id) eq data($p_new/customer_id) ) then {
if (data($p_old/first_name) ne data($p_new/first_name)) then {
set $cust := fn-bea:replace-value($cust, "first_name", fn:data($p_new/first_name));
set $modified := true();
} else {}
if (data($p_old/last_name) ne data($p_new/last_name)) then {
set $cust := fn-bea:replace-value($cust, "last_name",
fn:data($p_new/last_name));
set $modified := true();
} else {}
if (data($p_old/ssn) ne data($p_new/ssn)) then {
set $cust := fn-bea:replace-value($cust, "ssn",
fn:data($p_new/ssn));
set $modified := true();
} else {}
if ($modified) then {
tns:updatecustomer($c);
} else {}
} else {}
}
The following example compares the two input parameters for differences by iterating through the elements, and then calls the updateCustomer() operation in the data service to update the data in the underlying data source if changes have been identified.
declare procedure tns:update($p_old as element(customer),
$p_new as element(customer)) {
declare $cust as changed-element(customer) :=
fn-bea:changed-element($p_old);
declare $modified as xs:boolean := false();
if (data($p_old/customer_id) eq data($p_new/customer_id) ) then {
iterate $child over $p_old/* {
declare $name as xs:string := local-name($child);
declare $new := data($p_new/*[local-name() eq $name]);
if (data($child) ne $new) {
set $cust := fn-bea:replace-value($cust, $name, $new);
set $modified := true();
}
}
if ($modified) then {
tns:updatecustomer($c);
}
}
}
This example shows how to perform additional function calls to enrich the data before performing an update operation. The following input parameter is used:
<p_customer>
<customer_id>1</customer_id>
<ssn>545-54-5445</ssn>
<address>
<address_id>1</address_id>
<city>San Jose</city>
<street1>1108 First St.</street1>
<street2></street2>
<country>US</country>
</address>
</p_customer>
The getCustomerByCustID() operation returns the following data:
getCustomerByCustID() Return Data
<customer>
<customer_id>1</customer_id>
<first_name>John</first_name>
<last_name>Deer</last_name>
<ssn>123-12-1234</ssn>
<address>
<address_id>1</address_id>
<customer_id>1</customer_id>
<city>San Jose</city>
<street1>1108 First</street1>
<street2></street2>
<zip_code>95125</zip_code>
<address_valid>N</address_valid>
<country>US</country>
</address>
</customer>
The getValidAddress() operation accepts address information as a parameter and returns a standardized address element together with a validity code indicating whether the address is valid or not.
The getValidAddress() operation returns the following data:
<address>
<city>San Jose</city>
<street1>1108 1st Street</street1>
<street2></street2>
<zip_code>95131</zip_code>
<address_valid>Y</address_valid>
<country>US</country>
</address>
The following example calls a read operation in the data service to retrieve additional information to include in the updated information. The example then replaces the appropriate values and calls the updateCustomer() operation to update the data in the underlying data source.
declare procedure tns:update($p1 as element(p_customer)) {
declare $c as element(customer) :=
getCustomerByCustID($p1/customer_id, p1/address_id);
declare $cc as changed-element(customer) :=
fn-bea:changed-element($c);
set $cc := fn-bea:replace-value($cc, "ssn", $p1/ssn);
declare $vAddress as element(ValidAddress) := tns:getValidAddress(
<ns1:address>
<city>{ data($p1/city) }</city>
<street1>{ data($p1/street1) }</street1>
<street2>{ data($p1/stree2) }</street2>
<country>{ data($p1/country) }</country>
</ns1:address>);
set $cc := fn-bea:replace-value($cc, "city", $vAddress/city)
set $cc := fn-bea:replace-value($cc, "street1", $vAddress/street1)
set $cc := fn-bea:replace-value($cc, "street2", $vAddress/street2)
set $cc := fn-bea:replace-value($cc, "zip_code", $vAddress/zip_code)
set $cc := fn-bea:replace-value($cc, "address_valid",
$vAddress/address_valid)
set $cc := fn-bea:replace-value($cc, "country", $vAddress/country)
tns:updateCustomer($cc);
}This example shows how to send changed data to two different data sources. The following input parameter is used:
<p_customer>
<customer_id>1</customer_id>
<ssn>545-54-5445</ssn>
<first_name>Johan</first_name>
<last_name>Tyson</last_name>
<cud_operation>U</cud_operation>
<address>
<address_id>1</address_id>
<city>San Jose</city>
<street1>1108 First St.</street1>
<street2></street2>
<country>US</country>
<cud_operation>U</cud _operation>
</address>
<address>
<address_id>1</address_id>
<city>San Jose</city>
<street1>1108 First St.</street1>
<street2></street2>
<country>US</country>
<cud_operation>D</cud _operation>
</address>
</p_customer>
The following example reads and updates customer information, replacing the data in the first data source and inserting the customer data in the second data source. Similarly, the example reads and updates the related address information, replacing the data in the first data source and inserting the address data in the second data source.
declare procedure tns:update($p1 as element(p_customer)) {
declare $c as element(customer) :=
getCustomerInfoByCustID(data($p1/customer_id));
declare $cc as changed-element(customer) :=
fn-bea:changed-element($c);
declare $curc as element(customer);
if (data($p1/cud_operation) eq "U") then {
set $cc := fn-bea:replace-value($cc, "first_name",
data($p1/first_name));
set $cc := fn-bea:replace-value($cc, "last_name",
data($p1/last_name));
set $cc := fn-bea:replace-value($cc, "ssn", data($p1/ssn));
(: this update goes into the first data source :)
updateCustomer($cc);
set $curc := fn-bea:current-value($cc);
(: this insert goes into the second data source :)
insertCustomer($curc);
}
{
declare $addr as element(address) := getAddressInfoByCustID(data($p1/customer_id));
declare $caddr as changed-element(address) :=
fn-bea:changed-element($addr);
declare $curaddr as element(address);
declare $paddr := $p1/address[1];
if (data($p1/cud_operation) eq "U") then {
set $caddr := fn-bea:replace-value($caddr, "city",
data($paddr/city));
set $caddr := fn-bea:replace-value($caddr, "street1",
data($paddr/street1));
set $caddr := fn-bea:replace-value($caddr, "street2",
data($paddr/street2));
set $caddr := fn-bea:replace-value($caddr, "country",
data($paddr/country));
(: this update goes into the first data source :)
updateAddress($caddr);
set $curaddr :=
<address>
<address_id>{ data($paddr/address_id) }</address_id>
<city>{ data($paddr/city) }</city>
<street1>{ data($paddr/street1) }</street1>
<street2>{ data($paddr/street2) }</street2>
<country>{ data($paddr/country) }</country>
</address>
(:this insert goes into the second data source :)
insertAddress($curaddr);
}
}
}The following functions from the XQuery 1.0 specification are not supported in current Oracle XQuery engine implementation:
The following functions are not available to client applications for use in ad-hoc queries:
| Note: | These functions are, however, available for use within data service XQuery functions and procedures. |
This section describes Oracle-specific implementation details related to functions and operators.
This section describes the Oracle XQuery language implementation, and contains the following topics:
The Oracle Data Service Integrator conforms to the W3C Working Draft “XQuery 1.0: An XML Query Language” dated 23 July 2004 (http://www.w3.org/TR/2004/WD-xquery-20040723/), with these exceptions:
Beyond compliance with the specification, Oracle’s XQuery language implementation (the Oracle Data Service Integrator XQuery engine) extends the XQuery language via the following:
Oracle offers a group by clause extension to standard FLWOR expressions. The following EBNF shows the syntax of the general FLWGDOR:
flwgdorExpression := (forClause | letClause) (forClause
| letClause
| whereClause
| groupbyClause
| orderbyClause)* returnClause
groupbyClause := "group" [variable "as" variable] "by" (expression
["as" variable]) ("," (expression ["as" variable]))*
The remaining clauses referenced in the EBNF fragment follow the standard definition, as presented in the XQuery specification.
As an example, consider the problem of grouping books by year, without losing books that do not have a year attribute. Using standard XQuery, you would need to perform a self-join with the result of the fn:distinct-values() function, concatenating the result of the self-join with the result for books without a year attribute.
The following illustrates an XQuery expression that can be used to accomplish this:
let $books := document("bib.xml")/bib/book return (
for $year in fn:distinct-values($books/@year)
return
<g>
<year>{ $year }</year>
<titles>{ $books[@year eq $year]/title }</titles>
</g>,
<g>
<year/>
<titles>{ $books[fn:empty(@year)]/title }
</g>
)
Using the Oracle group by extension function, you could write the same query as follows:
for $book in document("bib.xml")/bib/book
group $book as $partition by $book/@year as $year
return
<g>
<year>{ $year }</year>
<titles>{ $partition/title }</titles>
</g>
The following tables (Table 2-4 and Table 2-5) show book bindings before and after the group by clause is applied.
The FLWGOR expression conceptually builds a sequence of binding tuples, where the size of the tuple is the number of variables in scope at that point in the FLWGOR. In the example, the tuple at the group by clause consists of a single variable binding $book which binds to each book in the bib.xml document, one book at a time (see Table 2-4).
The group by creates a new sequence of binding tuples with each output tuple containing variables defined in the group by clause. After the group by, all variables there were previously in-scope go out of scope.
In the example, the output tuple from the group by clause is of size two with the variable bindings being for $year and $partition (see Table 2-5).
The number of output tuples is equal to the number of unique group by value bindings. In the above example, this is the number of unique book/@year values: 2. The variable introduced in the group clause ($partition in the example above) binds to the sequence of all matching input values.
This extension enables external consumers of XML generated by XQuery to have certain empty elements and attributes omitted. You can specify this using optional indicators, instead of employing computed constructors, conditional statements, and custom functions.
For example, consider the following query:
<a><b>{()}</b><c foo="{()}"/></a>,The extension enables the following to be returned:
<a><c/></a>
<a><b/><c foo=""/></a>
The extension uses the optional indicator '?' with direct element and attribute constructors. This means that in the following you could change the production DirElemConstructor to the following:
[94] DirElemConstructor ::= "<" QName "?"? DirAttributeList
("/>" | (">" DirElemContent* "</" QName S? ">")) /* ws: explicit */
Likewise, you could change the DirAttributeList to the following:
[95] DirAttributeList ::= (S (QName "?"? S? "=" S?
DirAttributeValue)?)*
When ? is present, elements with no children and attributes with the value "" are omitted. The query in the example could then be written as:
<a><b?>{()}</b><c foo?="{()}"/></a>which produces the following result:
<a><c/></a>
In another example, consider the case of constructing a new customer element with different tags. One requirement is that you do not want a phone element in the resulting customer when the phone number does not exist in the original customer. Using standard XQuery, you would have to write:
for $cust in CUSTOMER()
return
<customer>
<id>{ fn:data($cust/C_ID) }</id>
{
if (fn:exists($cust/PHONE))
then <phone>{ fn:data($cust/PHONE) }</phone>
else ()
}
...
</customer>
Using the optional element constructor, you could instead write the following:
for $cust in CUSTOMER()
return
<customer>
<id>{ fn:data($cust/C_ID) }</id>
<phone?>{ fn:data($cust/PHONE) }</phone>
...
</customer>
Similarly, when you want the resulting customer element to use attributes instead of elements, you would need to employ computed attribute constructors using standard XQuery, as illustrated by the following:
for $cust in CUSTOMER()
return
<customer
id="{ fn:data($cust/C_ID) }"
{
if (fn:exists($cust/PHONE))
then attribute { "phone" } { fn:data($cust/PHONE) }
else ()
}
...
/>
Using the optional attribute constructor, the query becomes:
for $cust in CUSTOMER()
return
<customer
id="{ fn:data($cust/C_ID) }"
phone?="{ fn:data($cust/PHONE) }"
...
/>
This section describes the Oracle-specific implementation details related to XQuery language processing.
|