Skip navigation.
Downloads | Product Documentation | Support

Data Services Developer's Guide

# Using Query Editor View

Liquid Data services provide a framework for creation and maintenance of functions that access and transform available data. You can use the Liquid Data XQuery Editor to create such functions.

A valid query function is always associated with a return type. In Source View a return type is described for each function. It typically matches the XML type — or schema — that defines the shape of your data service.

Once created, your query functions can be called by client applications. Details on the various methods of invoking Liquid Data functions can be found in the Client Application Developer's Guide.

You can also use the XQuery Editor to create standalone, ad hoc queries that can be run in Test View (see Testing Query Functions and Viewing Query Plans).

Topics discussed in this chapter include:

- Purpose of the XQuery Editor
- Key Concepts of Query Function Building
- Managing Query Components
- Working With Data Representations and Return Type Elements

# Purpose of the XQuery Editor

Using the XQuery Editor you can create query functions using an intuitive, drag-and-drop approach. During the creation process you can easily move back and forth between the editor to Source View.

Figure 6-1 Sample Parameterized Function in the XQuery Editor

The XQuery Editor relies on data services functions for the metadata necessary to represent various types of data. (For detailed information on importing metadata see Obtaining Enterprise Metadata.)

A data service may represent a physical data source or it may represent logical data that has previously been created. Data service and custom XQuery library functions are both represented from the Liquid Data Palette (Figure 6-2), a Workshop pane available when XQuery Editor View is active.

Figure 6-2 Data Service Functions Available from the Liquid Data Palette

Notice in Figure 6-2 that there are two different type of function representations: Functions represent by a straight (green) arrow are read functions, while functions represented by a more stylized (blue) arrow are navigation functions.

Essentially you create a query function by:

- Dragging in data representations from the Liquid Data Palette to the XQuery Editor work area.
- Identifying conditions, parameters, functions, and expressions that for your query.
- Associating elements with a return type.

As you work graphically you are automatically creating an XQuery in Source View.

Once created, you can execute your function using Test View (see Testing Query Functions and Viewing Query Plans). When you execute your query function, underlying data sources are accessed and the results appear. If you have appropriate permissions, data can be updated directly after the query is run.

## Data Source Representations

Metadata representations of source are available to the XQuery Editor from the Liquid Data Palette. The Liquid Data Palette lists available data services and their read and relationship functions. Any such function can be dragged into the XQuery Editor work area where it will be transformed into a for clause.

Figure 6-3 Example of the Liquid Data Palette

Read functions and web services often have input parameters. For example, the logical data service Customer (`customer.ds`) can be represented in the XQuery Editor by its read functions: getCustomer() and getPaymentList(). If you drag the getCustomer() item from the Liquid Data Palette to the XQuery Editor, the source representation shown in Figure 6-4 appears in the work area.

Figure 6-4 Data Service Function From the Liquid Data Palette

In some cases you may want to use a physical or logical data source representation several times in a query.

See Obtaining Enterprise Metadata and Using Data Services Design View for details on creating physical and logical data services.

## XQuery Editor Options

When you create a new function in your data service and then click on the name of your new function (Figure 6-1), you will automatically be placed in the XQuery Editor. Alternatively, click the XQuery Editor View tab and select your function from the drop-down menu. Initially your XQuery will have only a return type, assuming that your data service is associated with an XML type (see Associating an XML Type).

Figure 6-5 Right-click Menu Options in the XQuery Editor

Several right-click menu options are available when you click in any unoccupied part of the work area.

Option | Meaning
--- | ---
Edit in Source View | Opens Source View to the section containing the currently selected function.
Default layout | The elements in the XQuery Editor are rearranged according to a pre-established formula including docking the return type to the right side of the work area.
Add Parameter | Adds a simple or complex parameter to your work area. Complex parameters require you to select a schema file and global type. See Parameter Nodes.
Show Condition Lines | Hides/displays lines that identify conditions such as where clause predicates. By default condition lines are shown.
Remove Selected Item(s) | Deletes selected items from the work area.
Collapse All | Collapses all nodes in the work area including the return type.
Expand All | Expands all nodes in the work area.

# Creating a New Data Service and an XQuery Function

Creating a data service — as you will if you follow the steps in this section — is a good way to get the feel of what it is like to work with the XQuery Editor, as well as other aspects of the data service creating system provided with Liquid Data.

Through Source View you can quickly see how changes in the XQuery Editor are translated into XQuery code. Similarly any changes you make in Source View will be immediately reflected in the XQuery Editor work area. (See Using Source View.)

Note: There are a number of examples of query building in the XQuery Editor and Source View in the Liquid Data Samples Tutorial.

To access the tutorial see the Liquid Data edocs page:

>     http://download.oracle.com/docs/cd/E13190_01/liquiddata/docs85/index.html

#### The Goal

The goal is to quickly create a logical data service from scratch, including creating an XML type for your data service, using the XQuery Editor. In this example you create a logical data service by first building up a return type from several physical data services and then making that the type of your data service.

Note: The easiest way to change something you have done in the XQuery Editor is to use the Edit —> Undo command (or Ctrl-Z). Since before saving your application you will be able to undo any number of previous steps, it is often preferable to use Undo rather than redrawing mappings, zone settings, or conditions, since these actions all modify the underlying source.

#### Setting Up Your Application

Using Liquid Data sample data, the following steps illustrate one way to create a logical data service, including a return type.

Create a new Liquid Data application, called myLogical.

#### Importing Your Metadata

1. Right-click on the automatically-created project titled myLogicalDataServices.
2. Select Import Source Metadata. (If your WebLogic Server is not already running you will need to start it.)
3. Select Relational as the data source type.
4. Select all objects in cgDataSource from the drop-down list of available relational data sources.
5. Select the CUSTOMER table from the RTLCUSTOMER database.
6. Click through the remaining options in the wizard to created these a new data services.
7. Repeat steps 5 and 6 for CUSTOMER_ORDER table in the RTLAPPLOMS database.

#### Creating Your Data Service

1. Right-click again on the myLogicalDataServices project and choose New —> Data Service.
2. Name the data service myLogicalDS, then click Create. At this point your data service has no XML type.
3. Click on the titlebar of your new data service; select Add function. Name your new function CustOrder. Enter the XQuery Editor by clicking on the newly assigned name.

#### Creating Your Return Type

1. From the Liquid Data palette drag your CUSTOMER() and CUSTOMER_ORDER() functions into the XQuery Editor work area (Figure 6-6).

Figure 6-6 XQuery Editor With Two Data Sources and an Empty Return Type

The existence of the two unmapped for clauses, $CUSTOMER and $CUSTOMER_ORDER is accounted for by the return type's list of two empty elements.

Next you need to populate the return type. In this case CUSTOMER_ORDER should be set up as a child of the CUSTOMER complex element so that information will be return in the following shape:

>     Customer1
>     		..
>      		Order1
>     		..
>     		 Order2
>     		..
>     Customer2
>     		..

2. Holding down the Ctrl key map the CUSTOMER* element in the CUSTOMER for node to the topmost empty element in the return type.

Figure 6-7 Return Type After An Induced Mapping of the Customer For Node

3. In your return type right-click on the new CUSTOMER root element and select Expand Complex Mapping. This maps all the elements in your CUSTOMER node to corresponding elements in your return type.

As this report also must list each customer's orders, you will need to create a second for clause. One way to do this is to simply add the CUSTOMER_ORDER type as a subordinate to CUSTOMER, as shown in the next step.

4. Holding down Shift+Ctrl keys select the root element in the $CUSTOMER_ORDER for node and drag it over the CUSTOMER root element in your return type.

Figure 6-8 Append Mapping of the $CUSTOMER_ORDER to the Return Type

The CUSTOMER_ORDER elements will appear as subordinate to CUSTOMER.

Figure 6-9 Subordinate Node Added to the Return Type

If you try to run a query at this point it will fail for several reasons:

- Your data service has no associated XML type (schema).
- Your project (or application) needs to be build to create the proper SDO infrastructure.
- No where clause connecting the customer ID primary keys has been created.
- The master-detail structure of the report has not been created.

These issues will be resolved through the steps that follow.

5. In your CUSTOMER_ORDER node select the zone icon (see Figure 6-10) and drag it over the CUSTOMER_ORDER element in your return type. This will create an inner zone in your return type which in source translates into an inner for clause for your query.

Figure 6-10 Creating a Zone Supporting CUSTOMER_ORDER

6. Create a join between your two data source representations by dragging the CUSTOMER_ID element in the CUSTOMER node to the C_ID element in the CUSTOMER_ORDER node. A green line connecting the two elements will appear.

#### Creating Your Data Service's XML Type, Building Your Application, and Running Your Query

The default name for your new schema matches the name of your data service; the default namespace is actually the qualified name of the root element of your return type.

Note: Since the proposed qualified namespace of your new XML Type is identical to the qualified name of your CUSTOMER data service, a type conflict will occur if you try to complete this operation. The solution is to modify either the namespace or the root name.

1. Click on the titlebar of your return type and select Save and Associate XML Type. In order to complete this operation you need to provide a name and location for the underlying schema file, a namespace, and a name for the root element in your return type. As shown in Figure 6-11, in each case a default setting is provided.

Figure 6-11 Save and Associate XML Type Dialog

2. Change the root name from CUSTOMER to CUST_ORDER_DETAIL. Then click OK. This will also change the root name of your return type and complete the association.
3. Build your application.
4. Execute your query through Test View. Results should show customer orders nested for each customer. (See partial results in Figure 6-12.)

Figure 6-12 Test Results

Although there are several ways to go about accomplishing the same task, it is also important to be aware that there were points along the way where an effort to build or deploy your application would not have been successful because the query or the return type was not fully formed. Thus the order in which steps are accomplished is often important.

# Key Concepts of Query Function Building

The following terms and concepts are introduced in this section:

- Data Sources
- Source Schemas and Return Types
- XQuery Editor Components
- The Distinct By node represents a single distinct by clause.
- Setting Expressions
- Mapping to Return Types
- Modifying a Return Type

## Data Sources

Liquid Data supports multiple data sources including:

- RDBMS (relational database management systems)
- Web services
- Java functions
- Delimited files (such as spreadsheets)
- XML documents

For details on importing data source metadata from these sources into Liquid Data-based projects see Obtaining Enterprise Metadata.

## Source Schemas and Return Types

The XQuery Editor uses XML schema representations as:

- XML type. An XML schema that describes the structure of a physical or logical data source.
- Return type. The return type of a function. In the XQuery Editor the return type contains information necessary to support customized queries in terms of the ordering of information returned from the query.

For more information see XML Types and Return Types.

## XQuery Editor Components

Using the XQuery Editor, query functions can be built-up graphically using a combination of graphical gestures and functions, including:

- Standard XQuery for and let clauses
- XQuery constructs such as where and order by
- XQuery extensions such as group by and if-then-else
- Standard and user-defined XQuery functions
- Physical and logical data source references

The following topics describe XQuery clauses as rendered in the Liquid Data XQuery Editor. (For information on the XQuery engine used by Liquid Data and specific uses of XQuery in Source View see the Liquid Data XQuery Developer's Guide. This document also contains references to the most up-to-state XQuery W3C specifications.)

### Return Type Node

Query functions always map to a single return type. If your data service is associated with a return type, that type will appear in the Return node.

Figure 6-13 Sample Return Type

The return type can be thought of as extending the XML type to in support of:

- If-then-else constructs using the right-mouse click Conditional operation.
- Zones (see Setting Zones in Your Return Type).

When you click on a simple element in the return type, the expression on that element's constructor appears.

### For Clause Nodes

A for clause node represents a named XQuery for clause construct. For and let clause nodes are always based data service functions.

Figure 6-14 Sample For Statement Node

By default, whenever you add a data service to the XQuery Editor work area, it is represented in a for node. The for node typically represents looping over a query function using either:

- a variable reference
- an expression

#### Parameterized Input

A for node, representing a parameterized query function, provides both Input and Output sections. As you would expect, parameters are mapped to the Input elements while Output elements either serve as input to other nodes or to the return type.

Figure 6-15 Example of Parameterized For Node

Several options are available when you right-click on the title of a `for` node.

Option | Meaning
--- | ---
Rename | Brings up a dialog which allows you to rename your node. Names cannot contain spaces.
Delete | Removes the node and any mappings in or out of the node from the work area.
Convert to let Clause | Changes the clause from a for to a let. This operation is reversible.
Relationship Functions | Relationship functions associated with the data service are listed. Selecting a relationship function allows your for or let node to serve as input for the relationship. See Adding Relationship Functions to Existing Data Service for an illustration and code sample.
View Source | Shows the source underlying the currently selected node.

### Converting Between For and Let Clauses

For and let clauses (see Let Statement Nodes) have many interchangeable characteristics.

The following code shows the conversion of the DataServices/RTLServices/Case/getCaseByCustID() function expression from a for clause:

>     declare function ns1:getCaseByCustID($cust_id as xs:string) as element(ns0:CASE)* {
>     		for $x0 in ns1:getCase()
>     		where $cust_id eq $x0/CustomerID
>     return $x0
>     };

to a let clause:

>     declare function ns1:getCaseByCustID($cust_id as xs:string) as element(ns0:CASE)* {
>     let $x0 := ns1:getCase()
>     		where $cust_id eq $x0/CustomerID
>     return $x0
>     };

### Let Statement Nodes

A let clause binds a sequence of elements (graphically contained in a node) to a variable that in turn becomes available to the FLWR expression.

Options available for use with for clauses are also available for let clauses.

Figure 6-16 Let Statement in the RTLServices/OrderSummaryView Data Service

When examining a let clause, you can read the assign string (:=) as the "be bound to". For example, in the following let clause:

>     let $x := (1, 2, 3)

Can be read as "let the variable named x be bound to the sequence containing the items 1, 2, and 3."

See also Converting Between For and Let Clauses.

### Parameter Nodes

Parameter nodes enable you to associate a parameter with a for or let clause. Parameter nodes are created in the XQuery Editor work area (Figure 6-5). Three right-click menu options are available: Rename, Delete, and View Source.

Figure 6-17 XQuery Editor Add Parameter Dialog Box

You can create parameters that range from simple data elements to elements of any complexity.

#### Adding a Parameter Requiring a Simple Type

You can create a simple type parameter by selecting the type from the drop-down list and clicking Ok.

Figure 6-18 Setting a Simple Parameter Types

The act of mapping a parameter to a for or a let node containing an Input creates a parameterized query and also establishes a where condition. In Figure 6-19 the customer_id string parameter is dragged over the element in the ADDRESS node which is to be associated with the parameter through a where clause.

Figure 6-19 Parameter Mapped to a For Node

The corresponding Source View code highlights the parameter:

>     declare function ns5:getCaseView($custID as xs:string) as element(ns6:CaseView) {
>     <ns6:CaseView>
>     {
>     		<CASE_VIEW>
>     			<CASES>{
>     				for $Case in ns7:getCaseByCustID($custID)
>     					return <CASE>
>     						<CaseID> {fn:data($Case/CaseID)} </CaseID>
>     						<CustomerID>{fn:data($Case/CustomerID)}</CustomerID>
>     						<CaseType> {fn:data($Case/CaseType)} </CaseType>
>     						<ProductID> {fn:data($Case/ProductID)} </ProductID>
>     						<Status> {fn:data($Case/Status)} </Status>
>     						<StatusDate> {fn:data($Case/StatusDate)} </StatusDate>
>     						</CASE>
>     						}
>     			</CASES>
>     		</CASE_VIEW>
>     		}
>     </ns6:CaseView>
>     };

When you invoke your function from an application — or execute your function in Test View — you will supply a value for your parameter.

#### Adding a Complex Parameter

Complex parameters are established by identifying a schema and a global element. Some schemas have only one global element.

Figure 6-20 Setting a Complex Parameter Type

The resulting parameter can be associated with any for or let node. See also Parameterized Input.

#### Using the Parameter Dialog to Create a WHERE Clause

You can use the parameter dialog to create a where clause condition simply by dragging the simple or complex parameter over an element in a for or let clause. In Figure 6-21 the newly created parameter productID is mapped to PRODUCT_ID. Since the $PRODUCT for node is selected, the where clause is in scope.

Figure 6-21 Parameterized Where Clause

### Adding Relationship Functions to Existing Data Service

There are several ways to add relationship functions to existing data services. The recommended way is to use the right-click menu option available from for and let nodes, since this will created more appropriately nested clauses than simply dragging a relationship function from the Liquid Data palette into the work area.

For example, if you want to create a logical data service that was a union of customer order and order line items, you could start with a customer order and add the related line item data.

Figure 6-22 takes the RTLApp DataServices/ApparelDB/CUSTOMER_ORDER() function and shows the process of adding the related getCUSTOMER_ORDER_LINE_ITEM() function.

Figure 6-22 Adding a Relationship Function

The function initially appears as:

>     declare function tns:newFunction() as element(ns30:CUSTOMER_ORDER9)* {
>       for $CUSTOMER_ORDER in ns28:CUSTOMER_ORDER()
>         return $CUSTOMER_ORDER
>     };

Adding the relationship function changes it to:

`declare function tns:newFunction() as element(ns30:CUSTOMER_ORDER9)* {
for $CUSTOMER_ORDER in ns28:CUSTOMER_ORDER()
for $CUSTOMER_ORDER_LINE_ITEM in ns28:getCUSTOMER_ORDER_LINE_ITEM($CUSTOMER_ORDER)
return $CUSTOMER_ORDER
}`;

To complete this simple example you would need to add elements from the related data service to your return type and complete your mappings, as well as any transformations.

### Group By Statement Nodes

The Group By node represents a single group by clause with zero or more grouping expressions. The top part of the Group By node defines variables available to the generated group by expression. The bottom part defines the grouping expression itself.

Group By expressions are often used with aggregation functions such as grouping customers by total sales. A for or let clause supports multiple group by elements.

You can generate a Group By node by right-clicking on any element in a for or let node and selecting Create Group By from the right-click menu.

Figure 6-23 Creating a Group By Expression

In Figure 6-23 output will be grouped by the C_ID (customer ID) element. Once a GroupBy node is created mappings to target objects — such as the return type — are done through the new node.

Figure 6-24 Projecting Total Orders Grouped by Customer ID

The default name of the group by node will be a unique name based on the local name of the for/let node. Thus the CUSTOMER_ORDER for clause becomes the basis for CUSTOMER_ORDER_group0. Group By nodes cannot be renamed from the XQuery Editor.

As seen in Figure 6-24, any node mappings are automatically transferred to the Group By node.

The resulting source is:

>     declare function tns:getCustomerOrderAmount() as element(ns5:CUSTOMER_ORDER)* {
>         for $CUSTOMER_ORDER in ns6:CUSTOMER_ORDER()
>     group $CUSTOMER_ORDER as $CUSTOMER_ORDER_group by $CUSTOMER_ORDER/C_ID as $C_ID_group

>     return
>     <ns5:CUSTOMER_ORDER>
>             <ORDER_ID></ORDER_ID>
>             <C_ID>{fn:data($C_ID_group)}</C_ID>
>             <ORDER_DT></ORDER_DT>
>             <SHIP_METHOD_DSC></SHIP_METHOD_DSC>
>             <HANDLING_CHRG_AMT></HANDLING_CHRG_AMT>
>             <SUBTOTAL_AMT></SUBTOTAL_AMT>
>             <TOTAL_ORDER_AMT>{fn:sum($CUSTOMER_ORDER_group/TOTAL_ORDER_AMT)}</TOTAL_ORDER_AMT>
>             <SALE_TAX_AMT></SALE_TAX_AMT>
>             <SHIP_TO_ID></SHIP_TO_ID>
>             <SHIP_TO_NM></SHIP_TO_NM>
>             <BILL_TO_ID></BILL_TO_ID>
>             <ESTIMATED_SHIP_DT></ESTIMATED_SHIP_DT>
>             <STATUS></STATUS>
>             <TRACKING_NO?></TRACKING_NO>
>         </ns5:CUSTOMER_ORDER>
>     };

If you delete a Group By node any mappings from the parent node will need to be redrawn.

#### Creating Multiple Group By Elements

In the example you can add additional grouping expressions simply by dragging new elements over the "By" separator, (Figure 6-25).

Figure 6-25 Adding a Second Group By Element

The act of dragging the element over an existing group by expression, adds a second group by expression, as shown in Figure 6-26.

Figure 6-26 The New Group By Expression Element

The effect of adding the second group by in the above example is to group total orders by their status value.

>     <ORDER_ID/>
>           <C_ID>CUSTOMER0</C_ID>
>           <TOTAL_ORDER_AMT>1173.2</TOTAL_ORDER_AMT>
>           <STATUS>CLOSED</STATUS>
>        </ns0:CUSTOMER_ORDER5>
>        <ns0:CUSTOMER_ORDER5 xmlns:ns0="ld:DataServices/ApparelDB/CUSTOMER_ORDER5">
>           <ORDER_ID/>
>           <C_ID>CUSTOMER0</C_ID>
>           <TOTAL_ORDER_AMT>436.3</TOTAL_ORDER_AMT>
>           <STATUS>OPEN</STATUS>
>        </ns0:CUSTOMER_ORDER5>
>        <ns0:CUSTOMER_ORDER5 xmlns:ns0="ld:DataServices/ApparelDB/CUSTOMER_ORDER5">
>           <ORDER_ID/>

#### Using Multiple Group Nodes

You can create additional multiple Group By expressions to enable creation of logic such as:

Group by A, then
Group by B

In order to do this you need to introduce an additional for or let clause to establish the parent-child structure that will support the needed logic.

To creating a second-level group by:

1. Creating a child for clause. Right-click on the root element in your primary group by node and select Create For Clause.
2. Create a new zone (see Setting Zones in Your Return Type) in your return type. There are several ways to do this. One is to Add a Child Element.
3. Right-click on the new child element and select Mark as Zone.
4. Set the zone of your new for clause to the new child element. Do this by dragging the zone symbol over your new child element. You will know you have succeeded when the newChildElement displays an array symbol.

`NewChildElement * empty`

And, when you mouse over the new child element, it will be highlighted, indicating that it is a zone unto itself.

5. Create a group by right-clicking on the grouping element in the new for clause node.
6. Replace the new child element by dragging the group by element over the new child element while holding down the control key. This effectively overwrites the element with the group by expression.
7. Save and associate your new return type so that it become the XML type of your data service.

You can use Test View to verify your work.

### Distinct By Statement Nodes

The Distinct By node represents a single distinct by clause.

Distinct by is useful:

- When you what to return all distinct values for a particular element.
- When you want to perform functional operations on the result of a distinct by, such as the total number of distinct elements.

## Setting Conditions

Several types of conditions can be graphically applied to for and let clauses. You can create these conditions using a multi-function editor that appears at the bottom of XQuery Editor work area. (Figure 6-27).

Figure 6-27 Multi-function Condition Editor

To add or modify constraints for a for or let node first select the node, then click anywhere in the multi-function editor. Everything but your selected expression will become unavailable, as indicated by the "grayed out" appearance of unselected objects.

Condition types are:

- where
- group by

Figure 6-28 provides a closer look at the multi-function dialog which includes the ability to:

- Add any number of where or order by conditions.
- Edit a condition using the built-in line editor.
- Adjust the order in which the conditions are applied.
- Select XQuery operators from a drop-down list.
- Accept or cancel editing changes to a particular condition.
- Delete a where or order by clause.

Functions from the XQuery Function Palatte can be dragged into the multifunction box and then edited.

Figure 6-28 Detail of Multifunction Box

### Using XQuery Functions

Liquid Data contains a full set of built-in XQuery functions. Most XQuery functions in the XQuery Function Palatte are standard XQuery functions supported by the W3C. However, there are several BEA-specific functions as well as several extensions to the language. (For details on the BEA implementation of the 1.0 XQuery engine see XQuery Developer's Guide. For more detailed information on standard XQuery functions, see the W3C XQuery 1.0 and XPath 2.0 Functions and Operators specification.)

The functions available from the XQuery Functions palette are used to create conditions around for and let clauses. For example, if you wanted to establish a condition related to which customers a query would return you would follow these steps:

1. In the Data Services project create a new data service and new functions using any name. Click on the function name to enter the XQuery Editor.
2. From the RTLApp application select the CustomerDB/Customer data service from the Liquid Data Palatte; drag it into the work area.
3. Replace the empty return type with the node elements by dragging CUSTOMER over the empty element in the return type.
4. Expand complex mapping (or map to each individual element).
5. Save and associate your new return type to your XML type.
6. Click on the LastName element.
7. Click the Where icon (see The Where Clause) to create a new where condition.
8. Open the XQuery Function palette (if it is not already showing).
9. Drag the `fn:string-length() as xs:integer` function from the XQuery Palette into the Where condition.

Figure 6-29 XQuery Palette

10. Using the built-in line editor drag PO_ITEMS/ORDERID into the function so that it appears as:

>     Where fn:string-length($CUSTOMER/LAST_NAME)

11. Add `<5` as the predicate so the string appears as:

>     Where fn:string-length($i/ORDERID)<5

12. Press Return, then click the checkmark.

Figure 6-30 Editing an XQuery Function

If you mouse over the title of your for clause, you can see that the condition has been associated with the fragment. You can also verify this change in source view.

Figure 6-31 Mouse-over of Node Title Displays Its Conditions

When you run the function only records with LAST_NAMEs shorter than five characters will appear.

Automatic type casting generally ensures that input parameters used in functions and mappings are appropriate to the function in which they are used.

### The Where Clause

The `where` clause places a condition on a for and/or let clause. A where clause can be any query expression, including another FLWR expression. The where clause typically filters the number of matches in a FLWR loop.

A common use of the where clause is to specify a join between two sources. For example, consider the following query:

>     <results>
>     {
>     for $x in (1, 2, 3), $y in (2, 3, 4)
>     where $x eq $y
>     return
>          <matches>{$x}</matches>
>     }
>     </results>

The where clause in this query filters (or joins or constraints) the results that match two sequences specified in the for clause. In this case, the numbers 2 and 3 match, and the query returns the following results:

>     <results>
>          <matches>2</matches>
>          <matches>3</matches>
>     </results>

To effect this in the XQuery Editor you would select the for or let clause to which the where condition applies. Then, in the where condition field, you enter:

>     $x eq $y

You can type in the name of an element or drag it from the a node in the work area into the Condition Editor. The eq XQuery operator can be entered directly or selected from the conditional pop-up list.

Figure 6-32 Conditional Operator Selection List

See also Using the Parameter Dialog to Create a WHERE Clause.

### The Order By Clause

The order by clause indicates output order for a given set of data.

Unless otherwise specified, the order data appears will follow the XML tree. This is known as the document order. The order by keyword indicates that the content should be sorted in ascending order by the identified element(s).

XQuery keywords such as descending are supported. For example, an XQuery can be written that orders the customers by last name in descending order:

>     for $customer in document('customers.xml')//customer
>     	order by last_name descending
>     	return
>     		<customer>
>     			{$customer/first_name}
>     			{$customer/last_name}
>     		</customer>
>     ...

In the XQuery Editor you would select the for or let clause to which the order by condition applies and in the order by condition field enter:

>     last_name descending

You can type in the name of an element or drag it from the work area into the Condition Editor Figure 6-28.

### Creating Join Conditions

Join conditions are represented as equality relationships in where clauses. Therefore you can create such an equality relationship by dragging and dropping the eq function onto a row in the Conditions tab and then selecting two source elements/attributes into the same row.

## Setting Expressions

The Expression editor is most commonly used to edit return type expressions. For example, if the return type contains:

>     ORDERID xsd:int

The Expression Editor could be used to limit the scope of the expression to a single customer:

>     Expression>{fn:data($o/ORDERID)} eq "1001"

Prototypes of functions available from the XQuery Function Palatte can be dragged into the Expression editor or you can use the build-in line editor to enter them yourself.

Figure 6-33 Expression Editor

Operation of the Expression editor is similar to that for the multifunction box. When you select a element other workspace artifacts are grayed out. However, you can drag elements from any part of the work area into the Expression Editor.

XQuery operators are available as a drop down list, as shown in Figure 6-32, or you can simply type them in.

# Managing Query Components

If you think of selected data elements as nouns (what you want to work on), the functions as verbs (the action), then the mapping among the data elements creates a logical sentence that expresses the query.

Results and query performance can change significantly depending on how you:

- Map (or project) source data from one or more sources to the return type.
- Specify zones and other conditions (filter source data) and expressions (element level operations).

Although you can simply type in an XQuery and run it from Test View, the more common way to create a query is build it up through the following operations:

- Map simple or complex elements to the return type
- Define query parameters
- Transform information using built-in or custom functions
- Filter data using where or order by conditions
- Adjust expressions as required by business requirements

Note: Some operations are not deterministic. For example if a node has elements mapped to a return type, deleting the node before removing the mappings may create error conditions. In Instead you can use Undo and then delete the mappings or your can make the necessary changes in Source View.

# Working With Data Representations and Return Type Elements

Mapping elements involves establishing a visual relationship between data source elements and the return type or an intermediary node requiring input parameters.

There are two types of schema elements: simple and complex. Complex elements contain elements and/or attributes.

Figure 6-34 Expanded Schema Showing Complex and Simple Elements

To expand a complex element, click on the plus sign (+) to the left of its name. (If you double click on the name itself, you will enter edit mode.)

## Mapping to Return Types

As shown in Creating a New Data Service and an XQuery Function, the XQuery Editor automatically generates queries based on graphical mappings into a return type.

The XQuery Editor supports two types of mappings: value mappings and complex element mappings. Value mappings map (assign) only the value of an element or attribute from a source to the value of its target element or attribute. Element mappings allow mapping source elements (simple or complex) to target.

(For additional background information see XML Types and Return Types and Editing XML Types and Return Types.)

### Mapping Elements and Attributes to the Type

A questionmark symbol [`?`] next to an element name represents an optional element, meaning that it is not required by the query. Primary keys are never optional.

### Complex Element Mappings to a Return Type

You can rapidly map complex elements from source to your return type. This known as an induced mapping is useful where all or part of the return type should match source representations.

There are many situations when you will find it convenient to map elements into your type, including:

- When you are creating a type from scratch.
- When you want elements individually mapped but it is easier to map complex elements, expand the mappings to include values, and then add or delete some mappings using right-click return type management commands.

If the match is not exact, mapping a complex element to your return type will be appended.

There are several benefits of mapping or projecting elements:

- Manual one-to-one mapping of multiple elements is less often needed.
- The query is often easier to read.
- If the underlying structure of the complex element changes — an element is added, deleted, or an attribute is changed — the generated query does not change.

Figure 6-35 shows the results of the mapping of a complex element to a return type.

Figure 6-35 Example of Mapping of a Complex Element

Note: You cannot map multiple elements to a single target element.

#### Source-to-Target Mapping Options

Three types of source-to-return type mappings are available:

- Simple mappings. Individual source node elements are individually mapped to simple elements or attributes in the return type. You can create a simple mapping by dragging and dropping elements from the source node to a corresponding target element in the return type. All elements do not need to be mapped, depending on the information you want in the XML document generated by your query function. However, special attention should be paid to non-optional elements (those without adjacent question-marks (?)), since your query will fail if non-optional elements are not projected.
- Induced mappings. You must hold down the Ctrl key when doing an induced mapping. An induced mapping means that a complex element is mapped to a comparable (exactly named) element in the return type. The XQuery Editor calculates the matching elements (name and type) and maps that complex element. For example, you create an induced map when you drag and drop the CREDIT_CARD* element (root element in a source node) onto the CREDIT_CARD complex element in the return type.

The following code expresses the results of an induced mapping:

>     declare function tns:newFunction() as element(ns5:CREDIT_CARD)* {
>         for $CREDIT_CARD in tns:getCreditCard()
>     return
>     $CREDIT_CARD

In many cases an induced mapping is insufficient either for further building your query function or running it. You can always expand complex element mappings by right-clicking on the element and selecting the only available option: Expand Complex Elements. In the above case the source would be correspondingly modified:

>     declare function tns:newFunction() as element(ns5:CREDIT_CARD)* {
>         for $CREDIT_CARD in tns:getCreditCard()
>     return
>     <ns5:CREDIT_CARD>
>         <CreditCardID>{fn:data($CREDIT_CARD/CreditCardID)}</CreditCardID>
>         <CustomerID>{fn:data($CREDIT_CARD/CustomerID)}</CustomerID>
>         <CustomerName>{fn:data($CREDIT_CARD/CustomerName)}</CustomerName>
>         <CreditCardType>{fn:data($CREDIT_CARD/CreditCardType)}</CreditCardType>
>         <CreditCardBrand>{fn:data($CREDIT_CARD/CreditCardBrand)}</CreditCardBrand>
>         <CreditCardNumber>{fn:data($CREDIT_CARD/CreditCardNumber)}</CreditCardNumber>
>         <LastDigits>{fn:data($CREDIT_CARD/LastDigits)}</LastDigits>
>         <ExpirationDate>{fn:data($CREDIT_CARD/ExpirationDate)}</ExpirationDate>
>         <Status?>{fn:data($CREDIT_CARD/Status)}</Status>
>         <Alias?>{fn:data($CREDIT_CARD/Alias)}</Alias>
>         <AddressID>{fn:data($CREDIT_CARD/AddressID)}</AddressID>
>     </ns5:CREDIT_CARD>
>     };

Figure 6-36 Expanded Complex Element

- Append mappings. You can append a source element and children (if any) to an element in a return type using the key combination of Ctrl+Shift. Click the source element, press the key combination of Ctrl+Shift and drag the element over an element in the return type. If the underlying element is highlighted, you can add the source as its child.

Note: Any changes you make to a return type should be propagated to your data services XML type using the Save and Associate XML Type right-mouse option, available from the return type titlebar.

### Removing Mappings

You can delete mappings between elements by selecting the mapping line (link) and pressing Delete. Alternatively, use the Delete key.

## Modifying a Return Type

The shape of the information returned by your query is determined by its return type. Using a combination of mapping techniques and return type options you can:

- Add or remove elements and attributes from your return type.
- Set up repeatable sections, known as zones.

You should only modify a return type if you intend to propagate the change to the data service's XML type using the Save and Associate XML Type command, described in Creating a New Data Service and an XQuery Function.

### Modifying a Return Type

You can edit your return type by right-clicking any element. Editing options for a type in the XQuery Editor are somewhat different options described in Editing an XML Type. For example, in a return type you can create zones automatically add for clauses to your query, allowing for a "master-detail" arrangement of results.

Warning: While it is possible to modify a return type and run a query in an ad hoc manner, problems will likely arise when your application calls a query with a mismatch between the return clause and the XML type of the data service.

Table 6-37 describes notable return type editing options.

Table 6-37 Notable Return Type Options

Option | Meaning
--- | ---
Add Child Element | Creates a child element for the currently selected element.
Add Complex Child Element | Allows you to specify a schema and type for a new complex child element. By default, the type is the root element of the schema. If the schema has several global elements, however, you will first need to specify the element that you want to become the root.
Add Attribute | Creates an attribute for the selected element.
Make Conditional | Inserts an element named Conditional above the currently selected element and clones the element (and children, if any). Conditional elements can be used in conjunction with if-then-else constructs. Transformational logic can then be developed through the XQuery Editor and mapped to the appropriate branch of the condition.
Clone | Duplicates the selected element (and children, if any) to the same level of the schema hierarchy. If you clone a simple element an unmapped, untyped element of the same name will be created.
Mark as Zone / Remove Zone | Sets (or removes) a zone setting for the current element and its children (if any). If the elements are in a zone the query will return them in a master-detail arrangement. See Setting Zones in Your Return Type.
Delete | Deletes the selected element and any child elements or attributes.
Find | The Find dialog allows you to search for text strings in the return type with options to match case, match whole words only, use wildcards (`*,?`), or regular expressions.

A special option, Expand Complex Mappings, is becomes available for use with Induced mappings. See Complex Element Mappings to a Return Type for details.

There are several things to keep in mind when making changes to a return type:

- Changes to a return type should be propagated the your data service's XML type.
- Changes to a return type through Liquid Data components exist only in memory until you run the File —> Save All command in Workshop.
- Changes to a file using the Save All command cannot be reversed through Undo.

### Adding a Complex Child Element

You can add a complex child element to a return type by selecting a schema and identifying a global element (a type). Complex child elements incorporate data service schemas (`.xsd` file) into the return type.

To add a complex global element to your return type:

1. Click on the element you want to be the parent of the complex element.
2. Right-click and select Add Complex Child Element.
3. In the dialog that appears navigate to the schema you want to use. If your schema only has one global element, then it will be automatically selected. Otherwise, you will need to pick which global element to use.

When you add a complex child element it will be place at the end of its peers in the return type.

### Setting Zones in Your Return Type

In Liquid Data return types zones identify how query results will be arranged. Introducing zones using the XQuery Editor is the same as adding a subordinate for clause to your query.

For example in Figure 6-38 the CUSTOMER_ORDER elements for a particular customer will be grouped under that customer.

Figure 6-38 Sample Return Type With Two Zones

By default, return types have only a single zone. However, without additional zones elements simply repeat in their natural order. In the simple example shown in Figure 6-38 this would mean that if a customer had more than one order, both the customer information and the order information would be repeated it your report until all matching orders had appeared.

The following slightly simplified XML illustrates a single-zone approach.

>     <CUSTOMERID>987655</CUSTOMERID>
>     <CUSTOMERNAME>Supermart</CUSTOMERNAME>
>     <ORDER>
>     		<ORDERID>632</ORDERID>
>     		<CUSTOMERID>987655</CUSTOMERID>
>     <CUSTOMERID>987655</CUSTOMERID>
>     <CUSTOMERNAME>Supermart</CUSTOMERNAME>
>     <ORDER>
>     		<ORDERID>888</ORDERID>
>     		<CUSTOMERID>987655</CUSTOMERID>
>     ..

Notice the repetition of CUSTOMERNAME and CUSTOMERID.

The XQuery source clearly shows why this is:

>     for $o in f2:PO_CUSTOMERS()
>     where $c/CUSTOMERID eq $o/CUSTOMERID
>     return
>             <t1:CUSTOMER>
>              <CUSTOMERID>{fn:data($c/CUSTOMERID)}</CUSTOMERID>
>               <CUSTOMERNAME>{fn:data($c/CUSTOMERNAME)}</CUSTOMERNAME>
>     							<ORDER>
>                     <ORDERID>{fn:data($o/ORDERID)}</ORDERID>
>                     <CUSTOMERID>{fn:data($o/CUSTOMERID)}</CUSTOMERID>
>                     {
>                     for $i in f3:PO_ITEMS()
>                     where $o/ORDERID eq $i/ORDERID
>                     return
>                         ()
>                     }
>                     </ORDER>

>             </t1:CUSTOMER>

If you were, however, to create a repeatable zone around the ORDER element, a subordinate for clause will be created.

>     where $c/CUSTOMERID eq $arg0
>     return
>     			<t1:CUSTOMER>
>              <CUSTOMERID>{fn:data($c/CUSTOMERID)}</CUSTOMERID>
>               <CUSTOMERNAME>{fn:data($c/CUSTOMERNAME)}</CUSTOMERNAME>

>              {
>             for $o in f2:PO_CUSTOMERS()
>             where $c/CUSTOMERID eq $o/CUSTOMERID
>                 return
>                     <ORDER>
>                     <ORDERID>{fn:data($o/ORDERID)}</ORDERID>
>                     <CUSTOMERID>{fn:data($o/CUSTOMERID)}</CUSTOMERID>
>                     {
>                     for $i in f3:PO_ITEMS()
>                     where $o/ORDERID eq $i/ORDERID
>                     return
>                         ()
>                     }
>                     </ORDER>
>             }
>             </t1:CUSTOMER>

Specifically the highlighted where clause in the second code fragment mandates that all orders be collected under a single instance of customer.

To create a zone simply right-click on an element and select Mark as Zone. Once created, the zone will appear highlighted whenever you move your cursor into areas under its control (Figure 6-38).

#### Associating XQuery Editor Nodes With Zones

In XQuery for, let, and group by clauses can enclose other for, let, or group by clauses. Similarly, nodes representing these constructs can be associated with return type zones using the create zone icon in the titlebar of the node (see Figure 6-10 in the XQuery Editor example at the beginning of this chapter). Simply drag the icon over an existing zone to associate the node with the zone.

To verify that the operation is successful mouse-over the zone icon after the association is complete. If successful, the appropriate zone will be highlighted (see Figure 6-38). Alternatively, look at Source View to verify that your operation has been successful or simply run your query under Test View.

Note: The order in which you create zones and other aspects of your XQuery in the XQuery Editor can be significant. For example, zones should be created before creating a where clause associating two nodes.

#### Removing Zones

To remove a zone, right-click on the parent element in the zone and select the Remove Zone option.

### Validating and Saving Your Return Type

You can make changes in your function's return type and, optimally, bring your data service into conformance with the changes that you have made.

Figure 6-39 Return Type Management Options

Several right-click menu options are available for managing the return type, including:

- Show Type Difference. A toggle that displays or hides distinctions between your return type and the data service XML type. When activated Show Type Difference color coding is used to categorize differences between your return type and your data service's XML type.

  Color | Meaning
  --- | ---
  Black | Unchanged from XML type.
  Red | Removed from the return type (but still present in the XML type).
  Blue | In the return type but not the XML type.

Notice in the following example that two new child elements have been added to the return type.

Elements differences detected when comparing the return type with the content of the XML type are shown in red. This includes elements you have deleted from the return type as well as those you have added to the return type.

The addition of DESCRIPTION to the return type is shown in blue in Figure 6-40.

Figure 6-40 Return Type With a New Element

The arrows to the left of changed items indicate whether the change is originating locally in the return type (—>) or in the data service's XML type ( —>).

- Save and Associate Schema. Provides a means for substituting the schema of a revised return type for the data service XML type. In order to change the data service XML type using this command you should not change the return type name.

Figure 6-41 Save and Associate Dialog

This command can also be used to save the revised return type to a schema, schema location, target Namespace, or root name that is different than that used by the containing data service.

When you are building a return type from data service functions it is sometimes necessary to change either the namespace or the root name prior to using the Save and Associate Schema command. This is because the qualified name of your return type will initially be the same as the function used to create the return type.

Other options include:

- Going to Design View and use the right-click menu Associate XML Type to change the schema associated with the data service (see Associating an XML Type). This will change the return type for all the read functions in your data service.
- Saving your data service to a new name using the Save As command. Then associate the new XML type. This is probably the better option if you have other data services that are dependent on the XML type.

- Dock to Right. A toggle that attaches/detaches the return type to the right edge of the work area.
- View Source. Shows your return type in its native XML format.