43 XQuery Implementation

The Oracle XQuery engine fully supports all of the language features that are described in the World Wide Web (W3C) specification for XQuery with one exception: modules. For more information about the XQuery 1.0 and XPath 2.0 functions and operators (W3C Working Draft 23 July 2004), see the following URL: http://www.w3.org/TR/2004/WD-xpath-functions-20040723/

Oracle Service Bus supports the following XQuery functions:

43.1 Supported Function Extensions from Oracle

For descriptions of all Oracle function extensions, see "XQuery Implementation" in the XQuery and XQSE Developer's Guide at http://download.oracle.com/docs/cd/E13162_01/odsi/docs10gr3/xquery/extensions.html.

Oracle Service Bus supports all Oracle function extensions to XQuery except for the following:

  • fn-bea:is-access-allowed

  • fn-bea:is-user-in-group

  • fn-bea:is-user-in-role

  • fn-bea:userid

  • fn-bea:async

  • fn-bea:timeout

  • fn-bea:get-property

  • fn-bea:execute-sql()

Oracle recommends that you do not use the following functions in Oracle Service Bus. They are better covered by other language features:

  • fn-bea:if-then-else

  • fn-bea:QName-from-string

  • fn-bea:sql-like

43.2 Function Extensions from Oracle Service Bus

Oracle Service Bus provides the following XQuery functions:

43.2.1 fn-bea:lookupBasicCredentials

The fn-bea:lookupBasicCredentials function returns the user name and unencrypted password from a specified service account. You can specify any type of service account (static, pass-through, or user-mapping). See Chapter 17, "Service Accounts."

Use the fn-bea:lookupBasicCredentials function as part of a larger set of XQuery functions that you use to encode a user name and password in a custom transport header or in an application-specific location within the SOAP envelope. You do not need to use this function if you only need user names and passwords to be located in HTTP Authentication headers or as WS-Security user name tokens. Oracle Service Bus already retrieves user names and passwords from service accounts and encodes them in HTTP Authentication headers or as WS-Security user name tokens when required.

The function has the following signature:

fn-bea:lookupBasicCredentials( $service-account as xs:string ) as UsernamePasswordCredential

where $service-account is the path and name of a service account in the following form:

project-name[/folder[...]]/service-account-name

The return value is an XML element of this form:

<UsernamePasswordCredential 
   xmlns="http://www.bea.com/wli/sb/services/security/config">
   <username>name</username>
   <password>unencrypted-password</password>
</UsernamePasswordCredential>

You can store the returned element in a user-defined variable and retrieve the user name and password values from this variable when you need them.

For example, your Oracle Service Bus project is named myProject. You create a static service account named myServiceAccount in a folder named myFolder1/myFolder2. In the service account, you save the user name of pat with a password of patspassword.

To get the user name and password from your service account, invoke the following function:

fn-bea:lookupBasicCredentials( myProject/myFolder1/myFolder2/myServiceAccount )

The function returns the following element:

<UsernamePasswordCredential 
   xmlns="http://www.bea.com/wli/sb/services/security/config">
   <username>pat</username>
   <password>patspassword</password>
</UsernamePasswordCredential>

43.2.2 fn-bea:isUserInGroup

Returns whether or not a given user belongs to a given group (true or false). For example:

fn-bea:isUserInGroup($user-name as xs:string, $group-name as xs:string)

43.2.3 fn-bea:isUserInRole

Returns whether or not a given user belongs to a given role (true or false). For example:

fn-bea:isUserInRole($user-name as xs:string, $role-name as xs:string)

43.2.4 fn-bea: uuid

The function fn-bea:uuid returns a universally unique identifier. The function has the following signature:

fn-bea:uuid() as xs:string

You can use this function in the proxy pipeline to generate a unique identifier. You can insert the generated unique identifier into an XML document as an element. You cannot generate a unique identifier to the system variable. You can use this to modify a message payload.

For example, suppose you want to generate a unique identifier to add to a message for tracking purposes. You could use this function to generate a unique identifier. The function returns a string that you can add it to the SOAP header.

43.2.5 fn-bea:execute-sql()

The fn-bea:execute-sql() function provides low-level database access from XQuery within Oracle Service Bus message flows--see Section 37.9, "Accessing Databases Using XQuery." The query returns a sequence of flat row elements with typed data.

The function has the following signature:

fn-bea:execute-sql( $datasource as xs:string, $rowElemName as xs:QName, $sql as xs:string, $param1, ..., $paramk) as element()*

where

  • $datasource is the JNDI name of the datasource

  • $rowElemName is the name of the row element—specify $rowElemName as whatever QName you want each element of the resulting element sequence to have

  • $sql is the SQL statement

  • $param1, ..., $paramk are 1 to k parameters

  • element()* represents the sequence of elements returned

The return value is a sequence of flat row elements with typed data and automatically translates values between SQL/JDBC and XQuery data models. Data Type mappings that the XQuery engine generates or supports for the supported databases can be found in the Appendix H, "XQuery-SQL Mapping Reference."

When you execute the fn-bea:execute-sql() function from an Oracle Service Bus message flow, you can store the returned element in a user-defined variable.

Use the following examples to understand the use of the fn-bea:execute sql() function in Oracle Service Bus:

43.2.5.1 Example 1: Retrieving the URI from a Database for Dynamic Routing

Oracle Service Bus proxy services support specification of the URI to which messages are to be routed at run time (dynamically)—see Section 37.8, "Using Dynamic Routing." Example 43-1 is an example use of the fn-bea:execute-sql() function to retrieve the URI from a database in a dynamic routing scenario.

Example 43-1 Get the URI for a Business Service from a Database

<ctx:route><ctx:service>
{
    fn-bea:execute-sql(
    'ds.myJDBCDataSource', 
    xs:QName('customer'), 
    'SELECT targetService FROM DISPATCH_MAPPING WHERE customer_priority=?',
      xs:string($body/m:Request/m:customer_pri/text())
    )/TARGETSERVICE/text()
}
</ctx:service></ctx:route>

In Example 43-1:

  • ds.myJDBCDataSource is the JNDI name to the data source

  • xs:string($body/m:Request/m:customer_pri/text()) interrogates the request message and populates customer_priority=? with the value of customer_pri in the message

  • /TARGETSERVICE/text() is the path applied to the result of the SQL statement, which results in the string (CDATA) contents of that element being returned

  • <ctx:route><ctx:service> ... </ctx:service></ctx:route> are required elements of the XQuery statement for a dynamic routing scenario

  • The following is the table definition for DISPATCH_MAPPING:

    create table DISPATCH_MAPPING 
(
  customer_priority varchar2(256),
  targetService varchar2(256),
  soapPayload varchar2(1024)
);

The DISPATCH_MAPPING table is populated as shown in Example 43-2:

Example 43-2 DISPATCH_MAPPING Table

 INSERT INTO DISPATCH_MAPPING (customer_priority, targetService, soapPayload)
 VALUES ('0001', 'system/UCGetURI4DynamicRouting_proxy1', '<something/>');
 INSERT INTO DISPATCH_MAPPING (customer_priority, targetService, soapPayload)
 VALUES ('0002', 'system/UCGetURI4DynamicRouting_proxy2', '<something/>');

Note:

The third column in the table (soapPayload) is not used in this scenario.

Executing the fn-bea:execute-sql for Example 3

If the XQuery in Example 43-1 is executed as a result of a proxy service receiving the request message in Example 43-3 (note that the value of <customer_pri> in the request message is 0001), the URI returned for the dynamic route scenario is

system/UCGetURI4DynamicRouting_proxy1

(See also Example 43-2.)

Example 43-3 Example Request Message $body

<m:Request xmlns:m="http://www.bea.com/alsb/example"> 
<m:customer_pri>0001</m:customer_pri>
</m:Request>

43.2.5.2 Example 2: Getting XMLType Data from a Database

Data Type mappings that the XQuery engine generates or supports for the supported databases can be found in the Appendix H, "XQuery-SQL Mapping Reference." Note that the XMLType column type in SQL is not supported. However, you can access the data in an XMLType column by using the getStringVal() method of the XMLType object to convert it to a String value.

The following scenario outlines a procedure you can use to select data from an XMLType column in an Oracle database.

  1. Use an assign action in a proxy service message flow to assign the results of the following XQuery to a variable ($result).

    Example 43-4 Get XMLType Data from a Database

    fn-bea:execute-sql(
    'ds.myJDBCDataSource', 
    'Rec', 
    'SELECT a.purchase_order.getStringVal() purchase_order from datatypes a'
)

    where:

    • ds.myJDBCDataSource is the JNDI name to the data source

    • Rec is the $rowElemName—therefore, Rec is the QName given to each element of the resulting element sequence

    • select a.purchase_order.getStringVal() ... is the SQL statement that uses the getStringVal() method of the XMLType object to convert it to a String value

    • datatypes is the table from which the value of the XML is read (the datatypes table in this case contains one row)

      Note:

      The following is the table definition for the dataty.pes table: 
      create table datatypes 
(
  purchase_order xmltype
);

  2. Use a replace action to replace the node contents of $body with the results of the fn-bea:execute-sql() query (assigned to $result in the preceding step):

    Replace [ node contents ] of [ undefined XPath ] in [ body ] with 
[ $result/purchase_order/text() ]

    The following listing shows $body after the replacement.

    Note:

    The datatypes table contains one row (with the purchase order data); the row contains the XML represented in Example 43-5.

Example 43-5 $body After XML Content is Replaced with Result of fn-bea:execute-sql()

<soap-env:Body>
  <openuri:orders xmlns:openuri="http://openuri.com/"> 
    <openuri:order> 
      <openuri:customerID>123</openuri:customerID> 
      <openuri:orderID>123A</openuri:orderID> 
    </openuri:order> 
    <openuri:order> 
      <openuri:customerID>345</openuri:customerID> 
      <openuri:orderID>345B</openuri:orderID> 
    </openuri:order> 
    <openuri:order> 
      <openuri:customerID>789</openuri:customerID> 
      <openuri:orderID>789C</openuri:orderID> 
    </openuri:order> 
  </openuri:orders> 
</soap-env:Body>

43.2.6 fn-bea:serialize()

You can use the fn-bea:serialize() function if you need to represent an XML document as a string instead of as an XML element. For example, you may want to exchange an XML document through an EJB interface and the EJB method takes String as argument. The function has the following signature:

fn-bea:serialize($input as item()) as xs:string

43.3 Creating and Using Custom XPath Functions

You can create and use your own custom XPath functions in both inline XQuery expressions and in XQuery resources. For more information, see "Creating and Using Custom XPath Functions" in the Oracle Fusion Middleware Developer's Guide for Oracle Service Bus.