As Published In
Oracle Magazine
March/April 2005

DEVELOPER: XML


Aggregate Data with XQuery

By Nilesh Junnarkar, Nirav Chanchani, and Julie Basu

Oracle XML Data Synthesis brings information together.

Most large organizations have multiple applications in-house, each designed to support a specific set of business functions, and these applications are generally optimized for a particular business function. Unlike its applications, however, the various units within a business do not operate independently of each other. There often is a strong desire for the IT infrastructure of a business to get a complete picture of the way the business operates and the current state of the enterprise.

Applications are capable of making available information contained within their specific domains, but they are often not designed to provide a consolidated view of information contained in other applications or data. For example, to get a complete picture of the business that a company does with a particular partner, the company's management might need to access multiple datasources, including databases, applications, and services. Examples of these datasources are 

  • An order entry application, for getting information on the orders placed

  • A financial Web service, for seeing the latest status on payments

  • A customer-relationship management (CRM) application, for viewing customer details

  • A shipping and distribution database, for getting information on products shipped to the partner

Clearly, it is difficult for an organization to construct a full picture of its operations when the desired information is distributed across multiple heterogeneous datasources. Any solution for this problem must be able to access information in real time and must be easily extensible to handle new and different types of datasources. The cost of building such an application must also not be prohibitive.

Herein lies the problem: How does an IT organization go about quickly building an application that supports real-time access to information contained across multiple datasources, each datasource with its own data format, method of access, and communication protocol?

One answer is to use a new solution: Oracle XML Data Synthesis.

Oracle XML Data Synthesis

Oracle XML Data Synthesis (Oracle XDS) is designed expressly to solve the problem of accessing disparate data and aggregating the information in real time. Oracle XDS provides a generic service that takes care of all the plumbing with regard to information source access and information aggregation, by providing an easy-to-use declarative framework to plug in various types of datasources and dynamically query across them. Because heterogeneous data formats are typically involved in this problem, Oracle XDS utilizes an XML-based approach—it converts input data to XML (using standard or custom functions) and uses XQuery as a declarative XML query language to specify how to consolidate the different pieces of XML information.

The benefits of using the Oracle XDS infrastructure are many: 

  • You specify datasources in a declarative manner—for example, in a configuration file.

  • You can also declaratively plug in translation functions for XML conversion (relevant for non-XML datasources).

  • The underlying Oracle XDS infrastructure is responsible for invoking the appropriate application-programming interfaces (APIs) to get the information, processing it following the user-specified XQuery, and returning the consolidated result.

  • Because information is queried directly from the datasources, it is always current, real-time information, and there is no overhead of data replication, as in data warehousing.

  • Oracle XDS is a middle-tier J2EE-based solution that employs standard APIs and technologies, including Java Database Connectivity (JDBC), J2EE Connector Architecture (J2CA), Web services, and XQuery for data access and querying.

 

figure 1
Figure 1: A many-datasource business problem


A Real-Life Problem Solved with XDS

We will now consider a real-life user problem and show how to build an application, using Oracle XDS, to solve it.

A manufacturing organization has the requirement to build an executive dashboard where company management can get a consolidated view of the company's business with its largest and key customers. The requirements are to build a Web-based application that accesses up-to-date information on key customers from the following sources: 

  • Order management system for orders placed by the customers in the past week

  • Financial spreadsheet for receivables from the customers

  • Shipping Web service produced by the shipping vendor for orders shipped to the customers

  • CRM application for information on the customers

Installing OC4J and Oracle XDS. The solution for the firm's business requirements runs on the J2EE container of Oracle Application Server—Oracle Containers for J2EE (OC4J). You can download OC4J at oracle.com/technology/software/products/ias/preview.html.

The solution also requires Oracle XDS Preview. To install Oracle XDS Preview, download Oracle XDS. Oracle XDS is packaged as an xds.zip file. Unzip the xds.zip download in the directory (for the purposes of this article, <XDS_INSTALL_UNZIP_DIR>) from which you are going to run the installation.

Note: This directory should not be ORACLE_HOME (oracle.home).

The following are abbreviated instructions and settings for Oracle XDS installation. For more-detailed instructions, open install_guide.html from the <XDS_INSTALL_UNZIP_DIR> directory in a browser.

After unzipping xds.zip, open the file install.properties in the <XDS_INSTALL_UNZIP_DIR> directory, using any text editor, and set the following appropriate properties: 

  • Set oracle.home to point to the directory where you want to install the new Oracle XDS OC4J instance.

  • Set Your_Directory_For_OC4J_Standalone to point to the directory where you want to install the new Oracle XDS OC4J instance.

Note that you may also point to your existing ORACLE_HOME if you have OC4J 10g (10.1.3)—Developer Preview 3, Build 041121—installed in that location. Also note that this directory cannot be where you have unzipped the xds.zip distribution.

Other properties can be set as follows: 

  • deploy.host = Hostname/IP address on which Oracle XDS is to be installed.

  • admin.username = Admin username for the OC4J instance. Default is admin.

  • admin.password = Admin password for the OC4J instance. Default is welcome.

  • proxy.host = HTTP proxy/firewall server hostname/IP address if your environment requires a proxy in order to access the internet.

  • proxy.port = HTTP proxy/firewall server port number if your Oracle XDS machine requires a proxy in order to access the internet.

  • nonProxyHosts=List of hostnames for which HTTP proxy should not be used.

(Besides the localhost, add your actual hostname to the list.)

The following database properties are required only for the Oracle XDS demo: 

  • database.host = Hostname/IP address of your database instance (replace Your_Oracle_Database_Host_Name).

  • database.port = Port number for the database listener.

  • database.sid = SID name of the database instance (replace Your_Oracle_Database_SID).

  • database.username = Database username. Default value is oe.

  • database.password = Password for the database user. Default value is oe . (Change only if the oe schema password is changed for your database instance.)

Note: Confirm the availability of the Order Entry schema ( oe ) on your Oracle database installation. If the oe schema user is locked, unlock it before using it.

After you have set all the required properties, install Oracle XDS, by running the ant -q install command in the <XDS_INSTALL_UNZIP_DIR> directory. (The install program copies the custom install.properties file that you edited to point to this location.)

You can now run ant startXDS from the <ORACLE_HOME> directory to start OC4J and try the basic Oracle XDS demo, "Basic Customer Order Search." To stop OC4J, run ant stopXDS from the <ORACLE_HOME> directory.

Once Oracle XDS is running and the Oracle XDS demo is installed and configured, you can go to localhost:8888/xdsdemos to try out the Oracle XDS Customer Orders application.

Note: This Oracle XDS download is a preview of the Oracle XDS technology. As such, the configuration files and query syntax are not final and are subject to change.

Setting up the datasources. Once Oracle XDS is installed, you can set up the required datasources to create the solution for our real-life user scenario. Set up the new datasources in the Oracle XDS demo application's source configuration XML file, at <ORACLE_HOME>/j2ee/home/applications/XDSDemo/META-INF/xds/config/xds-config.xml. The XML schema of this configuration file is located in the same directory.

All the datasources Oracle XDS uses are configured in an xds-config.xml file. To learn the structure of the xds-config.xml file, check out the schema file xds-config.xsd. To add a new datasource, add an xds-source tag to the xds-config.xml file. Then place the file in the META-INF/xds/config directory of your application archive (EAR file). For example, after the Oracle XDS demo application is deployed, you will find the configuration file in the <ORACLE_HOME>/applications/XDSDemo/META-INF/xds/config directory.

The <ORACLE_HOME>/doc/XDS-step-by-step.html document provides detailed information about setting up new Oracle XDS datasources.

Listings 1 through 4 contain the configured datasources located in the xds-config.xml file. Listing 1 shows the first datasource: an Oracle database Order Entry schema described via a Web Service Description Language (WSDL) source named orderInfo.

Code Listing 1: Order Entry (oe) database schema datasource 

<wsdl-source isCached="false">
<name>orderInfo</name>
	<namespace>http://xmlns.oracle.com/ias/xds</namespace>
	<input-parameters>
	       <part position="1" name="order_id" 
	                                type="xs:int"/>
	</input-parameters>
	<wsdlURL>http://myHost:8888/xdsdemos/orderInfo.wsdl
</wsdlURL>
	<operation>getOrderByID</operation>
	<service>OESQLService</service>
	<port>OESQLPort</port> 
</wsdl-source>


A sample demo WSDL for this source is part of the Oracle XDS demo application. You can edit this WSDL to change the SQL query to access data from database tables. The XDS SQL adapter transparently translates the relational data from the database to XML.

The second source is a file system or document source that accesses a spreadsheet in .CSV (comma-separated values) format that is translated to XML by use of Oracle Data Definition Description Language (D3L) translation. The translation is done according to rules specified in the template myHost:8888/xdsdemos/paymentInfoExcel.xml. Listing 2 shows this document source.

Code Listing 2: Document (CSV file) datasource 

<document-source isCached="true">
	<name>paymentStatusInfo</name>
	<namespace>http://xmlns.oracle.com/ias/xds</namespace>
	<cache-properties time-to-live="600">
	       <in-memory useSpool="true" useDiskCache="false"/>
</cache-properties>
	<target>http://myHost:8888/xdsdemos/paymentInfo.csv</target>
<XMLTranslate method="D3L">
http://myHost:8888/xdsdemos/paymentInfoExcel.xml
</XMLTranslate>
</document-source>


The next datasource is an enterprise information system (EIS) application that stores customer information. Oracle XDS uses J2CA to access data from this EIS system. Listing 3 shows this application datasource.

Code Listing 3: Application datasource (using J2CA) 

<wsdl-source isCached="false">
<name>customerInfo</name>
	<namespace>http://xmlns.oracle.com/ias/xds</namespace>
	<input-parameters>
	   <part position="1" name="key" 
	                           type="xs:string"/>
	</input-parameters>
<wsdlURL>http://myHost:8888/xdsdemos/CustomerInfo.wsdl</wsdlURL>
	<operation>getCustomerByKey</operation>
	<service>CustomerInfoMYEISService</service>
	<port>CustomerInfo</port>
        <typeMap xmlType="http://services.myeis.com/:Customer"
typeClass="org.w3c.dom.Node"/>
</wsdl-source>


The last datasource is a Web service that returns shipment tracking information, given a tracking number. Listing 4 shows this Web service datasource.

Code Listing 4: Web service datasource 

<wsdl-source isCached="true">
<name>shippingInfo</name>
<namespace>http://xmlns.oracle.com/ias/xds</namespace>
<cache-properties time-to-live="3600"/>
<input-parameters>
	   <part position="1" name="trackingNumber" 
	                           type="xs:string"/>
	</input-parameters>      
<wsdlURL>http://myHost/ShipmentTracking.wsdl</wsdlURL>
<operation>GetTrackingInfo</operation>
<service>GetTrackingInfoService</service>
<port>GetTrackingInfoPort</port> 
</wsdl-source>


Writing the XQuery. With all of the datasources configured in the xds-config.xml file, we can now retrieve the information. To retrieve the information for display in the dashboard, we will write the XQuery FLOWR expression shown in Listing 5.

Code Listing 5: XQuery FLOWR for dashboard 

declare namespace xds = "http://xmlns.oracle.com/ias/xds" ;
                     (: Returns payment info for all customers :)
declare function xds:paymentStatusInfo () external;
                     (: Returns customer info given customer name :)
declare function xds:customerInfo ($name as xs:string) external;
                    (: Returns order info given customer key :)
declare function xds:orderInfo ($orderId as xs:integer) external;
                   (: Shipping Info :)
declare function xds:shippingInfo( $trackNo as xs:string) external;
                  (: query parameter for passing customer name :)
declare variable $custName external;
                  (: Prolog END :)
let $custInfo := xds:customerInfo($custName)
return 
               (: Return Customer information from EIS Source :)
 <Result>
<CUSTOMER_INFO>
	       <Name> { $custInfo/name } </Name>
	       <Company> { $custInfo/company} </Company>
	       <Address> { $custInfo/address} </Address>
	</CUSTOMER_INFO>
	<ORDERS>{
(: Return Customer orders information from Financial Spreadsheet where customer key :)
(: is equal to customer key for the current customer (with name $custame) :)
for $custOrderInfo in xds:paymentStatusInfo()/excel/Row[CustomerKey eq $custInfo/key]
(: Get order information for each order with ID from OrderInfo database source :)

let $orderInfo := xds:orderInfo($custOrderInfo/OrderId)
return 
(: Return Each Order information along with payment status of the order and shipping :)
(: status as returned by Shipment tracking web service :)
	<ORDER>
	  <Amount>{$custOrderInfo/Amount/text()}</Amount>
  <PaymentStatus>{$custOrderInfo/PaymentStatus/text()}</PaymentStatus> 
	  <LINEITEMS>{ $orderInfo/LineItems }</LINEITEMS>
  <SHIPPING_STATUS>
    { xds:shippingInfo($orderInfo/trackingNo) }
  </SHIPPING_STATUS>
      </ORDER>
        }</ORDERS>
  </Result>
                            


In this XQuery expression, all the configured sources are accessed as external functions. The steps in the expression are explained by comments (: comments :) .

Displaying the results. The XQuery expression shown in Listing 5 can be passed on as an ad hoc query to Oracle XDS, or the query can be stored for reuse. You can store the XQuery expression in Listing 5 and then execute it by using one of the Oracle XDS client APIs: 

  • Java client API

  • JSP tag library

  • Session EJB

The oracle.xds.client.XDSFacade Java client is the simplest of the clients. You can find the Javadoc for the bean in the <Oracle_HOME>/doc/api directory. You can find sample code using the XDS Bean API at localhost:8888/xdsdemos/servlet/ViewSrc/CustomerOrders/UserShowResultsBean.jsp.

This XDSFacade class provides the following methods of executing an XQuery expression and returning the result: 

public void execute(String xquery, QueryParameter[] bindings); 
public void executeView(String viewName, QueryParameter[] bindings); 
public OXMLItem getNextItem(); 

 

Next Steps


DOWNLOAD

 Oracle XDS

The client APIs are discussed in the XDS-step-by-step.html document, and sample code for each of these client APIs is available in the Oracle XDS demo application.

Executing the XQuery expression in Listing 5 produces the results in the XML output shown in Listing 6. You can use XSLT to transform this XML and display the results in the dashboard with HTML formatting.

Code Listing 10: XQuery result 

<Result>
      <CUSTOMER_INFO>
         <Name>Goldie Slater</Name>
         <Company>Acme, Inc.</Company>
         <Address>200 First Street, San Jose, CA</Address>
      </CUSTOMER_INFO>
      <ORDERS>
         <ORDER>
            <Amount>282694.3</Amount>
            <PaymentStatus>Paid</PaymentStatus>
            <LINEITMS>
                     <Item>
                        <Name>LL201 - R</Name>
                        <Price>1.1</Price>
                        <Quantity>105</Quantity>
                        <ProductStatus>shipped</ProductStatus>
                     </Item>
                     <Item>
                        <Name>XRT - W/48</Name>
                        <Price>2341.9</Price>
                        <Quantity>112</Quantity>
                        <ProductStatus>on order</ProductStatus>
                     </Item>
                     <Item>
                        <Name>KB 101/ES</Name>
                        <Price>43</Price>
                        <Quantity>150</Quantity>
                        <ProductStatus>shipped</ProductStatus>
                     </Item>
                     <Item>
                        <Name>MB - S600</Name>
                        <Price>94</Price>
                        <Quantity>90</Quantity>
                        <ProductStatus>shipped</ProductStatus>
                     </Item>
                     <Item>
                        <Name>FGXT22 /E32</Name>
                        <Price>56</Price>
                        <Quantity>96</Quantity>
                        <ProductStatus>on order</ProductStatus>
                     </Item>
             </LINEITEMS>
             <SHIPPING_STATUS>
	       <TrackingNumber>1Z1689W20341211926</TrackingNumber>
               <DeliveredTo>Slater</DeliveredTo>
                <DeliveryLocation>San Jose</DeliveryLocation>
                <DeliveryDateTime>09/12/04 10:30 AM</DeliveryDateTime>
                <SignedBy>Mail Staff</SignedBy>
                <Carrier>UPS</Carrier>
                <CurrentLocation>N/A</CurrentLocation>
	     </SHIPPING_STATUS>  
         </ORDER>
      </ORDERS>
   </Result>


Conclusion

XML and XQuery technologies can form the basis of an effective real-time information integration solution. Specifically, Oracle XDS allows users to easily access and query across a diverse set of data, including databases, Web services, and packaged and legacy applications.


Nilesh Junnarkar (nilesh.junnarkar@oracle.com) is a software development manager in Oracle's Java 2 Enterprise Edition (J2EE) server group. Nirav Chanchani (nirav.chanchani@oracle.com) is a principal product manager, Server Technologies, at Oracle. Julie Basu (julie.basu@oracle.com) is a senior development manager in the Java platform group at Oracle.


Send us your comments