1. Developing Services with Oracle Service Bus
  2. Transforming Data
  3. Transforming Data with XQuery

19 Transforming Data with XQuery

This chapter describes how to create, locate, edit, and delete XQuery Transformation resources using the Oracle Service Bus Console.

XQuery transformation maps can describe XML-to-XML, XML to non-XML, and non-XML to XML mappings.

This chapter includes the following topics:

19.1 Introduction to XQuery Transformations

XQuery helps in querying XML data from XML documents. XQuery uses and extends XPath to help navigate and extract elements and attributes from an XML document.

Service Bus uses XQuery to implement its business logic. Service Bus makes use of XQuery resources for various activities, like transformations, data selection, condition evaluation, and data manipulation. Service Bus fully supports XQuery 1.0. This includes optional features such as modules. The older XQuery 2004 is also supported.

XQuery transformation maps describe the mapping between two data types. XQuery maps describe mappings between XML documents with different schemas. Using XQuery, Service Bus can process XML documents and transform document data from one XML schema to another, enabling data interchange among applications that use different schemas. You can perform complex data manipulation and transformation using XQuery. For example, you can map an incoming purchase order schema to an outgoing invoice schema.

You use XQuery expressions to create the data content for the message context variables (or part of a message context variable) during the execution of the message flow. You can use the Test Console directly in the XQuery Expression Editor to test the definition of the expression. Similarly, you use XQuery conditions to evaluate Boolean conditions in the message flow. You can use the Test Console directly in the XQuery Condition Editor to test the definition of the condition.

19.2 XQuery Editors and Mappers

JDeveloper provides both an Expression Builder, where you can script transformations using XQuery, and an XQuery Mapper, where you can create complex mappings.

The Oracle Service Bus Console provides an editor for scripting transformations using XQuery. The editor provides options to define an XQuery expression or to define an expression that evaluates at runtime to the name of an existing XQuery resource.

For both JDeveloper and the console, you access the editors from an action in either a pipeline or split-join.

19.2.1 JDeveloper Editors and Mappers

The XQuery mapper in JDeveloper is a graphical tool that lets you define mappings between schema root elements, WSDL message parts, or WSDL messages. Schema root elements can come from XSD schema files or WSDL files, but only those WSDL messages that contain a single message part can be mapped directly. Once you create an XSLT mapping in JDeveloper, you can upload the .xsl file generated by the mapper to an XSLT resource in the Oracle Service Bus Console.

JDeveloper also includes a variety of Expression Builders, where you can create expressions that specify an existing XSLT resource to use. For more information about the mapper and editors in JDeveloper, see the following topics:

19.2.2 Oracle Service Bus Console Editors

In the Oracle Service Bus Console, the XQuery/XSLT Expression Editor lets you create expressions that specify an existing XQuery resource to use.

Before you can reference an XQuery resource, you need to create the resource in the console and upload an existing XQuery transformation file (.xqy) to the resource. This feature allows you to create complex mappings in JDeveloper that you can them import and use in the console. You can reuse an XQuery transformation in multiple pipelines and split-joins.

For information about the XQuery/XSLT Editor in the Oracle Service Bus Console, see Working With Expression Editors in Oracle Service Bus Console.

19.3 Creating XQuery Maps in JDeveloper

You can create XQuery maps in a Service Bus project in JDeveloper, and then use them in XQuery expressions in pipelines and split-joins to map objects between external systems.

When you create an XQuery mapping, you need to select the source XML schema elements or XML files to use for the source and target mappings.

19.3.1 How to Create XQuery Mappings in JDeveloper

See "Creating an XQuery Map File" in Developing SOA Applications with Oracle SOA Suite for details on creating an XQuery map.

See "Using the XQuery Mapper" in Developing SOA Applications with Oracle SOA Suite for details on using the XQuery mapper to build your XQuery.

19.4 Testing Service Bus Projects Converted from XQuery 2004 to XQuery 1.0 in JDeveloper

When converting a Service Bus project from XQuery 2004 to XQuery 1.0, all 2004 XQueries will be switched to run against the XQuery 1.0 engine. After converting from XQuery 2004 to XQuery 1.0, the XQuery Mapper tab in JDeveloper displays, but doesn’t display actual mapping.

To test converted XQueries in JDeveloper:
  1. Restart JDeveloper.
  2. Ensure that the XQuery file that you want to test is open.
  3. Click the XQuery Source tab to enter Source view.
  4. Right click the source, and then select Run XQuery.

When testing converted XQueries:

  • Ensure that you make the namespace declaration correctly. This can be done in two ways:

    • Using an import statement from the XQuery specification:

      import schema namespace ns0="http://www.example.com/custele"
at "../TestInputSchemas/customerEle.xsd";

    • Using Oracle’s annotation mechanism:

      xquery version "1.0"; (:: OracleAnnotationVersion "1.0" ::)
declare namespace ns0="http://www.example.com/custele";
(:: import schema at "../TestInputSchemas/customerEle.xsd"::)

  • Ensure that you declare variables as schema-elements so that they are recognized by the JDeveloper mapper mechanism. For example:

    declare function local:AttributeToElement($customerOut as element()(::schema-element(ns0:customerOut)::)) 
	as element() (::schema-element (ns1:customer)::)

19.5 Working with XQuery Resources in the Oracle Service Bus Console

You can add XQuery resources to your Service Bus project. XQuery files, created using JDeveloper or other editors, can be imported into your project as resources.

19.5.1 How to Create an XQuery Resource in the Console

Use the Oracle Service Bus Console to add XQuery resources to your Service Bus project. You can either import an XQuery file created in an editor like JDeveloper, or create a resource and edit the code inline.

To create an XQuery Resource in the console:

  1. In the Project Navigator, right-click the project or folder to contain the XQuery resource, point to Create, and select Resource Click Transformations, click XQuery, and then click OK.

    The Create XQuery dialog appears.

  2. Do one of the following:

    • To create the resource from an existing XQuery file, click Choose File next to the File Upload field and then navigate to and select the file to use.

      The Resource Name field is automatically populated with the file name minus the file extension. You can change this name.

    • To create an XQuery from scratch, enter a unique name for the XQuery resource.

  3. Optionally, enter a brief Description of the resource.

  4. Click Create.

    The XQuery resource opens in the XQuery Definition Editor.

  5. To modify the XQuery, do the following:

    1. Click Edit XQuery Contents in the toolbar.

      The View/Edit Source dialog appears.

    2. To browse to and select a new XQuery file to upload, click Choose File.

    3. To modify the contents of the file, update the code directly in the Contents section of the dialog.

    4. Click Save. The XQuery is validated upon save.

  6. In the XQuery Definition Editor toolbar, click Save.

  7. To end the session and deploy the configuration to the runtime, click Activate.

19.5.2 How to Edit an XQuery Resource in the Console

Use the Oracle Service Bus Console to edit XQuery resources in your Service Bus project. You can either import an updated XQuery file created in an editor like JDeveloper, or edit the code inline.

To edit an XQuery Resource in the console:

  1. In the Project Navigator, expand the project and folders containing the XQuery resource to edit.
  2. Right-click the XQuery resource name, and select Open.
  3. To edit the XQuery source, click Edit XQuery contents in the toolbar.

    The View/Edit Source dialog appears.

  4. To browse to and select a new XQuery file to upload, click Choose File.
  5. To modify the contents of the file, update the code directly in the Contents section of the dialog.
  6. Click Save.
  7. In the XQuery Definition Editor toolbar, click Save.
  8. To end the session and deploy the configuration to the runtime, click Activate.

19.5.3 How to Delete an XQuery Resource in the Console

You can use the Oracle Service Bus Console to delete an XQuery resource from your Service Bus project. If the resource has any references, remove them before deleting it. Open the XQuery resource in the XQuery Definition Editor and click the Tools icon in the upper right, and then select References to find out whether there are any references.

To delete an XQuery resource in the console:

  1. In the Application Navigator or Project Navigator, expand the project and folders containing the XQuery resource to delete.
  2. Right-click the name of the XQuery resource, and select Delete. A confirmation dialog appears.
  3. Click Yes to delete the resource.
  4. Click Activate to end the session and deploy the configuration to the runtime.

19.5.4 How to Upgrade Your XQuery Resources to use XQuery 1.0

Service Bus supports XQuery 1.0. The older XQuery 2004 is also supported. Any new XQuery resource created in Service Bus uses the XQuery 1.0 version, by default.

If you have upgraded from a pre-12g Service Bus project, all XQuery resources in the project are configured to use the XQuery 2004 version. The following line is present as the first line in all XQuery files: 

xquery version "2004-draft";

You can choose to upgrade all XQuery 2004 resources in your project to use XQuery 1.0. The XQuery converter performs basic translation of XQuery 2004 files to XQuery 1.0. You need to manually verify and correct syntax errors that cannot be handled by the converter.

To upgrade the XQuery resources in a project:

  1. In the Application Navigator or Project Navigator, right-click the project to upgrade.
  2. Select Convert to XQ 1.0 from the context menu that appears. A confirmation dialog appears.
  3. Select Yes to convert all resources to use the XQuery 1.0 engine.

    You can use the Test Console to test your XQuery resources. To use the test console, open the project by clicking the project name in the Project Navigator. Click the Launch Test Console icon, under the Actions column, corresponding to the XQuery resource that you wish to test.

19.5.4.1 Syntax Errors After Xquery Update to V1.0

Versions of Xquery prior to 1.0 supported an optional indicator language extension. This non-standard language extension is not supported in the 1.0 query processor, but the (#ora-ext:if-exists-content#) pragma has been added to replace it.

Example 1

<address?>{ $customer/address }</address>

becomes

(#ora-ext:if-exists-content#) { <address>{ $customer/address }</address> }

Example 2

<a href="{$url}" alt?="{$description}"/>

becomes

<a href="{$url}" >{ (#ora-ext:if-exists-content#) { attribute alt {$description} } }</a>

19.6 Service Bus XQuery Functions

Service Bus supports these XQuery functions.

  • The standard XQuery functions described in the W3C specification:

    http://www.w3.org/TR/xpath-functions/

  • Oracle function extensions and language keywords provided as part of the Oracle XQuery engine—with a small number of exceptions, as described in Supported Function Extensions from Oracle.

  • Service Bus-specific function extensions. See Function Extensions from Service Bus.

    Note:

    All of the Oracle function extensions use the following function prefix fn-bea: In other words, the full XQuery notation for an extended function is of this format: 

    fn-bea: function_name.

19.6.1 Supported Function Extensions from Oracle

For descriptions of all Oracle function extensions, see Service Bus XQuery Functions.

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 Service Bus. They are better covered by other language features:

  • fn-bea:if-then-else

  • fn-bea:QName-from-string

  • fn-bea:sql-like

19.6.2 Function Extensions from Service Bus

Service Bus provides the following XQuery functions:

19.6.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 Working with 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. 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 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>
19.6.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)
19.6.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)
19.6.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.

19.6.2.5 fn-bea:execute-sql()

The fn-bea:execute-sql() function provides low-level database access from XQuery within Service Bus message flows--see 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 XQuery-SQL Mapping Reference.

When you execute the fn-bea:execute-sql() function from a 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 Service Bus:

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

Service Bus proxy services support specification of the URI to which messages are to be routed at runtime (dynamically)—see Using Dynamic Routing. The following is an example use of the fn-bea:execute-sql() function to retrieve the URI from a database in a dynamic routing scenario.

Example - 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 the example:

  • 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 the following example:

Example - 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 the Get the URI for a Business Service from a Database example is executed as a result of a proxy service receiving the request message in the following example (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

Example Request Message $body

<m:Request xmlns:m="http://www.bea.com/alsb/example"> 
<m:customer_pri>0001</m:customer_pri>
</m:Request>
19.6.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 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 - 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 the following example.

Example - $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>
19.6.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
19.6.2.7 fn-bea:binary-to-text

The fn-bea:binary-to-text function converts binary-content to text.

This function has the following signature: fn-bea:binary-to-text($arg0-anyType, $arg1-string)

19.6.2.8 fn-bea:binary-to-xml

The fn-bea:binary-to-xml function converts-binary content to inline XML.

This function has the following signature: fn-bea:binary-to-xml($arg-anyType)

19.6.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 Custom XPath Functions.