bea.com | products | dev2dev | support | askBEA

Download Docs | Site Map | Glossary
Search

Building Queries and Data Views

Designing Queries

This section explains how to design and build a BEA Liquid Data for WebLogicTM query using the Data View Builder, and provides example walk-throughs of how to build some simple queries. The following topics are included:

- Designing a Query

- Building a Query

- Opening the Source Schemas for the Data Sources You Want to Query

- Adding a Target Schema

- Mapping Source and Target Schemas

- Setting Conditions

- Showing or Hiding Data Types

- Using Automatic Type Casting

- Examples of Simple Queries

- Example: Return Customers by Name

- Example: Query Customers by ID and Sort Output by State

- Understanding Scope in Basic and Advanced Views

- Where Does Scope Apply?

- Basic View (Automatic Scope Settings)

- Advanced View (Setting the Scope Manually)

- When to Use Advanced View to Set Scope Manually

- Task Flow Model for Advanced View Manual Scoping

- Returning to Basic View

- Saving Projects from Basic or Advanced View

- Scope Recursion Errors

- Understanding Query Design Patterns

- Target Schema Design Guidelines and Query Examples

- Source Replication

- Next Steps

Designing a Query

The first step in constructing a query (or, more often, a set of queries) is a design step—drawing on the requirements identified to answer the following questions critical to the query design:

- What types of data sources do I need to query?

- What is the structure of each data source; that is, what do the XML source schemas look like?

- What do I want the query result (that is, the output of the query) to look like? In other words, how do I want to structure the output?

- What should the target XML schema look like? (The target schema defines the structure of the query result.)

- What target schema design pattern should I use?

Note: Proper design of the target schema is a key factor in building a successful query. In a nutshell, you need to ensure that cardinality is correct and check for target conformity. For complete guidelines and examples of recommended design patterns, see Target Schema Design Guidelines and Query Examples.

- What source conditions do I need to define to get the information I need from the data sources? (Source conditions are joins, unions, aggregations and so on defined to filter the source data in a certain way.)

Once you have designed or "modeled" the query in this way based on what you want the query to do and defined an outline strategy for accomplishing the information filtering, you are ready to build a test version of the query. For other than very simple queries, you will probably revise, refine and test the query several times adding optimization if necessary.

Building a Query

Building a query involves specifying one or more source schemas that describe resource data, selecting a single target schema that describes the shape of the query result, creating source-to-target mappings to further define what the query result will look like, and defining source conditions or filters on the data sources. The query extracts the results based on the conditions and mappings that you define in the query. The results can change dramatically depending on how you do the following:

- Specify conditions (filters on the source data)

- Map or project source data from one or more sources to the target schema.

If you have taken the time to outline a design for the query first, considering all the factors mentioned in the previous section (Designing Queries), constructing it will be a matter of following your design as a blueprint for drag-and-drop query building. Then you can test, fine-tune, and modify as needed to produce variations on the results, or to optimize the query for better performance.

The following sections take you through the basic tasks involved in building a query:

- Opening the Source Schemas for the Data Sources You Want to Query

- Adding a Target Schema

- Mapping Source and Target Schemas

- Setting Conditions

Opening the Source Schemas for the Data Sources You Want to Query

A source schema is the XML schema representation for the structure of the data in a data source. You can use multiple data source schemas per query.

The Sources tab on the Builder Toolbar contains the data sources configured on the Liquid Data Server to which you are connected. Note that a data source type only shows up as a button on the Builder Toolbar if it has been configured in the Server to which you are connecting.

Note: Only data sources that have been configured for access by Liquid Data are available from the Builder Toolbar. For information on how to configure Liquid Data data sources, see the Liquid Data Administration Guide.

For this example, open the schemas for the following two data sources which are already configured on the Liquid Data Samples server:

- PB-WL relational database

- XM-BB-C XML file

To do this, follow these steps:

1. Click the Design tab.

2. On the Builder Toolbar, click the Sources tab (on the bottom of the left vertical panel).

3. Open the data sources from the Builder toolbar as follows:

   - Click the Relational Databases and double-click on PB-WL data source to open the associated XML schema showing "Wireless" customers.

   - Click the XML Files button in the navigation panel and double-click on XM-BB-C data source to open the associated XML schema showing "BroadBand" customers.

   The XML schemas for the each of the data sources are displayed.

   Position the schema windows so you can view the data nodes in each schema. You can expand the data nodes by clicking the plus (+) sign. For example, in the PB-WL data source, CUSTOMER is a parent node with subordinate child nodes. The child nodes are the ones you will use as function parameters and map to the target schema.

Adding a Target Schema

A target schema is the XML schema representation for the structure of the target data (query result). Only one target schema per project is allowed. If you have a target schema open and decide to choose another, the current target schema is closed and the new one replaces it.

You can use a target schema file that you have saved on your local system or on the network, or one that has been saved to the Liquid Data server repository.

Note: Only target schemas that are saved to the Liquid Data server repository will be available to other Liquid Data users for distributed, team-style development.

For this example, we will use a target schema called amtByState. If this schema is not available in the Samples server repository and you would like to follow along with our example, you can create it yourself and save it locally or to the server repository as a .xsd file.

To create and set the target schema do the following:

1. Use a text editor to copy the following XML into a plain text file and save it to the server Repository as amtByState.xsd.

   The path to the schemas folder in the Liquid Data server repository is:

>     <WL_HOME>liquiddata/samples/config/ld_samples/repository/schemas/

Note: It is not necessary to save the target schema to the server Repository in order to use it in your local project—you can save it anywhere on your system. However, we recommend saving schemas to the Repository because it makes projects more "portable" and schema files accessible to all users who log onto this server.

Listing 3-1 XML Source for amtByState.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="STATE" minOccurs="0" maxOccurs="unbounded">
>          <xsd:complexType>
>           <xsd:sequence>
>            <xsd:element name="state" type="xsd:string" minOccurs="0" maxOccurs="1"/>
>           </xsd:sequence>
>          </xsd:complexType>
>         </xsd:element>
>         <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="AVERAGE_ORDER" type="xsd:string"/>
>            <xsd:element name="CUSTOMER_ID" type="xsd:string"/>
>            <xsd:element name="STATE" type="xsd:string"/>
>            </xsd:sequence>
>           </xsd:complexType>
>          </xsd:element>
>         </xsd:sequence>
>        </xsd:complexType>
>       </xsd:element>
>     </xsd:schema>

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

2. Choose the menu option File—>Set Target Schema.

   Navigate to the server Repository or to the location where you saved the amtByState.xsd schema. Choose amtByState.xsd and click Open.

   amtByState.xsd appears as the target schema.

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

Note: Remember that only one target schema per project is allowed. The target schema docks on the right side of the desktop area. The target schema may have more data nodes than you need for your result, but it must contain the data nodes required for the query result. Unreferenced nodes are disregarded in the result.

Editing a Target Schema

You can make simple changes to a target schema by right-clicking a node. A shortcut menu shows the editing functions that are available.

Function | Rules
--- | ---
Copy | You can copy both a source and a target node if: The node or its parent is not a clone. The node or its parent has not been cloned.
Paste | Appends the copied node and its children as a child of the selected node. If a copied node contains cloned nodes, Data View Builder pastes them as regular nodes. Only the hierarchical structure transfers. If a pasted node is a duplicate, Data View Builder renames the node as _copy1, _copy2, and so on. Pasted nodes lose any mapping attributes; however, Data View Builder will display a warning and allow you to abandon the task. The paste function works only on an element node. You cannot paste a child node to an attribute node. This menu item is unavailable unless you have data on the clipboard.
Add a Child (Node or Attribute) | Appends a new node or attribute as a child to the selected node. The name of the new element or node is new_node or new_attribute. The add function works only on an element node. You cannot add a child node to an attribute node. Note: Only string type data is supported.
Delete | Removes a selected node. If the node to be deleted is a mapped node, Data View Builder will display a warning and allow you to abandon the task.
Rename | Allows you to rename the selected node. An error message appears if the new name is a duplicate of a node at the same hierarchical level.

Mapping Source and Target Schemas

Mapping is a visual relationship structure among the critical data elements in the query. When you combine these relationships with functions, you have a set of instructions for the query generation engine. If you think of the selected data nodes as the nouns (what we want to work on), the functions as the verbs (the action), then the mapping among the data elements creates a logical sentence that expresses the query.

Before you begin creating a query, it is important to expand the nodes in the source schemas to reveal the data elements that you want to use at their lowest level. To expand a node, right click on it and choose Expand.

Note: You can use automatic type casting to ensure that input parameters used in functions and mappings are appropriate to the function in which they are used. When Automatic Type Casting is in effect, Liquid Data verifies (and if necessary promotes) the data types of input parameters for all source-to-target mappings and functions. For more information about automatic type casting, see Using Automatic Type Casting.

Mapping Node to Node

You can use drag-and-drop mappings from one element or attribute to another to create conditions on source data and source-to-target mappings that will define the shape of the query result.

Mapping Nodes to Create Conditions on Source Data

Choose one of the following methods to map a source schema node to another source schema node to create a Condition.

- Drag and drop a source schema element/attribute to another source schema element/attribute to define an eq (equality) source condition

Or

- For all functions other than eq (equality), drag and drop the function to the first empty row in the Condition column in the Work area first before you drag and drop elements/attributes as function parameters. Then drag a source schema element/attribute and drop it into the same row of the Condition column. Drag a second source schema element/attribute and drop it into the same row of the Condition column.

Mapping Nodes to Create Source-to-Target Mappings

Choose one of the following methods to map a source schema node to a target schema node.

- Drag and drop a source schema element/attribute to a target schema element/attribute.

Or

- On the Mappings tab, drag-and-drop a source schema element/attribute into a row in the Sources column and drag-and-drop a target schema element/attribute into the same row in the Target column. For all functions other than eq (equality), drag-and-drop the function first before you drag and drop elements or attributes as function parameters.

Note: You cannot map the same node from more than one source schema to a single node in the target schema. For example, if you map STATE (under CUSTOMER) from the Broadband database to state? in the target schema, you cannot successfully map STATE from a second source schema to state? in the target schema. The last mapping completed is the only mapping from source to target that the query generation engine processes. If you need to create a relationship among all three STATE elements, map the element in one source schema to the element in the second source schema. Then map one of the source elements to the target element.

Example: Query Customers by State

Figure 3-1 shows a simple join of the source element STATE in the Broadband source schema (XM-BB-C) with a source element STATE in the Wireless source schema (PB-WL). This action joins the common elements in each schema and disregards those that do not occur in both schemas.

Next, to project a result we designate what the output of this relationship should look when the query runs. By mapping one of the sources to the target, we specify that we want to store the result in the target schema. Because we are collecting only information about states and defining only one element in the target schema, we are in effect asking that Liquid Data fill only that data element in the result when the query runs.

To do this, drag and drop the STATE element in PB-WL source schema onto the state? element (under STATE*) in the Target schema.

See Figure 3-1 for an example of the mappings described in this example.

Figure 3-1 Mapping Element to Element

Mapping Nodes to Functions

When you drag and drop a source node onto another source node (either within the same source schema or among different source schemas) you are automatically creating an equality relationship between the two elements/attributes using the eq (equals) function. In other words, the eq function is mapped by default for all drag-and-drop relationships you create among source elements/attributes.

You can also create the same equality relationship the "long" way by dragging and dropping the eq function onto a row in the Conditions tab and then dragging and dropping source elements/attributes into the same row, or by opening the Functions Editor and dragging and dropping the function and elements directly into the Editor.

To use any of the available functions other than eq (equality) function, you must use this second method of dealing directly with the functions as described below.

To use a function:

1. Drag and drop the function from the Toolbox "Functions" panel to the first empty row under the "Conditions" on the Conditions tab.

2. Drag a source schema node and drop it into the same row of the Condition column. Drag a second source schema node and drop it into the same row of the Condition column.

To edit an existing functional relationship:

1. Open the Functions Editor by clicking the Edit button.

2. Edit the statement as needed. You can delete the current parameters or function, and drag and drop a new function and source elements/attributes into the Functions Editor.

Figure 3-2 shows the functional relationship of equality (eq) between two source elements that was created by default when you mapped the source elements in Example: Query Customers by State. (Note that you could have created this same relationship directly in the Functions Editor the way you would create any other functional relationship between elements/attributes.)

Figure 3-2 Mapping Elements to Functions

To get the view shown in Figure 3-2, click on the Conditions tab, select the row with the condition in it to activate the Edit button, and click the Edit button. This displays the condition in the Functions Editor.

For more information about functions, see What are Functions?.

For more information about using the Functions Editor and working with functions on the UI, see Functions and The Function Editor in Starting the Builder and Touring the GUI.

Supported Mapping Relationships

Data View Builder and Liquid Data support any of the Mapping actions described in the following table.

Table 3-1 Supported Mapping Relationships

Types of Mappings | Description
--- | ---
Source node to another source node | Creates an equality relationship between the two elements/attributes using the eq (equals) function. The eq function is used by default for all drag-and-drop mappings created among source elements/attributes to create a condition that will filter for matching items found.
Source node to a function | The data becomes an input parameter to a function. (You can also provide constants and variables as function parameters.) Each function has its own specification of parameters. The output from a function can be input to another function. For an example of this, see Example 2: Aggregates in Query Cookbook (specifically, the step Ex 2: Step 8. Add the "count" Function within the Aggregates example).
Source node to a target node | By mapping a source to a target, you are projecting, or storing, the data onto the target schema. All query examples provided in this documentation show how to map source schema elements/attributes to target elements/attributes. For example, see Example: Return Customers by Name and Example: Query Customers by ID and Sort Output by State.
Function to target node | A function (f1)output can be another function's (f2) input. For an example of this, see Example 2: Aggregates in Query Cookbook (specifically, the step Ex 2: Step 8. Add the "count" Function within the Aggregates example).

Removing Mappings

Mapped elements/attributes in a query are displayed on the Mappings tab. You can change your mind and remove a mapping by selecting the row or cell that contains it and then clicking the trashcan button. (See Figure 3-3.)

Figure 3-3 Removing a Mapping

Setting Conditions

You can create conditions or filters on source data by doing any of the following:

- Drag-and-drop a source node onto another source node to build a conditional statement that defines the default eq (equality) functional relationship between the mapped elements/attributes. (See Supported Mapping Relationships.)

- Drag-and-drop source elements/attributes and functions directly into a row on the Conditions tab to build a conditional statement with any of the functions available from Design tab—> Toolbox tab—>Functions panel.

What are Functions?

Functions are used as the verbs or actions in condition statements that establish relationships between or operations on data source elements or attributes. (The data source elements/attributes become one type of parameter to the functions.) A function is a built-in executable process that manipulates the data to perform a task. You must pass one or more parameters, which can be source data, variables, or constant values, for the function to produce output. The function returns a result to you based on the conditional statements you build and how you specify where to store the result.

In the previous example (Example: Query Customers by State) we defined the default equality relationship between two source elements (by dragging and dropping the CUSTOMER "STATE" element from one source to another); then defined the result by dragging and dropping the CUSTOMER "STATE" element from one of the source schemas onto the analogous "STATE" element in the target schema.

If you need to find out something other than information based on equality, you will need to use a different function. For example, suppose you want to find out how many customer IDs in the Broadband database are not equal to those in the Wireless database. The default functional action is to look for equality. If you simply map one customer ID source element/attribute to the other, the query engine looks for those instances of matching data, or equality.

(For the relationship of not equal to, you need to go to Builder Toolbar—>Sources tab—>Functions panel, expand the Operators node, and choose the ne function.)

When any functional relationship is involved besides equality, you must chose from the list of functions available in the Builder Toolbar—>Sources tab—>Functions panel. At that point you are applying a filter of your choice. It is very important to choose the function before you map the elements. Most of the Data View Builder functions are standard XML query language functions supported by the W3C. For related information about using functions, see the Functions Reference.

Note: You can use automatic type casting to ensure that input parameters used in functions and mappings are appropriate to the function in which they are used. When Automatic Type Casting is in effect, Liquid Data verifies (and if necessary promotes) the data types of input parameters for all source-to-target mappings and functions. For more information about automatic type casting, see Using Automatic Type Casting.

Using Constants and Variables in Functions

Instead of choosing an existing element/attribute as a parameter value, you can use one of these methods to specify that a constant value should be used instead of a data element from a source schema.

- Click the Query Parameter Navigation panel. If you wish to add a new variable, type the variable name in the available text box. Click Add. The new variable appears in the Query Parameter tree. Double click the new variable to use it as a parameter value. You can change the value each time your run the query. (For details on defining query parameters, see Query Parameters: Defining, which includes a list of supported data types for query parameters in Table 2-2.)

- Click the Constant Navigation panel. If the constant already exists in the Constant tree, double click the constant you want to use as a parameter in a function or drag and drop the constant to the appropriate row in the Condition column.(For details on defining constants, see Constants.)

Removing Conditions

Conditions are displayed in the Design view on the Conditions tab. You can change your mind and remove a condition by selecting the row or cell that contains it and then clicking the trashcan button. (See Figure 3-4.)

Figure 3-4 Removing a Condition

Adding or Deleting Parameters in a Condition Statement

To add or delete a parameter, select the row that contains the condition you want to edit and click the Edit button to bring up the Functions Editor.

In the Functions Editor, you can select the parameter you want to delete and click the trash can or use the options on the Edit menu to modify the condition statement.

You can drag and drop different functions into the Functions Editor from the Functions panel on the Builder Toolbar—>Toolbox tab.

Showing or Hiding Data Types

You can show or hide data types on all source and target elements/attributes in schema windows. Select View—>Data Types to display the data type of any source or target element/attribute, as well as required function parameter types. (An "X" next to the Data Types option on the View menu indicates that it is on.)

Using Automatic Type Casting

You can use automatic type casting to ensure that input parameters used in functions and mappings are appropriate to the function in which they are used.

Note: For a complete reference showing how Liquid Data transforms source element/attribute data types to data types of target elements/attributes, see the Type Casting Reference.

Select Automatic Type Casting on the Query menu to ensure that Liquid Data will assign (cast) a new data type when the source node data type does not match the mapped target node data type, and the source node is eligible to be type cast to the target node data type. (An "X" next to the Automatic Type Casting option on the Query menu indicates that it is on.)

When function parameters have a numeric type mismatch, the Liquid Data server can promote the input source to the input type required by the function if the promotion adheres to the prescribed promotion hierarchy. The promotion hierarchy exists only for numeric values.

Type | Promoted Type
--- | ---
byte | short
short | int
int | long
long | integer
integer | decimal
decimal | float
float | double

If the type mismatch requires casting in the reverse order, the server does not attempt type casting. In this case, Liquid Data attempts to type cast but the results may be unpredictable. For example, if the required function input type is xs:decimal, then source data that is integer, long, int, short, or byte can easily be promoted to a data type with more precision or larger number of digits. The server will complete that task. If the input function type is xs:double or xs:float and the required input type is xs:integer or xs:byte, Liquid Data tries to type cast successfully, but there may be unpredictable rounding or truncating. All other type mismatches, such as xs:date, xs:dateTime, or xs:string, require a type cast to avoid a type mismatch error.

Clear the Automatic Type Casting check box to disable this feature.

Exceptions to Automatic Type Casting

Liquid Data does not type cast comparison operators (such as eq, le, ge, ne, gt, lt, or ne) or any functions that accept xsext:anytype.

Type casting does not apply to function parameters (as well as target schema elements/attributes) that require these data types:

- xsext:item

- xsext:anyValue

- xsext:anyType

- Any other data type that cannot be cast

Automatic type casting does not succeed in all cases. If the source data is not compatible with the data type of the target node, automatic type casting will not improve the query results. For example, mapping a date to a numeric type may not produce useful results if the data is not relevant. You may not see an error on a type mismatch until the Liquid Data Server tries to run the query.

Examples of Simple Queries

This section includes walk-through examples of how to build some simple queries using the Data View Builder tools and features just described:

- Example: Return Customers by Name

- Example: Query Customers by ID and Sort Output by State

To work through these examples, begin on the Data View Builder "Design" tab. If you have worked through the previous example using amtByState target schema, we suggest you close that project and open/save a new project for each of the examples described below.

Example: Return Customers by Name

In this example, you want to return the last and first names of all Wireless customers with a last name that begins with "K."

Build the Query

The approach we will use is similar to the first example in this chapter; however, you are adding a condition that the last name begins with "K." Build the condition with the starts-with function as follows.

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

2. On the Builder Toolbar—>Sources tab, click Relational Databases. Double-click on the PB-WL (Wireless) relational database to add it to the project.

3. Create amtByState.xsd target schema and add it to the server repository. (For a copy of the schema file and instructions on how to save it to the repository, see Adding a Target Schema and the schema shown in Listing 3-1.)

4. Choose File—>Set Target Schema. Use the file browser to navigate to the Repository and select amtByState.xsd as the target schema.

   This target schema is displayed as a docked schema window on the right side of the workspace. To expand all nodes in the target schema, select the top level node, right mouse click and choose Expand from the popup menu.

5. Map the source schema CUSTOMER LAST_NAME to the corresponding LAST_NAME element in the target schema.

6. On the Builder Toolbar—>Toolbox tab, click Functions. Under String functions, find the starts-with function. Drag and drop starts-with onto the first row in the Conditions Tab.

   When you do this, the Functions Editor will automatically pop up and show you the condition statement with the starts-with function and variable placeholders.

7. Drag and drop CUSTOMER "LAST_NAME" element from the Source schema onto the first parameter (operand1).

   Note: This example shows what the function looks like with menu option View—>Data Types turned off. If you have this option on (it is on by default), data types for each parameter will also show.

8. On the Builder Toolbar—>Toolbox tab, click Constants. Type "K" in the text box, then drag and drop the Constants icon to the right of the text field onto the second parameter (operand2). (For details on using the Constants panel, see Constants in Starting the Builder and Touring the GUI.)

   The condition statement should look similar to that shown in following figure.

9. Close the Function Editor by clicking Close. (The condition statement is displayed on the first row of the Conditions tab in the Source column.)

Figure 3-5 shows the Design view of the query with conditions and source-to-target mappings completed.

Figure 3-5 Design View of Query Example: Return Customers By Name

View the XQuery and Run the Query to Test it

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.

1. Click on the Test tab.

   The generated XQuery is displayed in the Query panel on the left side of the Test tab as shown in Figure 3-6. The full XQuery is also provided in Listing 3-2.

   Figure 3-6 XQuery for Example: Return Customers By Name

Listing 3-2 XQuery for Example: Return Customers By Name

>     {--	Generated by Data View Builder 1.0 	--}

>     <customers>
>     	{
>     	for $PB_WL.CUSTOMER_1 in document("PB-WL")/db/CUSTOMER
>     	where xf:starts-with($PB_WL.CUSTOMER_1/LAST_NAME,"K")
>     	return
>     	<CUSTOMER>
>     		<LAST_NAME>{ xf:data($PB_WL.CUSTOMER_1/LAST_NAME) }</LAST_NAME>
>     	</CUSTOMER>
>     	}
>     </customers>

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

   The query result is shown in the Result panel on the right side of the Test tab as shown in Figure 3-7. The full XML query result is provided in Listing 3-3.

   Figure 3-7 Query Result for Example: Return Customers By Name

Listing 3-3 XML Query Result for Example: Return Customers By Name

>     <customers>
>       <CUSTOMER>
>         <LAST_NAME>KAY_1</LAST_NAME>
>       </CUSTOMER>
>       <CUSTOMER>
>         <LAST_NAME>KAY_2</LAST_NAME>
>       </CUSTOMER>
>       <CUSTOMER>
>         <LAST_NAME>KAY_3</LAST_NAME>
>       </CUSTOMER>
>       <CUSTOMER>
>         <LAST_NAME>KAY_4</LAST_NAME>
>       </CUSTOMER>
>       <CUSTOMER>
>         <LAST_NAME>KAY_5</LAST_NAME>
>       </CUSTOMER>
>       <CUSTOMER>
>         <LAST_NAME>KAY_6</LAST_NAME>
>       </CUSTOMER>
>       <CUSTOMER>
>         <LAST_NAME>KAY_7</LAST_NAME>
>       </CUSTOMER>
>       <CUSTOMER>
>         <LAST_NAME>KAY_8</LAST_NAME>
>       </CUSTOMER>
>       <CUSTOMER>
>         <LAST_NAME>KAY_9</LAST_NAME>
>       </CUSTOMER>
>       <CUSTOMER>
>         <LAST_NAME>KAY_10</LAST_NAME>
>       </CUSTOMER>
>     </customers>

(For complete details on how to test and run a query, see Testing Queries.)

Example: Query Customers by ID and Sort Output by State

In this example, there are two pieces of information that we want to display in the result. We want to find Customer IDs for customers who exist in both databases and we want to know the state each found customer resides in.

This example shows how to do the following:

- Project output

- Specify the order of the result

Open the Data Sources and Add a Target Schema

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

2. 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.

3. Choose File—>Set Target Schema. Use the file browser to navigate to the Repository and select amtByState.xsd as the target schema.

   Note: If amtByState.xsd is not already saved in the Samples server Repository, you can create it yourself and save it to the Repository. For a copy of the schema file and instructions on how to save it to the Repository, see Adding a Target Schema and the schema shown in Listing 3-1.

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

Map Nodes from Source to Target Schema to Project Output

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

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

2. Drag and dropWireless (PB-WL) LAST_NAME (under CUSTOMER*) onto LAST_NAME in the Target schema.

3. 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 3-8 Example: Query Customers by ID and Sort Output by State

Specify the Order of the Result Using the Sort By Features

To order the output alphabetically by State do the following:

1. Click the Sort By tab.

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

2. 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 the XQuery and Run the Query to Test it

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.

1. Click on the Test tab.

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

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

>     {--	Generated by Data View Builder 1.0	--}

>     <customers>
>     	{
>     	for $PB_WL.CUSTOMER_1 in document("PB-WL")/db/CUSTOMER
>     	let $CUSTOMER_2 :=
>     	               for $PB_BB.CUSTOMER_3 in document("PB-BB")/db/CUSTOMER
>     	               where ($PB_BB.CUSTOMER_3/CUSTOMER_ID  eq  $PB_WL.CUSTOMER_1/CUSTOMER_ID)
>     	               return
>     	               xf:true()
>     	where xf:not(xf:empty($CUSTOMER_2))
>     	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>

1. 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.

Listing 3-5 XML Query Result for Example: Query Customers by ID and Sort Output 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>

Understanding Scope in Basic and Advanced Views

Adding Scope to a condition is a way to specify the extent that the condition applies to the result. It helps you specify which part of a data view is the focal point for a particular condition in the query. A scope setting affects the placement of a "where" clause in the XQuery generation.

When you add a condition, the Data View Builder makes a best guess as to where the condition should appear in the query. The Data View Builder draws information from the structure of the target schema, the mappings from source schemas to the target schema, and the conditions.

In most cases, scope is implicit and the query generator can determine what the desired result should be. In other cases, it makes a very conservative assumption about the resulting scope of the condition. You can communicate your objectives more efficiently if you specify exactly what you want the query to return. By toggling to the Advanced view in the Data View Builder (Advanced View for Defining Explicit Scope for Conditions) and setting scope you explicitly indicate what part of the output, or query result, is affected by the condition.

The following sections are included here:

- Where Does Scope Apply?

- Basic View (Automatic Scope Settings)

- Advanced View (Setting the Scope Manually)

- When to Use Advanced View to Set Scope Manually

- Task Flow Model for Advanced View Manual Scoping

- Returning to Basic View

Where Does Scope Apply?

There are three candidate areas where Liquid Data sets scope. Scope candidates are:

- All repeatable elements in the target schema

- All repeatable input elements in functions

- The root of the target schema

Remember that a repeatable element always appears with an asterisk (*) or plus sign (+) occurrence indicator.

Basic View (Automatic Scope Settings)

The default setting in the Data View Builder is the basic view. In this view, when you add a condition to any query, the Data View Builder applies an automatic scope setting using internal rules that specify where the condition should appear in the query.

In most cases, the scope setting that Data View Builder chooses for each condition is the correct setting. When you have complex conditions, or a particular result in mind, you may want to switch to the Advanced mode where you can change scope settings as you wish.

Advanced View (Setting the Scope Manually)

Data View Builder enables you to override the automatic scope setting by using an Advanced view of the existing scope settings. When you switching to an Advanced view, Data View Builder displays the setting it selected for automatic scoping. You can change any or all of the individual scope settings or allow them to retain their original values.

When you switch to the Advanced view, it is not necessary to change any of the explicit scope settings selected by Data View Builder. However, if you add new conditions when you are in Advanced view, or change existing conditions, you must set the new scope manually for each condition.

To switch to the Advanced view, click Advanced view toggle so that an X is displayed next to Advanced view. The Conditions tab expands to show more information about the condition targets.

- The Current Scope initially shows the target schema root. Before you map schema sections and create conditions, you can drag a repeatable target schema or function input node to set the scope for a complete section of the target schema. Thereafter, the value in the Current Scope text box determines what will appear automatically in a Scope column cell for any new condition that you create. For more information about this technique, see Task Flow Model for Advanced View Manual Scoping.

- The Enabled column contains a switch to include or exclude a condition when the query runs.

- The Condition column shows the source node, condition, and condition target node.

- The Scope column shows which node Liquid Data automatically selected in the target schema to focus the result. You can also drag a repeatable target schema node directly to a cell in this column to change the scope for that condition.

- The Reset button in the upper right recalculates all scope settings and returns them to the automatic settings selected by Liquid Data.

Figure 3-9 Advanced View Showing Explicit Scope on Conditions Tab

Condition and Target pairs appear row by row. If there are multiple scope settings for a condition, the condition reappears in separate rows to display each unique scope setting.

Figure 3-10 Advanced View

The Current Scope text box shows the default scope setting for every condition that you add. Remember that the Basic view settings will continue to appear until you change them. If you add a new condition in Advanced view, the default scope is the target schema root until you change that value.

When to Use Advanced View to Set Scope Manually

Data View Builder automatically scopes conditions wherever it is most logical, possibly in more than one place. However, occasionally it may not put the automatic scope setting where you think it should be. In these cases, you can switch to the Advanced view to overwrite the automatic scope setting.

The most common case occurs when a condition logically applies in two places, but you want it to appear in only one place. You can diagnose this by examining the XQuery translation for where clauses, or do a test run of the query to view the result. If you are not satisfied, switch to the Advanced view to determine where the condition appears. Remember that Data View Builder lists the same condition more than once if it has more than one scope setting. Change the scope setting that you do not want by following the directions in Task Flow Model for Advanced View Manual Scoping.

A less common case is when you want to create an assertion. For example, the Liquid Data Server should return a result only when a certain condition occurs. You can accomplish this if you switch to the Advanced view, create the condition, and set the scope for the condition to be the root of the target schema.

Note: It is a good idea to run the query using the automatic scope settings first to ensure that it is necessary to revise the scope setting.

Task Flow Model for Advanced View Manual Scoping

If you decide to override automatic scope settings, there is a workflow model that will help you design the query, create conditions, and determine the scope. By following this methodology, you will find it is easy to create a query where you control the scope. Consider the example shown in Figure 3-11 of two source schemas: PB-BB and PB-WL, and the target schema customerLineItems.xsd.

Figure 3-11 Schemas for Manual Scope Example

The target schema, customerLineItems.xsd, has a hierarchical structure. There are three distinct sections in the schema that represent repeatable data. customer and order each have an asterisk (*) as the occurrence indicator. line_item has a plus sign (+) as the occurrence indicator. This means that the child nodes without an asterisk or plus are non-repeating. For each customer, there is one occurrence of first_name, last_name, and id. Each customer may have zero or more orders. When an order exists, each order has one id, date, and amount. If an order exists, there must be at least one line_item. Work on sections that appear under a repeatable node.

This workflow model assumes that you can build your query in steps, focusing on each section in the target schema as you go. Follow these steps for each section in the target schema where you want a result to appear.

1. Choose a repeatable section of the target schema for our scope. A section is a repeatable node (parent) and its children. It is recommended that you work from the outside in. In this case, the outermost section is the customer* section. (For this example we want to collect the first_name, last_name, and id in the result.)

2. Set the highest repeatable node in this section as the default scope, which in this case is customer*. Drag that element from the target schema onto the Current Scope text box on the Conditions tab. (For this example we drag and drop customerLineItems.xsd onto the Current Scope text box.)

3. Map selected source elements/attributes to that repeatable section in the target schema.

   For this example, we do the following mappings:

   - Map [PB-WL]/db/CUSTOMER*/FIRST_NAME to [customerLineItems.xsd]/customers/customer*/first_name.

   - Map [PB-WL]/db/CUSTOMER*/LAST_NAME to [customerLineItems.xsd]/customers/customer*/last_name.

   - Map [PB-WL]/db/CUSTOMER*/CUSTOMER_ID to [customerLineItems.xsd]/customers/customer*/id.

4. Set any conditions that connect and filter the mapped sources.

   By setting the default scope before creating the condition, Data View Builder sets the condition scope to that value.

   By mapping one section at a time and using the repetitive ancestor node as the default scope, your conditions will apply exactly where you need them to appear in the result.

   For our example, we set as a condition a join between CUSTOMER_ID in the PB-BB schema and CUSTOMER_ID in the PB-WL schema as shown in the figure below.

5. Repeat these steps for each section of the target schema where you want data to appear in the result. Work on one section at a time and work from the outside (more general) to the inside (most specific). Ensure that you set the default target, map, and define the conditions, before you move to the next section. The general rule is that any mapping with an associated condition requires a scope setting.

In a small number of cases, you may apply a condition on the argument (input) to a function that requires choosing the function as the default scope. This is not common but will occur when you choose a complex aggregate function.

Returning to Basic View

When you toggle Advanced view off (no X showing next to Advanced view), Data View Builder returns to automatic scoping mode and discards the changes you made in manual mode. The Current Scope text box and the Targets column disappear.

Saving Projects from Basic or Advanced View

If you save a project from Basic view, the project file discards scope information. When you reopen this project, Liquid Data once again applies automatic scope using its internal algorithms.

If you save a project from the Advanced view, all conditions retain current scope settings. When you reopen this project, all Advanced view settings appear.

Version Control

Liquid Data assigns a version attribute to the project file. If you open a project file created with an earlier version of Liquid Data, the project opens in the Advanced view if all conditions have explicit scope settings.

Scope Recursion Errors

It is possible to create a query where a condition depends on the values returned by a function, but the function input depends on the condition. For example:

- Select the xf:count function and map a source node to be the input of xf:count.

- Create a condition that uses the output of the xf:count function.

- In the Advanced view, set the condition target to the input of the xf:count function.

The xf:count function input must be filtered by applying the condition, but the condition input is the output of xf:count.

Data View Builder does not allow this to happen when automatic scoping is enabled. However, if you clear the Auto-Select Targets check box and set scope manually, it is possible for you to set the scope of a condition to a function input that creates a circular dependency. Data View Builder cancels the action and generates an error message:

Setting Scope/Target of condition {condition} to {scope node} creates circular dependency

Recommended Action

Basic view should generally support most scenarios—we expect that only a few users and/or queries will require use of the Advanced view manual scoping feature. You can assume that Liquid Data can interpret the scope requirements correctly for most types of queries. If you do choose to set scope manually, examine the generated XQuery to ensure the condition targets meet your expectations. If the recursion error message appears, consider resetting all condition scope targets. Override the automatic settings one at a time, switch to Test view to examine the query, run it, and assess the results.

Understanding Query Design Patterns

Here we present some common query patterns generated by the Data View Builder and provide high-level guidelines for effective query design including target schema design and source replication.

- Target Schema Design Guidelines and Query Examples

- Source Replication

Target Schema Design Guidelines and Query Examples

This section provides several examples of queries built with the Data View Builder. We describe the conditions and mappings for a query and the resulting XQuery. The purpose of this is to illustrate how we follow certain guidelines to design the various types of example queries.

- Design Guidelines

- Examples of Effective Query Design

Design Guidelines

Use these guidelines when working with target schemas in your queries:

1. Make sure the target schema has proper cardinality. For example, if you intend to project customer orders in your result, the target schema should reflect the parent-child relationship between "customer" and "orders."

   All examples in Examples of Effective Query Design demonstrate this guideline.

2. Understand how target schema conformity works and use it efficiently. In an XML schema:

   - A plus sign (+) next to a node indicates that the node is repeatable and required. (In other words, there must be 1 or more occurrences of this.)

   Since this setting requires extra checking of the data, most queries that use it pay some performance penalty.

   - An asterisk (*) next to a node indicates that the node is repeatable and optional. (In other words, there can be 0 or more occurrences of this.)

   Always use this setting if appropriate to avoid unnecessary data checking and the associated performance hit. Use this especially if you know that the underlying data sources enforce referential integrity between parent-child items.

   The following examples demonstrate this guideline:

   - Example 3: Find all Broadband customers (CUSTOMER is Repeatable and Optional)

   - Example 4: Find all Broadband customers (CUSTOMER is Repeatable and Required)

   - Example 5: Find all Broadband customers and return their Wireless orders if the customer has Wireless orders (ORDER is Required and Optional)

   - Example 6: Find the list of all Broadband customers that have at least one Wireless order and return their Wireless orders (ORDER is Repeatable and Required)

3. Project at least one element from each data source that is part of the query to the target schema. The following examples illustrates this guideline:

   - Example 1: Find all Broadband customers who are also Wireless customers

   - Example 2: Find all Broadband customers and their Wireless line items

Examples of Effective Query Design

For the following examples, assume we have two schema sets (databases) with the following entities (tables).

Broadband Schema with the following tables:

- Customer

- Order

- Line Item

Wireless Schema with the following tables:

- Customer

- Order

- Line Item

Example 1: Find all Broadband customers who are also Wireless customers

In this situation you do not project anything from the Wireless customer table.

The generated query will iterate over all customers and in Broadband and check for the existence of a matching customer in Wireless. This query also ensures duplicate customers are not returned from Broadband in the event a Broadband customer matches more than one Wireless customer.

Instructions to create query using Data View Builder:

1. Map the source PB-BB.CUSTOMER FIRST_NAME and LAST_NAME to the target CUSTOMER FIRST_NAME and LAST_NAME respectively.

2. Create the condition PB_BB.CUSTOMER CUSTOMER_ID eq PB_WL.CUSTOMER.CUSTOMER_ID

The query looks like:

>     <db>
>     	{
>     	for $PB_BB.CUSTOMER_1 in document("PB-BB")/db/CUSTOMER
>     	let $CUSTOMER_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:true()
>     	where xf:not(xf:empty($CUSTOMER_2))
>     	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>
>     	}
>     </db>

If you do not care about duplicates or know there will not be duplicates, you can avoid xf:entity(...) checking by projecting an element from the Wireless customer table.

Instructions to create this alternative version of the query using Data View Builder:

1. Map the source PB-BB.CUSTOMER FIRST_NAME and LAST_NAME to the target CUSTOMER FIRST_NAME and LAST_NAME respectively.

2. Map the source PB-WL.CUSTOMER STATE to the target CUSTOMER STATE (Additional projection).

3. Create the condition PB_BB.CUSTOMER CUSTOMER_ID eq PB_WL.CUSTOMER.CUSTOMER_ID

The query now looks like:

>     <db>
>     	{
>     	for $PB_WL.CUSTOMER_1 in document("PB-WL")/db/CUSTOMER
>     	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
>     	<CUSTOMER>
>     		<FIRST_NAME>{ xf:data($PB_BB.CUSTOMER_2/FIRST_NAME) }</FIRST_NAME>
>     		<LAST_NAME>{ xf:data($PB_BB.CUSTOMER_2/LAST_NAME) }</LAST_NAME>
>     		<STATE>{ xf:data($PB_WL.CUSTOMER_1/STATE) }</STATE>
>     	</CUSTOMER>
>     	}
>     </db>

Example 2: Find all Broadband customers and their Wireless line items

This query basically asks for all Broadband customers and Wireless line items for which there exists a Wireless order that joins with both the Broadband customer and Wireless line item.

Now for this situation user does not project anything from Wireless order table.

The generated query will iterate over all customers and in Broadband, then for each line item it will check for the existence of a matching order in Wireless that also matches a customer in Broadband.

Instructions to create query using Data View Builder:

1. Map the source PB-BB.CUSTOMER FIRST_NAME and LAST_NAME to the target CUSTOMER FIRST_NAME and LAST_NAME respectively.

2. Map the source PB-WL.CUSTOMER_ORDER_LINE_ITEM PRODUCT_NAME and EXPECTED_SHIP_DATE to the target CUSTOMER_ORDER_LINE_ITEM PRODUCTION and EXPECTED_SHIP-DATE respectively.

3. Create the condition PB_BB.CUSTOMER CUSTOMER_ID eq PB_WL.CUSTOMER_ORDER.CUSTOMER_ID

4. Create the condition PB_WL.CUSTOMER_ORDER.ORDER_ID eq PB_WL.CUSTOMER_ORDER_LINE_ITEM.ORDER _ID

The query looks like:

>     <ROWS>
>     	{
>     	for $PB_BB.CUSTOMER_1 in document("PB-BB")/db/CUSTOMER
>     	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_ORDER>
>     			{
>     			for $PB_WL.CUSTOMER_ORDER_LINE_ITEM_2 in document("PB-WL")/db/CUSTOMER_ORDER_LINE_ITEM
>     			let $CUSTOMER_ORDER_LINE_ITEM_3 :=
>     			               for $PB_WL.CUSTOMER_ORDER_4 in document("PB-WL")/db/CUSTOMER_ORDER
>     			               where ($PB_BB.CUSTOMER_1/CUSTOMER_ID  eq  $PB_WL.CUSTOMER_ORDER_4/CUSTOMER_ID)
>     			                 and ($PB_WL.CUSTOMER_ORDER_4/ORDER_ID  eq  $PB_WL.CUSTOMER_ORDER_LINE_ITEM_2/ORDER_ID)
>     			               return
>     			               xf:true()
>     			where xf:not(xf:empty($CUSTOMER_ORDER_LINE_ITEM_3))
>     			return
>     			<CUSTOMER_ORDER_LINE_ITEM>
>     				<PRODUCT_NAME>{ xf:data($PB_WL.CUSTOMER_ORDER_LINE_ITEM_2/PRODUCT_NAME) }</PRODUCT_NAME>
>     				<EXPECTED_SHIP_DATE>{ xf:data($PB_WL.CUSTOMER_ORDER_LINE_ITEM_2/EXPECTED_SHIP_DATE) }</EXPECTED_SHIP_DATE>
>     			</CUSTOMER_ORDER_LINE_ITEM>
>     			}
>     		</CUSTOMER_ORDER>
>     	</CUSTOMER>
>     	}
>     </ROWS>

For performance reasons, we recommend that you project the intermediate data, especially if you do not care about duplicates or know there will not be duplicates. In the example above, you can project an element from the Wireless order table.

Instructions to create query using Data View Builder:

1. Map the source PB-BB.CUSTOMER FIRST_NAME and LAST_NAME to the target CUSTOMER FIRST_NAME and LAST_NAME respectively.

2. Map the source PB-WL.CUSTOMER_ORDER ORDER_ID to the target CUSTOMER_ORDER ORDER_ID. (Additional projection)

3. Map the source PB-WL.CUSTOMER_ORDER_LINE_ITEM PRODUCT_NAME and EXPECTED_SHIP_DATE to the target CUSTOMER_ORDER_LINE_ITEM PRODUCTION and EXPECTED_SHIP-DATE respectively.

4. Create the condition PB_BB.CUSTOMER CUSTOMER_ID eq PB_WL.CUSTOMER_ORDER.CUSTOMER_ID

5. Create the condition PB_WL.CUSTOMER_ORDER.ORDER_ID eq PB_WL.CUSTOMER_ORDER_LINE_ITEM.ORDER _ID

The query looks like:

>     <ROWS>
>     	{
>     	for $PB_BB.CUSTOMER_1 in document("PB-BB")/db/CUSTOMER
>     	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>
>     		{
>     		for $PB_WL.CUSTOMER_ORDER_2 in document("PB-WL")/db/CUSTOMER_ORDER
>     		where ($PB_BB.CUSTOMER_1/CUSTOMER_ID  eq  $PB_WL.CUSTOMER_ORDER_2/CUSTOMER_ID)
>     		return
>     		<CUSTOMER_ORDER>
>     			<ORDER_ID>{ xf:data($PB_WL.CUSTOMER_ORDER_2/ORDER_ID) }</ORDER_ID>
>     			{
>     			for $PB_WL.CUSTOMER_ORDER_LINE_ITEM_3 in document("PB-WL")/db/CUSTOMER_ORDER_LINE_ITEM
>     			where ($PB_WL.CUSTOMER_ORDER_2/ORDER_ID  eq  $PB_WL.CUSTOMER_ORDER_LINE_ITEM_3/ORDER_ID)
>     			return
>     			<CUSTOMER_ORDER_LINE_ITEM>
>     				<PRODUCT_NAME>{ xf:data($PB_WL.CUSTOMER_ORDER_LINE_ITEM_3/PRODUCT_NAME) }</PRODUCT_NAME>
>     				<EXPECTED_SHIP_DATE>{ xf:data($PB_WL.CUSTOMER_ORDER_LINE_ITEM_3/EXPECTED_SHIP_DATE) }</EXPECTED_SHIP_DATE>
>     			</CUSTOMER_ORDER_LINE_ITEM>
>     			}
>     		</CUSTOMER_ORDER>
>     		}
>     	</CUSTOMER>
>     	}
>     </ROWS>

Example 3: Find all Broadband customers (CUSTOMER is Repeatable and Optional)

The target schema is ROWS(CUSTOMER*). This query returns the root element and all Broadband customers. Since, CUSTOMER is optional, an empty <ROWS/> element could be returned as the result of the query since it would conform to the schema.

Instructions to create query using Data View Builder:

- Map the source PB-BB.CUSTOMER FIRST_NAME and LAST_NAME to the target CUSTOMER FIRST_NAME and LAST_NAME respectively.

The following query will be generated. Notice that this query will indeed return an empty root element <ROWS/> if there are not any Broadband customers.

>     The query looks like:
>     <ROWS>
>     	{
>     	for $PB_BB.CUSTOMER_1 in document("PB-BB")/db/CUSTOMER
>     	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>
>     	}
>     </ROWS>

Example 4: Find all Broadband customers (CUSTOMER is Repeatable and Required)

This time the target schema is ROWS(CUSTOMER+). Again, this query returns the root element and all Broadband customers. But, since, CUSTOMER is required, in case there are no Broadband customer, an empty <ROWS/> element cannot be returned as the result of the query since such a result would not conform to the given target schema.

Instructions to create query using Data View Builder:

- Map the source PB-BB.CUSTOMER FIRST_NAME and LAST_NAME to the target CUSTOMER FIRST_NAME and LAST_NAME respectively.

Below is the query generated under this schema. This query will return the root element and all Broadband customers that exist. Observe that if there are not any Broadband customers then an empty result will be returned (not even the root element).

>     let $CUSTOMER_1 :=
>                    for $PB_BB.CUSTOMER_2 in document("PB-BB")/db/CUSTOMER
>                    return
>                    <CUSTOMER>
>                    	<FIRST_NAME>{ xf:data($PB_BB.CUSTOMER_2/FIRST_NAME) }</FIRST_NAME>
>                    	<LAST_NAME>{ xf:data($PB_BB.CUSTOMER_2/LAST_NAME) }</LAST_NAME>
>                    </CUSTOMER>
>     where xf:not(xf:empty($CUSTOMER_1))
>     return
>     <ROWS>
>     	{ $CUSTOMER_1 }
>     </ROWS>

The pattern of this query is discussed in more detail in Example 6: Find the list of all Broadband customers that have at least one Wireless order and return their Wireless orders (ORDER is Repeatable and Required).

Example 5: Find all Broadband customers and return their Wireless orders if the customer has Wireless orders (ORDER is Required and Optional)

In this case, the target schema is ROWS(CUSTOMER*(ORDER*)). The target schema allows for customers with zero orders. This means that the query can (and should) return customers without orders. Practically, this makes the query is a left outer-join between customers and orders.

Instructions to create query using Data View Builder:

1. Map the source PB-BB.CUSTOMER FIRST_NAME and LAST_NAME to the target CUSTOMER FIRST_NAME and LAST_NAME respectively.

2. Map the source PB-WL.CUSTOMER_ORDER ORDER_DATE and SHIP_METHOD to the target CUSTOMER_ORDER ORDER_DATE and SHIP_METHOD respectively.

3. Create the condition PB_BB.CUSTOMER CUSTOMER_ID eq PB_WL.CUSTOMER_ORDER.CUSTOMER_ID

The query looks like:

>     <ROWS>
>     	{
>     	for $PB_BB.CUSTOMER_1 in document("PB-BB")/db/CUSTOMER
>     	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>
>     		{
>     		for $PB_WL.CUSTOMER_ORDER_2 in document("PB-WL")/db/CUSTOMER_ORDER
>     		where ($PB_BB.CUSTOMER_1/CUSTOMER_ID  eq  $PB_WL.CUSTOMER_ORDER_2/CUSTOMER_ID)
>     		return
>     		<CUSTOMER_ORDER>
>     			<ORDER_DATE>{ xf:data($PB_WL.CUSTOMER_ORDER_2/ORDER_DATE) }</ORDER_DATE>
>     			<SHIP_METHOD>{ xf:data($PB_WL.CUSTOMER_ORDER_2/SHIP_METHOD) }</SHIP_METHOD>
>     		</CUSTOMER_ORDER>
>     		}
>     	</CUSTOMER>
>     	}

Example 6: Find the list of all Broadband customers that have at least one Wireless order and return their Wireless orders (ORDER is Repeatable and Required)

In this case, the target schema is ROWS(CUSTOMER*(ORDER+)). Now, the target schema does not allow for customers with zero orders. This means that the query should not return customers without orders. Practically, this makes the query is a (natural) join between customers and orders.

Instructions to create query using Data View Builder:

1. Map the source PB-BB.CUSTOMER FIRST_NAME and LAST_NAME to the target CUSTOMER FIRST_NAME and LAST_NAME respectively.

2. Map the source PB-WL.CUSTOMER_ORDER ORDER_DATE and SHIP_METHOD to the target CUSTOMER_ORDER ORDER_DATE and SHIP_METHOD respectively.

3. Create the condition PB_BB.CUSTOMER CUSTOMER_ID eq PB_WL.CUSTOMER_ORDER.CUSTOMER_ID

The query looks like:

>     <ROWS>
>     	{
>     	for $PB_BB.CUSTOMER_1 in document("PB-BB")/db/CUSTOMER
>     	let $CUSTOMER_ORDER_2 :=
>     	               for $PB_WL.CUSTOMER_ORDER_3 in document("PB-WL")/db/CUSTOMER_ORDER
>     	               where ($PB_BB.CUSTOMER_1/CUSTOMER_ID  eq  $PB_WL.CUSTOMER_ORDER_3/CUSTOMER_ID)
>     	               return
>     	               <CUSTOMER_ORDER>
>     	               	<ORDER_DATE>{ xf:data($PB_WL.CUSTOMER_ORDER_3/ORDER_DATE) }</ORDER_DATE>
>     	               	<SHIP_METHOD>{ xf:data($PB_WL.CUSTOMER_ORDER_3/SHIP_METHOD) }</SHIP_METHOD>
>     	               </CUSTOMER_ORDER>
>     	where xf:not(xf:empty($CUSTOMER_ORDER_2))
>     	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_ORDER_2 }
>     	</CUSTOMER>
>     	}
>     </ROWS>

The general pattern for handling required repeatable elements in the target schema is as follows. The goal is to be able to check for existence of at least one element before we generate the parent. Generation of required repeatable elements is "promoted" the element to the nearest optional repeatable ancestor (or the root of the result if there is no such element). There the list of elements is computed inside a let clause. After that, and the result (list) of this let clause is checked whether it is empty or not, before producing the rest of the result.

In this case, the ORDER element is required so we need to check for existence of orders before we produce customer. This means that we need to generate the list of orders for each customer, and output the customer only if this list is not empty.

Source Replication

A source is said to be replicated if the source appears multiple times in a query. Specifically in XQuery, a source is replicated if document("source name") appears multiple times in the XQuery, usually appearing in two different for clauses. Similarly in SQL, a source is replicated if the source (table) appears twice in a FROM clause (or in two different FROM clauses). (See the next section for examples.)

Why is source replication necessary?

The simplest example of a necessary source replication would be a self-join in SQL. In the classic example of a self-join, the query wants to get all the pairs of employee names to manager names from a single employee table:

>     SELECT e.name, m.name
>     FROM employee e, employee m
>     WHERE e.manager_id = m.id

In XQuery, the query would look like:

>     <employee_managers>
>     {
>     for $e in document("employee")//employee
>     for $m in document("employee")//employee
>     where $e.manager_id eq $m.id
>     return
>     <employee_manager>
>     	<employee> {$e.name} </employee>
>     	<manager> {$m.name} </manager>
>     </employee_manager>
>     }
>     </employee_managers>

In both of these examples, given the sources, there is no way to write these queries without replicating the sources.

When is source replication necessary?

Source replication is necessary whenever you want to use a source for two different purposes that will require iterating over the source twice. Another way to state it is when two different tuples from a source will be required at the same time.

When should you manually replicate sources?

In ambiguous cases, both replicating and not replicating a source would lead to reasonable queries.

For example, at the beginning of this section, we presented a self-join to get employee-manager pairs. Without replicating the source, you might try the following:

1. Map name to the target (get the employee name)

2. Join manager_id with id (join to get the manager)

3. Map name to the target (get the manager name)

Of course, the Data View Builder would interpret this query as: "give me all employees who are their own manager." This interpretation is no less valid than the desired one.

There is no way to go into Advanced mode to fix this query. You simply must replicate the source in this case.

Next Steps

- If you are ready to jump in and start designing more complex queries, refer to the advanced example queries in Query Cookbook.

- For detailed information on how to run a query, see Testing Queries.

- For information on how to optimize a query for better performance, see Optimizing Queries.