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/>');

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>

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>