Liquid Data by Example

Query Cookbook

This section provides examples of BEA Liquid Data for WebLogic queries using some of the advanced features and tools offered in the Data View Builder. This book assumes that you are familiar with the Data View Builder user interface and that you have an understanding of the basic concepts and tasks using the Data View Builder. For details on using the Data View Builder, see Building Queries and Data Views.

The following use cases and examples are provided here to give you a jump-start for constructing real-world queries to solve common problems. Each use case includes a viewlet demo of building the solution using Data View Builder. Watching a viewlet takes 3 to 5 minutes.

For an example of using a stored procedure in a query, see Example: Defining and Using a Customer Orders Stored Procedure.

Each use case has an example with a description of the problem and the steps to solve the problem. The examples use two databases:

The BroadBand database (PB-BB) contains "BroadBand" subscribers and service orders
The Wireless database (PB-WL) contains "Wireless" subscribers.

In cases where the target schemas do not already exist in the Liquid Data Samples Server repository, they are provided in this documentation along with the examples. You can cut-and-paste the schema content into a.xsd file to construct your own target schemas. (You can also copy from the PDF version of this document which may give you a copy that formats better your text editor.)

Note: To find out what data are contained in any data source, create a new "test" project, open the source schema you are interested in, and map key source nodes to any appropriate target schema. (For example, map customer first and last names and customer ID from source to target schemas.) Then click on Test tab and choose Query—>Run Query. The result will return all customers in the data source queried.

As you work through the examples, remember to save any projects that you want to keep before creating new ones.

Example 1: Simple Joins

A join merges data from two data sources based on a certain relation.

The Problem

For each Wireless Customer ID, determine whether the customer has any BroadBand orders. Assume that the Customer ID matches across databases.

The Solution

First, you want to find matching BroadBand customers (who are also included in the Wireless database), then return BroadBand Order IDs for the matching customers. Because Customer IDs in the Wireless database align with those in BroadBand, we can find matching BroadBand customers with a simple join of Wireless Customer IDs with the Customer IDs in the BroadBand order information.

To create the solution, follow these steps:

View a Demo

Simple Joins Demo... If you are looking at this documentation online, you can click the "Demo" button to see a viewlet demo showing how to build the conditions and create the mappings described in this example. This demo previews the steps described in detail in the following sections. The demo assumes you already have the target schema in the Liquid Data Samples Server repository.

  

Ex 1: Step 1. Verify the Target Schema is Saved in Repository

For this example, we will use a target schema called customerOrders.xsd. This schema is available in the Liquid Data Samples Server repository. The path to the schemas folder is:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/

Just in case you want to verify that you have the right schema file, the following code listing shows the XML for this schema.

Listing 2-1 XML Source for customerOrders.xsd Target Schema File

<?xml version = "1.0" encoding = "UTF-8"?>

<xsd:schema xmlns:xsd = "http://www.w3.org/2001/XMLSchema">
	<xsd:element name = "customers">
		<xsd:complexType>
			<xsd:sequence>
				<xsd:element ref = "customer" minOccurs = "0" maxOccurs = "unbounded"/>
			</xsd:sequence>
		</xsd:complexType>
	</xsd:element>
	<xsd:element name = "customer">
		<xsd:complexType>
			<xsd:sequence>
				<xsd:element ref = "first_name"/>
				<xsd:element ref = "last_name"/>
				<xsd:element ref = "orders" minOccurs = "0" maxOccurs = "unbounded"/>
			</xsd:sequence>
			<xsd:attribute name = "id" use = "optional" type = "xsd:string"/>
		</xsd:complexType>
	</xsd:element>
	<xsd:element name = "first_name" type = "xsd:string"/>
	<xsd:element name = "last_name" type = "xsd:string"/>
	<xsd:element name = "orders">
		<xsd:complexType>
			<xsd:sequence>
				<xsd:element ref = "order" minOccurs = "0" maxOccurs = "unbounded"/>
			</xsd:sequence>
		</xsd:complexType>
	</xsd:element>
	<xsd:element name = "order">
		<xsd:complexType>
			<xsd:attribute name = "id" use = "optional" type = "xsd:string"/>
			<xsd:attribute name = "date" use = "optional" type = "xsd:string"/>
			<xsd:attribute name = "amount" use = "optional" type = "xsd:string"/>
		</xsd:complexType>
	</xsd:element>
</xsd:schema>

Ex 1: Step 2. Open Source and Target Schemas

In the Data View Builder, choose File—>New Project to clear your desktop and reset all default values.

On the Builder Design—>Sources tab, click Relational Databases and open two data sources:

Double-click on the PB-WL (Wireless) relational database to open the schema for this data source.
Double-click on the PB-BB (BroadBand) relational database to open the schema for this data source.

Choose the menu option File—>Set Target Schema.

Navigate to the Liquid Data Samples Server repository or to the location where you saved the customerOrders.xsd schema. Choose customerOrders.xsd and click Open.
customerOrders.xsd appears as the target schema.
This target schema is displayed as a docked schema window on the right side of the design area.

Click the plus (+) sign (or right-mouse click and choose Expand) to expand the nodes in each source schema and in the target schema.

Ex 1: Step 3. Map Nodes from Source to Target Schema to Project the Output

Drag and drop [PB-WL]db/CUSTOMER/CUSTOMER_ID from source schema onto the target schema [customerOrders.xsd]/customers/customer/id

Drag and drop [PB-WL]db/CUSTOMER/FIRST_NAME from source schema onto the target schema [customerOrders.xsd]/customers/customer/first_name

Drag and drop [PB-WL]db/CUSTOMER/LAST_NAME from source schema onto the target schema [customerOrders.xsd]/customers/customer/last_name

Drag and drop [PB-BB]db/CUSTOMER_ORDER/ORDER_DATE onto the target schema [customerOrders.xsd]customers/customer/orders/order/order_date

Drag and drop [PB-BB]db/CUSTOMER_ORDER/ORDER_ID onto the target schema [customerOrders.xsd]customers/customer/orders/order/id

Ex 1: Step 4. Create a Query Parameter for a Customer ID to be Provided at Query Runtime

Create a Query Parameter wireless_id variable for a Wireless Customer ID that you will supply at query execution time:

On the Builder Design, click Toolbox and then click Query Parameter.

From the Type drop-down menu, choose xs:string.

In Parameter Name field, enter wireless_id and click Add.

The new parameter is displayed in the Query Parameters tree.

Ex 1: Step 5. Assign the Query Parameter to a Source Node

Drag and drop the wireless_id query parameter to [PB-WL]db/CUSTOMER/CUSTOMER_ID.

Ex 1: Step 6. Join the Wireless and BroadBand Customer IDs

Drag and drop [PB-WL]db/CUSTOMER/CUSTOMER_ID to [PB-BB]db/CUSTOMER_ORDER/CUSTOMER_ID

Ex 1: Step 7. View the XQuery and Run the Query to Test it

Click on the Test tab.

The generated XQuery for this query is shown in the following code listing.

Listing 2-2 XQuery for Example 1: Simple Joins

<customers>
	{
	for $PB_WL.CUSTOMER_1 in document("PB-WL")/db/CUSTOMER
	where ($#wireless_id of type xs:string  eq  $PB_WL.CUSTOMER_1/CUSTOMER_ID)
	return
	<customer id={$PB_WL.CUSTOMER_1/CUSTOMER_ID}>
		<first_name>{ xf:data($PB_WL.CUSTOMER_1/FIRST_NAME) }</first_name>
		<last_name>{ xf:data($PB_WL.CUSTOMER_1/LAST_NAME) }</last_name>
		<orders>
			{
			for $PB_BB.CUSTOMER_ORDER_2 in document("PB-BB")/db/CUSTOMER_ORDER
			where ($PB_WL.CUSTOMER_1/CUSTOMER_ID  eq  $PB_BB.CUSTOMER_ORDER_2/CUSTOMER_ID)
			return
			<order id={$PB_BB.CUSTOMER_ORDER_2/ORDER_ID} date={cast as xs:string($PB_BB.CUSTOMER_ORDER_2/ORDER_DATE)}></order>
			}
		</orders>
	</customer>
	}
</customers>

Set the variable value to submit to the query when the query runs. To do this, you need to enter a value in the Query Parameter panel. Click into the cell under Value and enter CUSTOMER_3.

(Customer IDs CUSTOMER_1 through CUSTOMER_10 are available in the data source to try.)

Click the Run query button to run the query against the data sources.

Ex. 1: Step 8. Verify the Result

Running this query with the wireless_id parameter set to CUSTOMER_3 produces the following XML query result.

Listing 2-3 Result for Example 1: Simple Joins

<customers>
  <customer id="CUSTOMER_3">
    <first_name>JOHN_3</first_name>
    <last_name>KAY_3</last_name>
    <orders>
      <order date="2002-03-06-08:00" id="ORDER_ID_3_0"/>
      <order date="2002-03-06-08:00" id="ORDER_ID_3_1"/>
      <order date="2002-03-06-08:00" id="ORDER_ID_3_2"/>
      <order date="2002-03-06-08:00" id="ORDER_ID_3_3"/>
    </orders>
  </customer>
</customers>

Example 2: Retrieving Information

The Problem

Find Customer IDs for customers who have both Wireless and BroadBand accounts and include in the generated data the state each customer resides in.

The Solution

This example shows how to do the following:

Project output
Specify the order of the result

Open Data Sources and Add a Target Schema

Choose File —> New Project to clear your desktop and reset all default values.

On the Builder Toolbar —> Sources tab, click Relational Databases and open two data sources:

Double-click on the PB-WL relational database to open the schema for this data source.
Double-click on the PB-BB relational database to open the schema for this data source.

Choose File —> Set Target Schema. Use the file browser to navigate to the Liquid Data Samples Server repository and select amtByState.xsd as the target schema.

Note: If amtByState.xsd is not already available from the repository, you can create and save it yourself. For a copy of the schema file and instructions on how to save it to the Liquid Data Samples Server repository, see Target Schemas in Building Queries and Data Views. The schema itself is shown in Listing 5-1 of the same book.

This target schema is displayed as a docked schema window on the right side of the workspace.

Map Elements from Source to Target Schema to Project Output

To project Customer first and last names and state to Target, do the following:

Drag and drop Wireless [PB-WL]/FIRST_NAME (under CUSTOMER*) onto FIRST_NAME in the Target schema.

Drag and drop Wireless [PB-WL]/LAST_NAME (under CUSTOMER*) onto LAST_NAME in the Target schema.

Drag and drop Wireless [PB-WL]/STATE (under CUSTOMER*) onto STATE (under CUSTOMER*) in the Target schema.

Join Two Sources

To create a join between Wireless [PB-WL] and BroadBand [PB-BB] on customer IDs, do the following:

Drag and drop BroadBand [PB-BB]/CUSTOMER_ID (under CUSTOMER*) onto the associated Wireless [PB-WL]/CUSTOMER_ID element.

The following shows the mappings in the Data View Builder.

Figure 2-1 Query to Identify Customers by Customer ID and Sort by State

Query to Identify Customers by Customer ID and Sort by State

Specify the Order of the Result Using the Sort By Features

To order the output alphabetically by State do the following:

Click the Sort By tab.

This tab shows repeatable elements in the target schema with subordinate fields that you can select for ordering.

From the drop-down menu choose CUSTOMER, and then click into the Direction cell next to STATE and set STATE to Ascending.

This will cause the query to display the results in ascending order by state.

View and Run the Query

Now that you have built the query, you can switch to the Test tab to view the generated XQuery and run the query to see the kind of result it returns.

Click on the Test tab.

The generated XQuery for this query is shown in the following code listing.

Listing 2-4 XQuery for Example: Query Customers by ID and Sort by State

<customers>
	{
	for $PB_WL.CUSTOMER_1 in document("PB-WL")/db/CUSTOMER
	where xf:not(xf:empty(
		for $PB_BB.CUSTOMER_2 in document("PB-BB")/db/CUSTOMER
		where ($PB_BB.CUSTOMER_2/CUSTOMER_ID  eq  $PB_WL.CUSTOMER_1/CUSTOMER_ID)
		return
		xf:true()))
	return
	<CUSTOMER>
		<FIRST_NAME>{ xf:data($PB_WL.CUSTOMER_1/FIRST_NAME) }</FIRST_NAME>
		<LAST_NAME>{ xf:data($PB_WL.CUSTOMER_1/LAST_NAME) }</LAST_NAME>
		<STATE>{ xf:data($PB_WL.CUSTOMER_1/STATE) }</STATE>
	</CUSTOMER>
	sortby(STATE ascending)
	}
</customers>

Click the Run query button to run the query against the data sources.

Querying these data sources as described in this example produces the XML query result shown in the following code listing. Figure 2-2 XML Result for Example: Query Customers by ID and Sort by State

<customers>
  <CUSTOMER>
    <FIRST_NAME>JOHN_3</FIRST_NAME>
    <LAST_NAME>KAY_3</LAST_NAME>
    <STATE>AZ</STATE>
  </CUSTOMER>
  <CUSTOMER>
    <FIRST_NAME>JOHN_8</FIRST_NAME>
    <LAST_NAME>KAY_8</LAST_NAME>
    <STATE>AZ</STATE>
  </CUSTOMER>
  <CUSTOMER>
    <FIRST_NAME>JOHN_10</FIRST_NAME>
    <LAST_NAME>KAY_10</LAST_NAME>
    <STATE>CA</STATE>
  </CUSTOMER>
  <CUSTOMER>
    <FIRST_NAME>JOHN_5</FIRST_NAME>
    <LAST_NAME>KAY_5</LAST_NAME>
    <STATE>CA</STATE>
  </CUSTOMER>
  <CUSTOMER>
    <FIRST_NAME>JOHN_4</FIRST_NAME>
    <LAST_NAME>KAY_4</LAST_NAME>
    <STATE>NV</STATE>
  </CUSTOMER>
  <CUSTOMER>
    <FIRST_NAME>JOHN_9</FIRST_NAME>
    <LAST_NAME>KAY_9</LAST_NAME>
    <STATE>NV</STATE>
  </CUSTOMER>
  <CUSTOMER>
    <FIRST_NAME>JOHN_1</FIRST_NAME>
    <LAST_NAME>KAY_1</LAST_NAME>
    <STATE>TX</STATE>
  </CUSTOMER>
  <CUSTOMER>
    <FIRST_NAME>JOHN_6</FIRST_NAME>
    <LAST_NAME>KAY_6</LAST_NAME>
    <STATE>TX</STATE>
  </CUSTOMER>
  <CUSTOMER>
    <FIRST_NAME>JOHN_2</FIRST_NAME>
    <LAST_NAME>KAY_2</LAST_NAME>
    <STATE>WA</STATE>
  </CUSTOMER>
  <CUSTOMER>
    <FIRST_NAME>JOHN_7</FIRST_NAME>
    <LAST_NAME>KAY_7</LAST_NAME>
    <STATE>WA</STATE>
  </CUSTOMER>
</customers>

Example 3: Aggregates

Aggregate functions produce a single value from a set of input values. An example of an aggregate function in Data View Builder is the count function, which takes a list of values and returns the number of values in the list.

The Problem

Find the number of orders placed in the BroadBand database for a given customer who is also in the Wireless database.

The Solution

This query relies on a data view called AllOrders which retrieves customers who are in the BroadBand database and also in the Wireless database. For each of these customers, the customer ID and orders are retrieved. Then, we use the Aggregate function count to determine how many orders are associated with a given customer. At query runtime, a customer ID is submitted as a query parameter and the result returns the number of orders associated with the given customer ID.

To create the solution, follow these steps:

View a Demo

Aggregates Demo... If you are looking at this documentation online, you can click the "Demo" button to see a viewlet demo showing how to build the conditions and create the mappings described in this example. This demo previews the steps described in detail in the following sections. The demo assumes you already have the target schema in the Liquid Data Samples Server repository and have created and configured the data view data source required for this example.

  

Ex 3: Step 1. Configure the "AllOrders" Stored Query as a Data View

For this example, we will use a data view data source called AllOrders.xv. However, before this data view can be used, it must first be created.

Figure 2-3 XML Source for AllOrders.xv Data View File

<customers>
	{
	for $PB-BB.CUSTOMER_1 in document("PB-BB")/db/CUSTOMER
	for $PB-WL.CUSTOMER_2 in document("PB-WL")/db/CUSTOMER
	where ($PB-WL.CUSTOMER_2/CUSTOMER_ID  eq $PB-BB.CUSTOMER_1/CUSTOMER_ID)
	   
	return
	<customer id={$PB-WL.CUSTOMER_2/CUSTOMER_ID}>
		<first_name>{ xf:data($PB-WL.CUSTOMER_2/FIRST_NAME) }</first_name>
		<last_name>{ xf:data($PB-WL.CUSTOMER_2/LAST_NAME) }</last_name>
		<orders>
			{
			for $PB-BB.CUSTOMER_ORDER_4 in document("PB-BB")/db/CUSTOMER_ORDER
			where ($PB-BB.CUSTOMER_1/CUSTOMER_ID  eq $PB-BB.CUSTOMER_ORDER_4/CUSTOMER_ID)
			return
			<order id={$PB-BB.CUSTOMER_ORDER_4/ORDER_ID} date={$PB-BB.CUSTOMER_ORDER_4/ORDER_DATE}></order>
			}
		</orders>
	</customer>
	}
</customers>

Use the WLS Administration Console to Configure Your Data View Data Source

Start and login to the WLS Administration Console for the Samples server you are using.

To start the WLS Administration Console for the Liquid Data Samples server running on your local machine, type the following URL in a browser address field: http://localhost:7001/console
Login to the console by providing the following default username and password for the Samples server.

Table 2-4

Field

Defaults

Username

system

Password

security

User Name and Password for Samples WLS Administration Console

Field	Defaults
Username	`system`
Password	`security`

In the left pane, click the Liquid Data node.

In the right pane click the Stored Queries tab.

Locate AllOrders among the queries (it will be near the bottom of the listing).

Click the Configure link.

For a schema enter customerOrders.xsd or browse to the file. (You do not need to enter a namespace URI or schema root element name for this example.)

Click Create.

Figure 2-5 Configuring Liquid Data Source Description for a Data View

Configuring Liquid Data Source Description for a Data View

Find all orders in the list of stored queries (now it should be near the top of the listing).

Click the Create Data View link.

The Create a Data View dialog box is displayed.

Provide a name such as AllOrders for your new data view, then click Create.

Available data views will display. AllOrders should appear in the list.

Ex 3: Step 2. Restart the Data View Builder and Find the New Data View

Reconnect to the Data View Builder by selecting File—>Connect.

On the Design tab, on the Builder Toolbar, click the Sources tab, then click Data Views.

The AllOrders.xv data view should be displayed in the list of available data views.

Ex 3: Step 3. Verify that the Target Schema is Saved in the Repository

For this example, we will use a target schema called customerOrdersA.xsd. This schema is available in the Liquid Data Samples Server repository. The path to the schemas folder is:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/

Just in case you want to verify that you have the right schema file, the following code listing shows the XML for this schema.

Listing 2-5 XML Source for customerOrdersA.xsd Target Schema File

<?xml version = "1.0" encoding = "UTF-8"?>

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
	<xsd:element name="customers">
		<xsd:complexType>
			<xsd:sequence>
				<xsd:element name="customer" minOccurs="0" maxOccurs="unbounded">
					<xsd:complexType>
						<xsd:sequence>
							<xsd:element name="first_name" type="xsd:string"/>
							<xsd:element name="last_name" type="xsd:string"/>
							<xsd:element name="orders" minOccurs="0" maxOccurs="unbounded">
								<xsd:complexType>
									<xsd:sequence>
										<xsd:element name="order" minOccurs="0" maxOccurs="unbounded">
											<xsd:complexType>
												<xsd:sequence>
												</xsd:sequence>
											<xsd:attribute name="id" type="xsd:string"/>
											<xsd:attribute name="date" type="xsd:string"/>
											<xsd:attribute name="amount" type="xsd:string"/>
											</xsd:complexType>
										</xsd:element>
									</xsd:sequence>
								</xsd:complexType>
							</xsd:element>
							<xsd:element name="amount" type="xsd:string"/>
						</xsd:sequence>
					<xsd:attribute name="id" type="xsd:string"/>
					</xsd:complexType>
				</xsd:element>
			</xsd:sequence>
		</xsd:complexType>
	</xsd:element>
</xsd:schema>

Ex 3: Step 4. Open the Data Sources and Target Schema

In the Data View Builder, choose File —> New Project to clear your desktop and reset all default values.

On the Builder Design —> Sources tab, click Data Views, and double-click on AllOrders.xv to open the schema for that data source.

Choose File —> Set Target Schema. Use the file browser to navigate to the Liquid Data Samples Server repository. Select CustomerOrdersA.xsd as the target schema. This target schema is displayed as a docked schema window on the right side of the design area.

Ex 3: Step 5. Map Source Nodes to Target to Project the Output

Drag and drop [AllOrders]/ customers/customer/first_name from the AllOrders source schema onto [CustomerOrdersA.xsd]/customers/customer/first_name in the target schema.

Drag and drop [AllOrders]/ customers/customer/last_name from AllOrders source schema onto [CustomerOrdersA.xsd]/customers/customer/last_name in the target schema.

Ex 3: Step 6. Create Two Query Parameters to be Provided at Query Runtime

Create two Query Parameter variables: first_name and last_name, that you can use to insert variable customer information when the query runs. Create both variables as type xs:string. Do this as follows:

On the Builder Toolbar, click Toolbox and then click Query Parameter.

From the Type drop-down menu, choose xs:string.

In Parameter Name field, enter first_name and click Add.

The new parameter is displayed in the Query Parameters tree.

Repeat steps 2 and 3 to create the last_name variable.

You should now see both parameters displayed in the Query Parameters tree.

Ex 3: Step 7. Assign the Query Parameters to Source Nodes

Assign the first_name and last_name Query Parameter variables to customer first name and last name nodes in the AllOrders data view as follows:

Drag and drop the first_name variable onto [allOrders]/customers/customer/first_name in the AllOrders source schema.

Drag and drop the last_name variable onto [allOrders]/ customers/customer/last_name in the AllOrders source schema.

Ex 3: Step 8. Add the Count XQuery Function

Add the count XQuery function and specify the input and output as follows:

On the Builder Toolbar, click Toolbox and then click XQuery Functions.

Double-click on the count function (under Aggregate Functions)

The count function window is displayed, showing input parameter srcval and output as some integer result.

Note: Create complex or aggregate functions only on the desktop by double-clicking as described in this step. Do not attempt to drag and drop them directly into the Conditions tab.

Drag and drop [AllOrders]/customer/orders/order/date from the AllOrders source schema onto [count-Function]input/Parameters/srcval.

Drag and drop [count-Function]Output/result to [customerOrdersA.xsd]/customers/customer/amount in the target schema.

Note: Make sure to drag result onto the customer amount — the last node in the fully expanded schema tree; not onto the optional orders amount?

Ex 3: Step 9. Verify Mappings and Conditions

Your mappings should look like those shown in Figure 2-6.

Figure 2-6 Mappings for Example2: Aggregates

Mappings for Example2: Aggregates

Your Conditions should like those shown in Figure 2-7.

Figure 2-7 Conditions for Example 2: Aggregates

Conditions for Example 2: Aggregates

Ex 3: Step 10. View the XQuery and Test by Running the Query

Click on the Test tab.

The generated XQuery for this query is shown in the following code listing.

Listing 2-6 XQuery for Example 2: Aggregates

namespace view = "urn:views"
<customers>
	{
	for $view:AllOrders.customer_2 in view:AllOrders()/customers/customer
	let $srcval_3 := 
	               for $view:AllOrders.orders_5 in $view:AllOrders.customer_2/orders
	               for $view:AllOrders.order_6 in $view:AllOrders.orders_5/order
	               return
	               xf:data($view:AllOrders.order_6/@date)
	where ($#last_name of type xs:string  eq  $view:AllOrders.customer_2/last_name)
	  and ($#first_name of type xs:string  eq  $view:AllOrders.customer_2/first_name)
	return
	<customer>
		<first_name>{ xf:data($view:AllOrders.customer_2/first_name) }</first_name>
		<last_name>{ xf:data($view:AllOrders.customer_2/last_name) }</last_name>
		<amount>{ cast as xs:string(xf:count($srcval_3)) }</amount>
	</customer>
	}
</customers>

In the Query Parameter panel on the Test tab, set the variable values as follows:

last_name

(For last_name, KAY_1 through KAY_10 are available in the data source.)

first_name

(For first_name, JOHN_1 through JOHN_10 are available in the data source.)

Click the Run query button to run the query against the data sources.

Ex 3: Step 11. Verify the Result

Running this query with last_name set to KAY_1 and first_name set to JOHN_1 produces the following XML query result.

Listing 2-7 Result for Example 2: Aggregates

<customers>
  <customer>
    <first_name>JOHN_1</first_name>
    <last_name>KAY_1</last_name>
    <amount>2</amount>
  </customer>
</customers>

Example 4: Date and Time Duration

Data View Builder supports a set of functions that operate on date and time. (For more information on date and time functions see Date and Time Functions in the XQuery Reference Guide.)

The Problem

Determine if a BroadBand customer has any open orders in the BroadBand database before a specified date.

The Solution

For each BroadBand order that matches the given Customer ID, you need to set these conditions:

The order status is "OPEN"
The ship date for a given customer_id is earlier than or equal to the date (date1) provided. (customer_id and date1 are a variables that you define as query parameters to be submitted at query runtime).

To create the solution, follow these steps:

View a Demo

Date and Time Duration Demo... If you are looking at this documentation online, you can click the "Demo" button to see a viewlet demo showing how to build the conditions and create the mappings described in this example. This demo previews the steps described in detail in the following sections. The demo assumes you already have the target schema in the Liquid Data Samples Server repository.

  

Ex 4: Step 1. Verify the Target Schema is Saved in Repository

For this example, we will use a target schema called customerLineItems.xsd. This schema is available in the Liquid Data Samples Server repository.

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/

Just in case you want to verify that you have the right schema file, the following code listing shows the XML for this schema.

Listing 2-8 XML Source for customerLineItems.xsd Target Schema File

<?xml version = "1.0" encoding = "UTF-8"?>

<xsd:schema xmlns:xsd = "http://www.w3.org/2001/XMLSchema">
	<xsd:element name = "customers">
		<xsd:complexType>
			<xsd:sequence>
				<xsd:element ref = "customer" minOccurs = "0" maxOccurs = "unbounded"/>
			</xsd:sequence>
		</xsd:complexType>
	</xsd:element>
	<xsd:element name = "customer">
		<xsd:complexType>
			<xsd:sequence>
				<xsd:element ref = "first_name"/>
				<xsd:element ref = "last_name"/>
				<xsd:element ref = "orders" minOccurs = "0" maxOccurs = "unbounded"/>
			</xsd:sequence>
			<xsd:attribute name = "id" use = "required" type = "xsd:string"/>
		</xsd:complexType>
	</xsd:element>
	<xsd:element name = "first_name" type = "xsd:string"/>
	<xsd:element name = "last_name" type = "xsd:string"/>
	<xsd:element name = "orders">
		<xsd:complexType>
			<xsd:sequence>
				<xsd:element ref = "order" minOccurs = "0" maxOccurs = "unbounded"/>
			</xsd:sequence>
		</xsd:complexType>
	</xsd:element>
	<xsd:element name = "order">
		<xsd:complexType>
			<xsd:sequence>
				<xsd:element ref = "line_item" minOccurs = "0" maxOccurs = "unbounded"/>
			</xsd:sequence>
			<xsd:attribute name = "id" use = "required" type = "xsd:string"/>
			<xsd:attribute name = "date" use = "required" type = "xsd:string"/>
			<xsd:attribute name = "amount" use = "required" type = "xsd:string"/>
		</xsd:complexType>
	</xsd:element>
	<xsd:element name = "line_item">
		<xsd:complexType>
			<xsd:attribute name = "id" use = "required" type = "xsd:string"/>
			<xsd:attribute name = "product" use = "required" type = "xsd:string"/>
			<xsd:attribute name = "status" use = "required" type = "xsd:string"/>
			<xsd:attribute name = "expected_ship_date" use = "required" type = "xsd:string"/>
		</xsd:complexType>
	</xsd:element>
</xsd:schema>

Ex 4: Step 2. Open Source and Target Schemas

In the Data View Builder, choose File—>New Project to clear your desktop and reset all default values.

On the Builder Design—>Sources tab, click Relational Databases and open one data source:

Double-click on the PB-BB (BroadBand) relational database to open the schema for this data source.

Choose the menu option File—>Set Target Schema.

Navigate to the Liquid Data Samples Server repository or to the location where you saved the customerLineItems.xsd schema. Choose customerLineItems.xsd and click Open.
customerLineItems.xsd appears as the target schema.
This target schema is displayed as a docked schema window on the right side of the design area.

Click the plus (+) sign (or right-mouse click and choose Expand) to expand the nodes in each source schema and in the target schema.

Ex 4: Step 3. Map Source to Target Nodes to Project the Output

Project the output values as follows.

Drag and drop [PB-BB]/db/CUSTOMER/FIRST_NAME from the source schema onto [customerLineItems.xsd]/customers/customer/first_name in the target schema.

Drag and drop [PB-BB]/db/CUSTOMER/LAST_NAME from the source schema onto [customerLineItems.xsd]/customers/customer/last_name in the target schema.

Drag and drop [PB-BB]/db/CUSTOMER/CUSTOMER_ORDER_LINE_ITEM/LINE_ID from the source schema onto [customerLineItems.xsd]/customers/customer/orders/order/line_item/id in the target schema (id is an attribute of line_item).

Drag and drop [PB-BB]/db/CUSTOMER/CUSTOMER_ORDER_LINE_ITEM/PRODUCT_NAME from the source schema onto [customerLineItems.xsd]/customers/customer/orders/order/line_item/product in the target schema (product is an attribute of line_item).

Drag and drop [PB-BB]/db/CUSTOMER/CUSTOMER_ORDER_LINE_ITEM/STATUS from the source schema [customerLineItems.xsd]/customers/customer/orders/order/line_item/status in the target schema (status is an attribute of line_item).

Drag and drop

[PB-BB]/db/CUSTOMER/CUSTOMER_ORDER_LINE_ITEM/EXPECTED_
SHIP_DATE

from the source schema [customerLineItems.xsd]/customers/customer/orders/order/line_item/expected_ship_date in the target schema (expected_ship_date is an attribute of line_item).

At this point, the following mappings should be displayed on the Mappings tab. (Getting the mappings in the same order as shown is not as important as verifying that the relationships between source and target nodes are the same. The @ symbols indicate attributes.)

Source	Target
`[PB-BB]/db/CUSTOMER/FIRST_NAME`	`[customerLineItems.xsd]/customers/customer/ first_name`
`[PB-BB]/db/CUSTOMER/LAST_NAME`	`[customerLineItems.xsd]/customers/customer/ last_name`
`[PB-BB]/db/CUSTOMER/CUSTOMER_ ORDER_LINE_ITEM/LINE_ID`	`[customerLineItems.xsd]/customers/customer/ orders/order/line_item/@id`
`[PB-BB]/db/CUSTOMER/CUSTOMER_ ORDER_LINE_ITEM/PRODUCT_NAME`	`[customerLineItems.xsd]/customers/customer/ orders/order/line_item/@product`
`[PB-BB]/db/CUSTOMER/CUSTOMER_ ORDER_LINE_ITEM/STATUS`	`[customerLineItems.xsd]/customers/customer/ orders/order/line_item/@status`
`[PB-BB]/db/CUSTOMER/CUSTOMER_ ORDER_LINE_ITEM/EXPECTED_SHIP_ DATE`	`[customerLineItems.xsd]/customers/customer/ orders/order/line_item/@expected_ship_date`

Ex 4: Step 4. Create Joins

Join customer with corresponding line-item data. This requires two joins, one to find the customer's Order IDs, and another that uses the Order IDs and finds the corresponding line-item information:

Drag and drop [PB-BB]/db/CUSTOMER/CUSTOMER_ID onto [PB-BB]/db/CUSTOMER_ORDER/CUSTOMER_ID

Drag and drop [PB-BB]/db/CUSTOMER_ORDER/ORDER_ID onto [PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/ORDER_ID

Ex 4: Step 5. Create Two Query Parameters for Customer ID and Date to be Provided at Query Runtime

Create two Query Parameter variables: customer_id and date1, that you can use to insert as variable values when the query runs. Create both variables as type xs:string. Do this as follows:

On the Builder Toolbar, click Toolbox and then click Query Parameter.

From the "Type" drop-down menu, choose xs:string.

In Parameter Name field, enter customer_id and click Add.

The new parameter is displayed in the Query Parameters tree.

Repeat steps 2 and 3 to create the date1 variable.

You should now see both parameters displayed in the Query Parameters tree.

Ex 4: Step 6. Set a Condition Using the Customer ID

On the Builder Toolbar, click Toolbox and then click XQuery Functions.

Drag and drop the equals [eq] function (under Comparison operators) onto the next empty row in the Conditions tab.

The Functions Editor appears, displaying placeholder variables for you to fill in.

On the Builder Toolbar, click on Query Parameter, then drag customer_id onto anyValue1 onto the left side of the equation.

Drag [PB-BB]/db/CUSTOMER/CUSTOMER_ID onto the right side of the equation.

The function should look like this: (customer_id eq [PB-BB]/db/CUSTOMER/CUSTOMER_ID)

Close the Functions Editor.

Ex 4: Step 7. Set a Condition to Determine if Order Ship Date is Earlier or Equal to a Date Submitted at Query Runtime

Click on XQuery Functions, and drag and drop the Operator function [le] (less than or equal) onto the next empty row on the Conditions tab.

The Functions Editor pops up and displays a statement with placeholder variables for you to fill in.

Drag and drop
[PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/EXPECTED_SHIP_DATE onto anyValue1 on the left side of the equation.

Click on XQuery Functions, and drag and drop the date-from-string-with-format function (under Data and Time functions) onto anyValue2 on the right side of the equation.

At this point, the expression in the Functions Editor should appear as:

([PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/EXPECTED_SHIP_DATE le  xfext:date-from-string-with-format(pattern,srcval))

Click Constants, enter the following in the String field:

yyyy-MM-dd

Now drag it (via the Constant icon next to the field) onto pattern (first placeholder parameter to the date function).

Click on Query Parameter, and drag and drop date1 from the Query Parameters panel onto srcval (the second placeholder parameter to the date function).

The completed expression should look like this:

([PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/EXPECTED_SHIP_DATE  le  xfext:date-from-string-with-format("yyyy-MM-dd",date1))

Close the Functions Editor.

The condition you created is displayed on the Conditions tab in the Source column.

Ex 4: Step 8. Set a Condition to Include Only "Open" Orders in the Result

Set the second condition to an Open Order status.

Click on XQuery Functions, and drag and drop the Comparison operator [eq] (equal) function onto the Conditions tab.

The Functions Editor pops up and displays a statement with placeholder variables for you to fill in.

For the left parameter (anyValue1), drag and drop [PB-BB]/db/customer_order_line_item/status on to anyValue1.

For the right parameter (anyValue2), create a constant String with a value of OPEN, and drop it (via the Constant icon next to the field) onto anyValue2.

The completed expression should look like this: ([PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/STATUS eq "OPEN")
Close the Functions Editor.

Ex 4: Step 9. View the XQuery and Run the Query to Test it

Click on the Test tab.

The generated XQuery for this query is shown in the following code listing.

Listing 2-9 XQuery for Example 3: Date and Time Duration


<customers>
	{
	for $PB_BB.CUSTOMER_1 in document("PB-BB")/db/CUSTOMER
	where ($#customer_id of type xs:string  eq  $PB_BB.CUSTOMER_1/CUSTOMER_ID)
	return
	<customer>
		<first_name>{ xf:data($PB_BB.CUSTOMER_1/FIRST_NAME) }</first_name>
		<last_name>{ xf:data($PB_BB.CUSTOMER_1/LAST_NAME) }</last_name>
		<orders>
			<order>
				{
				for $PB_BB.CUSTOMER_ORDER_LINE_ITEM_4 in document("PB-BB")/db/CUSTOMER_ORDER_LINE_ITEM
				where xf:not(xf:empty(
					for $PB_BB.CUSTOMER_ORDER_5 in document("PB-BB")/db/CUSTOMER_ORDER
					where ($PB_BB.CUSTOMER_1/CUSTOMER_ID  eq  $PB_BB.CUSTOMER_ORDER_5/CUSTOMER_ID)
					  and ($PB_BB.CUSTOMER_ORDER_5/ORDER_ID  eq  $PB_BB.CUSTOMER_ORDER_LINE_ITEM_4/ORDER_ID)
					return
					xf:true()))
				  and ($PB_BB.CUSTOMER_ORDER_LINE_ITEM_4/STATUS  eq  "OPEN")
				  and ($PB_BB.CUSTOMER_ORDER_LINE_ITEM_4/EXPECTED_SHIP_DATE  le  xfext:date-from-string-with-format("yyyy-MM-dd",$#date1 of type xs:string))
				return
				<line_item id={$PB_BB.CUSTOMER_ORDER_LINE_ITEM_4/LINE_ID} product={$PB_BB.CUSTOMER_ORDER_LINE_ITEM_4/PRODUCT_NAME} status={$PB_BB.CUSTOMER_ORDER_LINE_ITEM_4/STATUS} expected_ship_date={$PB_BB.CUSTOMER_ORDER_LINE_ITEM_4/EXPECTED_SHIP_DATE} />
				}
			</order>
		</orders>
	</customer>
	}
</customers>

In the Query Parameter panel on the Test tab, set the variable values for customer_id and date1 to submit to the query when the query runs.

For example:

customer_id: CUSTOMER_1 (CUSTOMER_1 through CUSTOMER_10 are available in the data source.)
date1: 2002-08-01 (You can enter any date in the form yyyy-MM-dd.)

Click the Run query button to run the query against the data sources.

Ex 4: Step 9. Verify the Result

Running this query with customer_id set to CUSTOMER_1 and date1 set to 2002-08-01 produces the following XML query result.

Listing 2-10 Result for Example 3: Date and Time Duration

<customers>
  <customer>
    <first_name>JOHN_B_1</first_name>
    <last_name>KAY_1</last_name>
    <orders>
      <order>
        <line_item id="LINE_ID_1" product="RBBC01" status="OPEN" expected_ship_date="2002-03-06"/>
        <line_item id="LINE_ID_3" product="BN16" status="OPEN" expected_ship_date="2002-03-06"/>
        <line_item id="LINE_ID_5" product="CS100" status="OPEN" expected_ship_date="2002-03-06"/>
        <line_item id="LINE_ID_1" product="RBBC01" status="OPEN" expected_ship_date="2002-03-06"/>
        <line_item id="LINE_ID_3" product="BN16" status="OPEN" expected_ship_date="2002-03-06"/>
        <line_item id="LINE_ID_5" product="CS100" status="OPEN" expected_ship_date="2002-03-06"/>
      </order>
    </orders>
  </customer>
</customers>

Example 5: Union

A union query is equivalent to concatenating two or more subordinate queries, and pooling the query results into the same output. There are two important rules for a union query.

Each subordinate query produces a result directed at a repeatable target schema node that is not shared (parent or child) with any other subordinate query target.
You cannot specify any conditions across these subordinate queries.

The Problem

For any BroadBand Customer ID, list any BroadBand and Wireless orders. Assume the Customer IDs match across databases.

The Solution

This query requests a union of BroadBand orders and Wireless orders. Remember that a union retrieves data from multiple sources, such as the BroadBand and Wireless databases, but there are no conditions for the query. If you specify any condition, such as matching order dates, then you are creating a join query. In this example, you need a target schema that contains a repeatable list of Customer IDs, and within that list, a repeatable list of orders. Then you will clone the orders element, using one element for BroadBand orders and the other element for Wireless orders.

To create the solution, follow these steps:

View a Demo

Union Demo... If you are looking at this documentation online, you can click the "Demo" button to see a viewlet demo showing how to build the conditions and create the mappings described in this example. This demo previews the steps described in detail in the following sections. The demo assumes you already have the target schema in the Liquid Data Samples Server repository.

  

Ex 5: Step 1. Verify the Target Schema is Saved in Repository

For this example, we will use a target schema called unionOrders.xsd. This schema is available in the Liquid Data Samples Server repository. The path to the schemas folder is:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/

Just in case you want to verify that you have the right schema file, the following code listing shows the XML for this schema.

Listing 2-11 XML Source for unionOrders.xsd Target Schema File

<?xml version = "1.0" encoding = "UTF-8"?>
<!--Generated by Data View Builder 1.1. Conforms to w3c http://www.w3.org/2001/XMLSchema-->
<xsd:schema xmlns:xsd = "http://www.w3.org/2001/XMLSchema"  >
	<xsd:element name="customers">
		<xsd:complexType>
			<xsd:sequence>
				<xsd:element name="customer" minOccurs="0" maxOccurs="unbounded">
					<xsd:complexType>
						<xsd:sequence>
							<xsd:element name="first_name" type="xsd:string"/>
							<xsd:element name="last_name" type="xsd:string"/>
							<xsd:element name="state" type="xsd:string"/>
							<xsd:element name="orders" minOccurs="0" maxOccurs="unbounded">
								<xsd:complexType>
									<xsd:sequence>
										<xsd:element name="order" minOccurs="0" maxOccurs="unbounded">
											<xsd:complexType>
												<xsd:sequence>
													<xsd:element name="date" type="xsd:string"/>
													<xsd:element name="amount" type="xsd:decimal"/>
												</xsd:sequence>
											</xsd:complexType>
										</xsd:element>
									</xsd:sequence>
								</xsd:complexType>
							</xsd:element>
						</xsd:sequence>
					</xsd:complexType>
				</xsd:element>
			</xsd:sequence>
		</xsd:complexType>
	</xsd:element>
</xsd:schema>

Ex 5: Step 2. Open Source and Target Schemas

In the Data View Builder, choose File —> New Project to clear your desktop and reset all default values.

On the Builder Toolbar Design —> Sources tab, click Relational Databases and open two data sources:

Double-click on the PB-WL (Wireless) relational database to open the schema for this data source.
Double-click on the PB-BB (BroadBand) relational database to open the schema for this data source.

Choose the menu option File —> Set Target Schema.

Navigate to the Liquid Data Samples Server repository. Choose unionOrders.xsd and click Open.

unionOrders.xsd appears as the target schema.

Click the plus (+) sign (or right-mouse click and choose Expand) to expand the nodes in each source schema and in the target schema.

Ex 5: Step 3. Clone the Orders Element of the Target Schema

In the Data View Builder, select the Orders element of the target schema and right-mouse click. The Orders element is a child of the Customers element and has a child called Order.

Choose Clone from the right-mouse menu.

The Cloned complex element labeled Orders(2) appears. The name of the original complex element is changed to Orders(1).

Ex 5: Step 4. Create a Query Parameter for a Customer ID

Create a Query Parameter variable, customer_id, that you can use to insert as a variable for a BroadBand customer ID value when the query runs. To create this parameter, do the following:

On the Builder Toolbar, click Toolbox and then click Query Parameter.

From the Type drop-down menu, choose xs:string.

In Parameter Name field, enter customer_id and click Create.

The new parameter is displayed in the Query Parameters tree.

Ex 5: Step 5. Assign a Query Parameters

Assign the query parameter customer_id to the BroadBand customer ID by dragging and dropping query parameter customer_id to the [PB-BB]/db/CUSTOMER/CUSTOMER_ID element.

Ex 5: Step 6. Define Source Relationships

Within PB-BB, join the BroadBand Customer ID to the Order Customer ID.

Drag and drop [PB-BB]/db/CUSTOMER/CUSTOMER_ID onto BroadBand [PB-BB]/db/CUSTOMER_ORDER/CUSTOMER_ID

Join the BroadBand customer ID from the BroadBand Customer table with the Wireless customer ID from the Wireless Customer Order table as follows:

Drag and drop [PB-BB]/db/CUSTOMER/CUSTOMER_ID onto [PB-WL]/db/CUSTOMER_ORDER/CUSTOMER_ID

Ex 5: Step 7. Project the Output to the Target Schema

Project the BroadBand order information.

Drag and drop [PB-BB]/db/CUSTOMER_ORDER/TOTAL_ORDER_AMOUNT onto [UnionOrders.xsd]/customers/customer/orders(1)/order/amount
Drag and drop [PB-BB]/db/CUSTOMER_ORDER/ORDER_DATE onto [UnionOrders.xsd]/customers/customer/orders(1)/order/date

Project the Wireless [PB-WL] order information.

Drag and drop [PB-WL]/db/CUSTOMER_ORDER/TOTAL_ORDER_AMOUNT onto [UnionOrders.xsd]/customers/customer/orders(2)/order/amount
Drag and drop [PB-WL]/db/CUSTOMER_ORDER/ORDER_DATE onto [UnionOrders.xsd]/customers/customer/orders(2)/order/date

Project the BroadBand user information.

Drag and drop [PB-BB]/db/CUSTOMER/FIRST_NAME onto [unionOrders.xsd]/customers/customer/first_name
Drag and drop [PB-BB]/db/CUSTOMER/LAST_NAME onto [unionOrders.xsd]/customers/customer/last_name
Drag and drop [PB-BB]/db/CUSTOMER/STATE onto [unionOrders.xsd]/customers/customer/state

Ex 5: Step 8. View, then Run the Query

Click on the Test tab.

The generated XQuery for this query is shown in the following code listing.

Listing 2-12 XQuery for Example 4: Union

<customers>
	{
	for $PB_BB.CUSTOMER_1 in document("PB-BB")/db/CUSTOMER
	where ($#customer_id of type xs:string  eq  $PB_BB.CUSTOMER_1/CUSTOMER_ID)
	return
	<customer>
		<orders>
			{
			for $PB_BB.CUSTOMER_ORDER_6 in document("PB-BB")/db/CUSTOMER_ORDER
			where ($PB_BB.CUSTOMER_1/CUSTOMER_ID  eq  $PB_BB.CUSTOMER_ORDER_6/CUSTOMER_ID)
			return
			<order>
				<date>{ xf:data($PB_BB.CUSTOMER_ORDER_6/ORDER_DATE) }</date>
				<amount>{ xf:data($PB_BB.CUSTOMER_ORDER_6/TOTAL_ORDER_AMOUNT) }</amount>
			</order>
			}
		</orders>
		<orders>
			{
			for $PB_WL.CUSTOMER_ORDER_9 in document("PB-WL")/db/CUSTOMER_ORDER
			where ($PB_BB.CUSTOMER_1/CUSTOMER_ID  eq  $PB_WL.CUSTOMER_ORDER_9/CUSTOMER_ID)
			return
			<order>
				<date>{ xf:data($PB_WL.CUSTOMER_ORDER_9/ORDER_DATE) }</date>
				<amount>{ xf:data($PB_WL.CUSTOMER_ORDER_9/TOTAL_ORDER_AMOUNT) }</amount>
			</order>
			}
		</orders>
		<custID>{ xf:data($PB_BB.CUSTOMER_1/CUSTOMER_ID) }</custID>
	</customer>
	}

</customers>

In the Query Parameter panel, click into the cell under "Value" and enter a value for customer_id. (CUSTOMER_1 through CUSTOMER_10 are available to try.)

Click the Run query arrow to run the query against the data sources.

Ex 5: Step 9. Verify the Result

Querying these data sources as described in this example produces an XML query result similar to that shown in the following code listing where CUSTOMER_4 was used as the query parameter value for customer_id.

Listing 2-13 Result for Example 4: Union

<customers>
  <customer>
    <first_name>JOHN_B_4</first_name>
    <last_name>KAY_4</last_name>
    <state>NV</state>
    <orders>
      <order>
        <date>2002-03-06-08:00</date>
        <amount>1000</amount>
      </order>
      <order>
        <date>2002-03-06-08:00</date>
        <amount>1500</amount>
      </order>
      <order>
        <date>2002-03-06-08:00</date>
        <amount>2000</amount>
      </order>
      <order>
        <date>2002-03-06-08:00</date>
        <amount>2500</amount>
      </order>
      <order>
        <date>2002-03-06-08:00</date>
        <amount>3000</amount>
      </order>
    </orders>
    <orders>
      <order>
        <date>2002-03-06-08:00</date>
        <amount>1000</amount>
      </order>
      <order>
        <date>2002-03-06-08:00</date>
        <amount>2000</amount>
      </order>
      <order>
        <date>2002-03-06-08:00</date>
        <amount>4000</amount>
      </order>
      <order>
        <date>2002-03-06-08:00</date>
        <amount>5000</amount>
      </order>
      <order>
        <date>2002-03-06-08:00</date>
        <amount>10000</amount>
      </order>
    </orders>
  </customer>
</customers>

Example 6: Minus

A minus relationship (A minus B) returns all instances of some named value that are in A but not in B. There is no explicit minus operation in the XQuery language or Data View Builder; however, a simple compare and count technique can be used. For example: for each instance of the named value in A, count all matching instances in B; if the count is zero, that means there are no matches, and the query therefore returns the instance from A.

The Problem

Find all customers that are BroadBand customers, but not Wireless customers. Assume that Customer IDs match across databases.

The shaded area in Figure 2-8 represents the BroadBand customers who are not Wireless customers.

Figure 2-8 BroadBand and Wireless Customers

BroadBand and Wireless Customers

The Solution

If a customer has only a BroadBand account, then a join across the BroadBand and Wireless databases on that Customer ID produces an empty result. We can take advantage of that fact by counting the number of instances produced by the join. If the number is zero, then the Customer ID represents a BroadBand-only customer.

To create the solution, follow these steps:

View a Demo

Minus Demo... If you are looking at this documentation online, you can click the "Demo" button to see a viewlet demo showing how to build the conditions and create the mappings described in this example. This demo previews the steps described in detail in the following sections. The demo assumes you already have the target schema in the Liquid Data Samples Server repository.

  

Ex 6: Step 1. Verify the Target Schema is Saved in Repository

For this example, we will use a target schema called minus.xsd. This schema is available in the Liquid Data Samples Server repository. The path to the schemas folder is:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/

Just in case you want to verify that you have the right schema file, the following code listing shows the XML for this schema.

Listing 2-14 XML Source for minus.xsd Target Schema File

<?xml version = "1.0" encoding = "UTF-8"?>

<xsd:schema xmlns:xsd = "http://www.w3.org/2001/XMLSchema">
	<xsd:element name="results">
		<xsd:complexType>
			<xsd:sequence>
				<xsd:element name="CUSTOMER" minOccurs="1" maxOccurs="unbounded">
					<xsd:complexType>
						<xsd:sequence>
							<xsd:element name="FIRST_NAME" type="xsd:string"/>
							<xsd:element name="LAST_NAME" type="xsd:string"/>
						</xsd:sequence>
					</xsd:complexType>
				</xsd:element>
			</xsd:sequence>
		</xsd:complexType>
	</xsd:element>
</xsd:schema>

Ex 6: Step 2. Open Source and Target Schemas

In the Data View Builder, choose File—>New Project to clear your desktop and reset all default values.

On the Builder Toolbar Design—>Sources tab, click Relational Databases and open two data sources:

Double-click on the PB-WL (Wireless) relational database to open the schema for this data source.
Double-click on the PB-BB (BroadBand) relational database to open the schema for this data source.

Choose the menu option File —> Set Target Schema.

Navigate to the Liquid Data Samples Server repository or to the location where you saved the minus.xsd schema. Choose minus.xsd and click Open.
minus.xsd appears as the target schema.
This target schema is displayed as a docked schema window on the right side of the design area.

Click the plus (+) sign (or right-mouse click and choose Expand) to expand the nodes in each source schema and in the target schema.

Ex 6: Step 3. Find BroadBand and Wireless Customers with the Same Customer ID

Drag and drop [PB-BB]/db/CUSTOMER/CUSTOMER_ID onto [PB-WL]/db/CUSTOMER/CUSTOMER_ID to join the BroadBand CUSTOMER_ID and the Wireless CUSTOMER_ID.

Ex 6: Step 4. Find the Count of the Wireless Customers

On the Builder Toolbar Design —> Toolbox tab, click XQuery Functions and double-click on the xf:count function (under Aggregate functions) to open it.

Drag and drop the [PB-WL]/db/CUSTOMER/CUSTOMER_ID onto the input of the xf:count function.

Ex 6: Step 5. Set a Condition that Specifies the Output of "count" is Zero

Click on the Conditions tab.

Drag and drop the eq (equal) function (in the XQuery functions Comparison operators folder) onto the next empty row under Conditions on the Conditions tab.

The Functions Editor is displayed.

For the first parameter, drop xf:count/result onto anyValue1.

For the second parameter, create a Number constant, set it to 0 and drop it on anyValue2.

Note: To create the Number constant, on Builder —> Toolbox tab, click Constants, enter 0 in the Number field, and drag the Constant icon next to that field onto anyValue2 in the equation in the Functions Editor.

The equality condition should look like this: ([xf:count]/result eq 0)
Close the Functions Editor.

Project the BroadBand customers to the target results.

Drag and drop [PB-BB]/db/CUSTOMER/FIRST_NAME onto [minus.xsd]/results/CUSTOMER/FIRST_NAME
Drag and drop [PB-BB]/db/CUSTOMER/LAST_NAME onto [minus.xsd]/results/CUSTOMER/LAST_NAME

Ex 6: Step 6. View the XQuery and Run the Query to Test it

Click on the Test tab.

The generated XQuery for this query is shown in the following code listing.

Listing 2-15 XQuery for Example 5: Minus

<results>
	{
	for $PB_BB.CUSTOMER_1 in document("PB-BB")/db/CUSTOMER
	let $srcval_2 := 
	               for $PB_WL.CUSTOMER_3 in document("PB-WL")/db/CUSTOMER
	               where ($PB_BB.CUSTOMER_1/CUSTOMER_ID  eq  $PB_WL.CUSTOMER_3/CUSTOMER_ID)
	               return
	               xf:data($PB_WL.CUSTOMER_3/CUSTOMER_ID)
	let $xf:count_4 := xf:count($srcval_2)
	where ($xf:count_4  eq  0)
	return
	<CUSTOMER>
		<FIRST_NAME>{ xf:data($PB_BB.CUSTOMER_1/FIRST_NAME) }</FIRST_NAME>
		<LAST_NAME>{ xf:data($PB_BB.CUSTOMER_1/LAST_NAME) }</LAST_NAME>
	</CUSTOMER>
	}
</results>

Click the "Run query" button to run the query against the data sources.

Ex 6: Step 7. Verify the Result

When you run this query on the sample data sources as described here, the result will be one record because the sample BroadBand data source has one customer record that is different from the Wireless customer records.

Listing 2-16 Result for Example 5: Minus

<results>
  <CUSTOMER>
    <FIRST_NAME>JOHN</FIRST_NAME>
    <LAST_NAME>PARKER</LAST_NAME>
  </CUSTOMER>
</results>

Example 7: Complex Parameter Type (CPT)

The Complex Parameter Type Cookbook example shows how to use Liquid Data to create an integrated view that connects two enterprise information systems: a database and an in-flight XML data source using a complex parameter type (CPT). A query that uses both data sources determines whether the customer has sufficient credit for the incoming order to be processed.

The Problem

The company receives dozens of electronically transmitted orders daily and needs to quickly respond to its field office if an order cannot be accepted because a customer has exceeded their credit limit. The credit limit and amount of outstanding orders is known to the system. The quantity and price of the items being ordered is supplied in real-time along with the order.

The Solution

The company develops a complex parameter type (CPT) that models the incoming purchase order as an XML schema and sets a simple orderLimit parameter that an operator can change whenever the query is run. The query also calculates the total amount outstanding of current orders and the total amount of the incoming order. The objective is to accept orders if the total amount of both outstanding and incoming orders is within the order limit. Otherwise, the order is rejected.

To recreate the solution, follow these steps:

Note: The implementation details of the Complex Parameter Type demo, the DB-COCPT sample, and the CPT cookbook example vary slightly.

View a Demo

Complex Parameter Type (CPT) Demo. If you are looking at this documentation online, you can click the "Demo" button to see a viewlet demo showing how to build the conditions and create the mappings described in this example.

  

Ex 7: Step 1. Verify the Availability of Schemas and Sample Data Stream

In creating the DB-CPTCO sample query, we use the following files that are installed with Liquid Data samples.

From the Liquid Data Samples Server repository schema directory:

BroadBand database schema. The path to this file is:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/broadbandp.sql

Complex parameter type schema. The path to this file is:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/coCptSample2.xsd

Target schema. The path to this file is:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/COCPTSampleTarget-Schema.xsd

CPT sample XML stream. The path to this file is:

<WL_HOME>/samples/domains/liquiddata/ldrepository/xml_files/coCPTsample2.xml

If you want to refer to the sample DB-CPTCO project, it is installed as the following file:

<WL_HOME>/<LD_HOME>/liquiddata/buildquery/db-cptco/coCPTSample.qpr

For reference purposes, code listings for several of the XML files used in this example appear below:

Listing 2-17 DB-CPTCO Sample CPT Schema (coCptSample2.xsd)

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema targetNamespace="urn:schemas-bea-com:ld-cocpt"
								xmlns:cocpt="urn:schemas-bea-com:ld-cocpt"
								xmlns:xsd="http://www.w3.org/2001/XMLSchema">
	<xsd:element name="CustOrder">
		<xsd:complexType>
			<xsd:sequence>
				<xsd:element name="CUSTOMER_ORDER" minOccurs="0" maxOccurs="unbounded">
					<xsd:complexType>
						<xsd:sequence>
							<xsd:element name="CUSTOMER_ID" type="xsd:string"/>
							<xsd:element name=
											"NEW_ORDER_LINE_ITEM"type="cocpt:NEW_ORDER_LINE_ITEMType" 
											minOccurs="0" maxOccurs="unbounded"/>
						</xsd:sequence>
					</xsd:complexType>
				</xsd:element>
			</xsd:sequence>
		</xsd:complexType>
	</xsd:element>
	<xsd:complexType name="NEW_ORDER_LINE_ITEMType">
		<xsd:sequence>
			<xsd:element name="PRODUCT_NAME" type="xsd:string"/>
			<xsd:element name="QUANTITY" type="xsd:decimal"/>
			<xsd:element name="PRICE" type="xsd:decimal"/>
		</xsd:sequence>
	</xsd:complexType>
</xsd:schema>

Listing 2-18 DB-CPTCO Target Schema (COCPTSampleTargetSchema.xsd)

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema targetNamespace="urn:schemas-bea-com:ld-cocpt" xmlns:cocpt="urn:schemas-bea-com:ld-cocpt" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
	<xsd:element name="CustOrder">
		<xsd:complexType>
			<xsd:sequence>
				<xsd:element name="CUSTOMER_ORDER" minOccurs="0" maxOccurs="unbounded">
					<xsd:complexType>
						<xsd:sequence>
							<xsd:element name="CUSTOMER_ID" type="xsd:string"/>
							<xsd:element name="NEW_ORDER_LINE_ITEM" type="cocpt:NEW_ORDER_LINE_ITEMType" minOccurs="0" maxOccurs="unbounded"/>
						</xsd:sequence>
					</xsd:complexType>
				</xsd:element>
			</xsd:sequence>
		</xsd:complexType>
	</xsd:element>
	<xsd:complexType name="NEW_ORDER_LINE_ITEMType">
		<xsd:sequence>
			<xsd:element name="PRODUCT_NAME" type="xsd:string"/>
			<xsd:element name="QUANTITY" type="xsd:decimal"/>
			<xsd:element name="PRICE" type="xsd:decimal"/>
		</xsd:sequence>
	</xsd:complexType>
</xsd:schema>

Listing 2-19 DB-CPTCO Sample XML Data Stream (coCptSample2.xml)

<?xml version="1.0" encoding="UTF-8"?>
<cocpt:CustOrder xmlns:cocpt="urn:schemas-bea-com:ld-cocpt"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="urn:schemas-bea-com:ld-cocpt
		coCptSample2.xsd">	
	<CUSTOMER_ORDER>
		<CUSTOMER_ID>CUSTOMER_1</CUSTOMER_ID>
		<NEW_ORDER_LINE_ITEM>
			<PRODUCT_NAME>RBBC01</PRODUCT_NAME>
			<QUANTITY>1000</QUANTITY>
			<PRICE>20</PRICE>
		</NEW_ORDER_LINE_ITEM>
		<NEW_ORDER_LINE_ITEM>
			<PRODUCT_NAME>CS2610</PRODUCT_NAME>
			<QUANTITY>1000</QUANTITY>
			<PRICE>20</PRICE>
		</NEW_ORDER_LINE_ITEM>
	</CUSTOMER_ORDER>
</cocpt:CustOrder>

You may also want to examine the CO-CPTSAMPLE definition in the WLS Administration Console for the Samples server you are using.

In the left pane, click the Liquid Data node.

In the right pane, click the Complex Parameter Types tab and click on CO-CPTSAMPLE.

See the section Creating a Complex Parameter Type for details.

Ex 7: Step 2. Open the Target Schema and CO-CPTSAMPLE CPT

In the Data View Builder, choose File—>New Project.

On the Design tab, on the Builder Toolbar, click the Toolbox tab, then click Complex Parameter Type. The CO-CPTSAMPLE complex parameter type is listed.

Double-click on CO-CPTSAMPLE to open the CPT schema.

Choose File -> Set Target Schema. Browse to the Liquid Data Samples Server repository schema directory.

Select the following file as the target schema:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/COCPTSampleTargetSchema.xsd

In the target schema window on the right side of the design area, right-click on the top element expand the target schema.

Ex: 7: Step 3. Create an orderLimit Query Parameter

Since credit limits vary from customer to customer, it is convenient to have an order limit query parameter that can be changed whenever a query is run.

In the Data View Builder select Design—>Toolbox.

Click on the Query Parameter tab.

Enter orderLimit as a parameter name.

Select xs:decimal as the parameter type.

Click Create.

Drag and drop the orderLimit parameter icon onto the target schema [COCPTSampleTargetSchema.xsd]/cocpt:CustOrderStatus/CUSTOMER/CUSTOMER_ORDER/TOTAL_OPEN_ORDERLIMIT

Ex 7: Step 4. Save the Project

You can save a project at any time. To initially create a project, use File—>Save Project As. Use the file browser to choose a location and project name (we use myCoCPT).

Ex 7: Step 5. Test Access to the Complex Parameter Source

Follow these steps to verify access to the CPT data source:

Drag and drop output from:

[CO-CPTSAMPLE]/cocpt:CustOrder/CUSTOMER_ORDER/CUSTOMER_ID

onto the target schema: [COCPTSampleTargetSchema.xsd]/cocpt:CustOrderStatus/cocpt:CUSTOMER/cocpt:CUSTOMER_ID.

Click the Test tab.

In the lower-left pane of the Data View Builder (Test mode), click in the Values area under Query Parameters to the right of the CPT name (CO-CPTSAMPLE).

Navigate to the XML data file associated with the CO-CPTSAMPLE complex parameter type.

<WL_HOME>/samples/domains/liquiddata/ldrepository/xml_files/coCptSample2.xml

Enter an orderLimit value such as 200000.

Now you can click the Run arrow to execute a preliminary query. The following result shows that your CPT is successfully retrieving from the XML file data:

Listing 2-20 Interim Results (1) from the CPT Example Query

<prefix1:CustOrderStatus xmlns:prefix1="urn:schemas-bea-com:ld-cocpt">
  <prefix1:CUSTOMER>
    <prefix1:CUSTOMER_ID>CUSTOMER_1</prefix1:CUSTOMER_ID>
    <prefix1:CUSTOMER_ORDER>
      <prefix1:TOTAL_OPEN_ORDERLIMIT>200000</prefix1:TOTAL_OPEN_ORDERLIMIT>
    </prefix1:CUSTOMER_ORDER>
  </prefix1:CUSTOMER>
</prefix1:CustOrderStatus>

Return to the Toolbar Design mode.

In the case of this data source, the customer identification is provided so there is no need to create a customer ID query parameter.

Ex 7: Step 6: Determine the Total Amount of New Orders

Since all runtime source items from a CPT are treated as character strings, any data items from the CO-CPTSAMPLE data source must first be cast appropriately. Then quantities and prices are multiplied together. The sum of the products of quantity and prices is the total amount of new orders.

Click XQuery Functions.

Drag and drop a multiply [*] function into the design area (from the Numeric operators group).

Drag and drop a sum Aggregate function (xf:sum) into the design area.

Figure 2-9 XQuery Functions Used to Calculate Total New Orders in Data Stream BroadBand and Wireless Customers

Drag and drop the output result of xs:decimal to one side of the multiply equation and the output result of xs:decimal2 to the other.

Drag and drop [CO-CPTSAMPLE]cocpt:CustOrder/CUSTOMER_ORDER/NEW_ORDER_LINE_ITEM/QUANTITY to one side of the multiply equation.

Drag and drop [CO-CPTSAMPLE]cocpt:CustOrder/CUSTOMER_ORDER/NEW_ORDER_LINE_ITEM/PRICE to the other side of the multiply equation.

Drag the [*] function output result to the input parameter of xf:sum. This gives you the total order amount in the CPT data source.

Drag and drop output from xf:sum onto the target schema [COCPTSampleTargetSchema.xsd]/cocpt:CustOrderStatus/CUSTOMER/CUSTOMER_ORDER/NEW_ORDER_TOTAL_AMOUNT

Rerun your query. A new order total of 40000 appears.

Return to Design mode.

Ex 7: Step 7. Create the Necessary Joins and Mappings to the Target Schema

Move the PB-BB relational source schema onto the design area.

On the Builder Toolbar Design—>Sources tab, click Relational Databases, and double-click on PB-BB to open the schema for the BroadBand sample data source.

Expand the PB-BB schema.

Create the necessary joins to allow us to fetch the line items for a particular order for a particular customer.

Drag and drop [CO-CPTSAMPLE]/cocpt:CustOrder/CUSTOMER_ORDER/CUSTOMER_ID onto [PB-BB]/db/CUSTOMER/CUSTOMER_ID

Drag and drop [PB-BB]/db/CUSTOMER/CUSTOMER_ID onto the [PB-BB]/db/CUSTOMER_ORDER/CUSTOMER_ID

Drag and drop [PB-BB]/db/CUSTOMER_ORDER/ORDER_ID onto [PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/ORDER_ID

Figure 2-10 Setting Joins Between CPT and Relational Data Source

Setting Joins Between CPT and Relational Data Source

Next, project FIRST_NAME and LAST_NAME elements onto the target schema.

Drag and drop [PB-BB]/db/CUSTOMER/FIRST_NAME onto the target schema [cocptsampletarget-schema]/cocptCustOrderStatus/CUSTOMER/FIRST_NAME

Drag and drop [PB-BB]/db/CUSTOMER/LAST_NAME onto the target schema [cocptsampletarget-schema]/cocptCustOrderStatus/CUSTOMER/LAST_NAME

Note: In this version of the CO-CPTSAMPLE, automatic scoping is used. See Understanding Condition Scoping in Building Queries and Data Views for more information.

Although your query is not complete, you can test run it again.

Listing 2-21 Interim Results (2) from CPT Example Query

<prefix1:CustOrderStatus xmlns:prefix1="urn:schemas-bea-com:ld-cocpt">
  <prefix1:CUSTOMER>
    <prefix1:FIRST_NAME>JOHN_B_1</prefix1:FIRST_NAME>
    <prefix1:LAST_NAME>KAY_1</prefix1:LAST_NAME>
    <prefix1:CUSTOMER_ID>CUSTOMER_1</prefix1:CUSTOMER_ID>
    <prefix1:CUSTOMER_ORDER>
      <prefix1:NEWORDER_TOTAL_AMOUNT>40000</prefix1:NEWORDER_TOTAL_AMOUNT>
      <prefix1:TOTAL_OPEN_ORDERLIMIT>200000</prefix1:TOTAL_OPEN_ORDERLIMIT>
    </prefix1:CUSTOMER_ORDER>
  </prefix1:CUSTOMER>
</prefix1:CustOrderStatus>

Return to Design mode.

Ex 7: Step 8. Determine the Amount of Currently Open Orders

Follow these steps to find the total amount of open orders in the sample PB-BB database:

Click the XQuery Functions.

Drag and drop the multiply [*] operator into the design area.

Drag and drop [PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/QUANTITY and [PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/PRICE to the function operands.

Drag and drop another xf:sum into the design area. Drag the output result of the f()*2 - Function (*2 being the second use of a multiplication function) to the input parameter of xf:sum2.

Drag and drop output from xf:sum2 onto the target schema [COCPTSampleTargetSchema.xsd]/cocpt:CustOrderStatus/CUSTOMER/CUSTOMER_ORDER/OPEN_ORDER_TOTAL_AMOUNT

Finally, we need to restrict results to open orders:

In the Data View Builder select Design —> Toolbox.

Click on Constants.

Create a string constant called OPEN and drag the icon to the right of OPEN to the [PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/STATUS item.

If we run the query, the amount of open orders should be 150000.

Ex 7: Step 9: Determine the Total Amount of All Open and New Orders

Click XQuery Functions.

Drag and drop the plus [+] numeric operator into the design area.

Use xf:sum (total new orders) and xf:sum2 (total open orders) as the operands to obtain the total of open and new order amount.

Ex 7: Step 10: Test If Open Orders + New Orders Exceeds the Order Limit

Select XQuery Functions.

Under Comparison operators locate the [gt] (greater than) function. Drag it into the design area.

Use the output of the sum of open and newly arrived orders as the first input parameter.

Use the query parameter orderLimit as input for operand2. The result is a Boolean value that is True if the sum of open and newly arrived orders is greater than the overall order limit.

Ex 7: Step 11: Determine If the Order is Accepted or Rejected

Now that the relationships and conditions are established, set up an if-then-else test to solve the business problem. See The Problem.

Under Other functions locate the xfext:if-then-else function. Drag it into the design area.

Drag the output of the gt function to the if-then-else condition parameter.

Click on the Toolbox Constants tab. In the String field enter REJECT, drag the String field icon to the then parameter in the xfext:if-then-else function.

Change the String field to ACCEPT, then drag the String field icon to the else parameter.

Drag the output result onto the target schema [COCPTSampleTargetSchema.xsd]/cocpt:CustOrderStatus/CUSTOMER/CUSTOMER_ORDER/ORDER_REVIEW_STATUS

Click Test.

Ex 7: Step 12: View the XQuery

The generated XQuery is shown in the following code listing.

Listing 2-22 XQuery for Example 6: Complex Parameter Type (CPT)

namespace cocpt = "urn:schemas-bea-com:ld-cocpt"

<cocpt:CustOrderStatus>
	{
	for $COCPTSAMPLE.CUSTOMER_ORDER_2 in ($#COCPTSAMPLE of type element cocpt:CustOrder)/CUSTOMER_ORDER
	for $PB_BB.CUSTOMER_3 in document("PB-BB")/db/CUSTOMER
	let $srcval_4 := 
	               for $PB_BB.CUSTOMER_ORDER_LINE_ITEM_5 in document("PB-BB")/db/CUSTOMER_ORDER_LINE_ITEM
	               where xf:not(xf:empty(
	               	for $PB_BB.CUSTOMER_ORDER_7 in document("PB-BB")/db/CUSTOMER_ORDER
	               	where ($PB_BB.CUSTOMER_3/CUSTOMER_ID  eq  $PB_BB.CUSTOMER_ORDER_7/CUSTOMER_ID)
	               	  and ($PB_BB.CUSTOMER_ORDER_7/ORDER_ID  eq  $PB_BB.CUSTOMER_ORDER_LINE_ITEM_5/ORDER_ID)
	               	return
	               	xf:true()))
	                 and ("OPEN"  eq  $PB_BB.CUSTOMER_ORDER_LINE_ITEM_5/STATUS)
	               return
	               $PB_BB.CUSTOMER_ORDER_LINE_ITEM_5/QUANTITY * $PB_BB.CUSTOMER_ORDER_LINE_ITEM_5/PRICE
	let $xf:sum2_8 := xf:sum($srcval_4)
	let $srcval_9 := 
	               for $COCPTSAMPLE.NEW_ORDER_LINE_ITEM_10 in $COCPTSAMPLE.CUSTOMER_ORDER_2/NEW_ORDER_LINE_ITEM
	               return
	               $COCPTSAMPLE.NEW_ORDER_LINE_ITEM_10/QUANTITY * $COCPTSAMPLE.NEW_ORDER_LINE_ITEM_10/PRICE
	let $xf:sum_12 := xf:sum($srcval_9)
	let $v_13 := $xf:sum2_8 + $xf:sum_12
	let $gt_14 := $v_13 gt $#orderLimit of type xs:decimal
	where ($COCPTSAMPLE.CUSTOMER_ORDER_2/CUSTOMER_ID  eq  $PB_BB.CUSTOMER_3/CUSTOMER_ID)
	return
	<cocpt:CUSTOMER>
		<cocpt:FIRST_NAME>{ xf:data($PB_BB.CUSTOMER_3/FIRST_NAME) }</cocpt:FIRST_NAME>
		<cocpt:LAST_NAME>{ xf:data($PB_BB.CUSTOMER_3/LAST_NAME) }</cocpt:LAST_NAME>
		<cocpt:CUSTOMER_ID>{ xf:data($COCPTSAMPLE.CUSTOMER_ORDER_2/CUSTOMER_ID) }</cocpt:CUSTOMER_ID>
		<cocpt:CUSTOMER_ORDER>
			<cocpt:OPENORDER_TOTAL_AMOUNT>{ $xf:sum2_8 }</cocpt:OPENORDER_TOTAL_AMOUNT>
			<cocpt:NEWORDER_TOTAL_AMOUNT>{ $xf:sum_12 }</cocpt:NEWORDER_TOTAL_AMOUNT>
			<cocpt:TOTAL_OPEN_ORDERLIMIT>{ $#orderLimit of type xs:decimal }</cocpt:TOTAL_OPEN_ORDERLIMIT>
			<cocpt:ORDER_REVIEW_STATUS>{ xfext:if-then-else( treat as xs:boolean($gt_14), "REJECT", "ACCEPT") }</cocpt:ORDER_REVIEW_STATUS>
		</cocpt:CUSTOMER_ORDER>
	</cocpt:CUSTOMER>
	}
</cocpt:CustOrderStatus>

Return to Design mode.

Ex 7: Step 13. Run the XQuery to Verify the Result

When you run this query on the sample data sources as described in this example, the result is an accepted order.

Listing 2-23 Result of Example 6: Complex Parameter Type (CPT)

<prefix1:CustOrderStatus xmlns:prefix1="urn:schemas-bea-com:ld-cocpt">
  <prefix1:CUSTOMER>
    <prefix1:FIRST_NAME>JOHN_B_1</prefix1:FIRST_NAME>
    <prefix1:LAST_NAME>KAY_1</prefix1:LAST_NAME>
    <prefix1:CUSTOMER_ID>CUSTOMER_1</prefix1:CUSTOMER_ID>
    <prefix1:CUSTOMER_ORDER>
      <prefix1:OPENORDER_TOTAL_AMOUNT>150000</prefix1:OPENORDER_TOTAL_AMOUNT>
      <prefix1:NEWORDER_TOTAL_AMOUNT>40000</prefix1:NEWORDER_TOTAL_AMOUNT>
      <prefix1:TOTAL_OPEN_ORDERLIMIT>200000</prefix1:TOTAL_OPEN_ORDERLIMIT>
      <prefix1:ORDER_REVIEW_STATUS>ACCEPT</prefix1:ORDER_REVIEW_STATUS>
    </prefix1:CUSTOMER_ORDER>
  </prefix1:CUSTOMER>
</prefix1:CustOrderStatus>