In concrete terms, a data service is simply a file that contains XML Query (XQuery) instructions for retrieving, aggregating, and transforming data. Essentially you create a query function by:
Integrating physical and logical data sources into the query.
Mapping data sources to the data service's Return type.
Creating XQuery statements that include conditions, parameters, functions, and expressions.
You can also modify the Return type, either within XQuery Editor View or using an external tool.
In this lesson, you will use XQuery Editor View to develop a variety of XQuery instructions.
Objectives
After completing this lesson, you will be able to:
Use the graphical XQuery Editor View to create parameterized, string, and date functions; outer joins, aggregate, and order by and constant expressions.
Use the XQuery Function Palette to add built-in XQuery functions to a query.
Overview
XQuery Editor View provides a graphical, drag-and-drop approach to constructing queries. Using XQuery Editor View, you can:
View and modify the data service's Return type, whose shape is defined by the data service's XML Type.
View, add, modify, and delete the function calls from other physical and logical data services that define which data source(s) will be queried.
View, add, and delete the source-to-target mappings that define which data will be made available to consuming applications.
View, add, modify, and delete the parameters, expressions, and conditions that define how the data will be processed.
Changes that you make in XQuery Editor View are immediately reflected in Source View. Similarly, changes you make in Source View will be immediately effective in XQuery Editor View.
18.1 Importing Schemas for Query Development
To simplify development time in this lesson you will use ready-made schemas that define a data service's Return type.
Objectives
In this exercise, you will:
Create a folder to organize all the queries that you will create in this lesson and the next.
Import the schemas that you will use in those queries.
Instructions
Create a new folder in the DataServices project folder, and name it MyQueries.
Right-click the MyQueries folder and choose Import.
Navigate to <beahome>\weblogic81\samples\LiquidData\EvalGuide\MyQueries, select the schemas folder, and click Import. This will automatically create a folder named schemas, and appropriate .xsd files, within the MyQueries directory. These .xsd files will be used to determine the Return type for all queries developed in this lesson.
18.2 Creating Source-to-Target Mappings
Every function within a logical data service includes source-to-target mappings that define what results will be returned by the function. As described in Part I, there are several types of mappings:
A simple mapping means that you are mapping simple source node elements to simple elements in the Return type one at a time. You can create a simple mapping by dragging and dropping any element from the source node to its corresponding target element in the Return type. Optional Return type elements do not need to be mapped; otherwise elements in the Return type need to be mapped in order for your query to run.
An induced mapping means that a complex element is mapped to a complex element in the Return type. In this gesture the top level complex element in the Return type is ignored (source node name need not match). The editor automatically then maps any child elements (complex or simple) that are an exact match for source node elements.
An overwrite mapping replaces a Result type element and all its children (if any) with the source node elements. As an example of the general steps needed to create an overwrite mapping, you would press <Ctrl>, then drag and drop the source node's complex element onto the corresponding element in the Result type. The entire source node's complex element is brought to the Result type, where it completely replaces the target element with the source element.
An append mapping adds a simple or complex element (and any children or attributes) as a child of the specified element in the Return type. To create an append mapping, select the source element, then press <Ctrl>+<Shift> while dragging and dropping the source node's element onto the element in the Return type that you want to be the parent of the new element(s).
Alternatively, if you simply want to add a child element to a Return type, you can drag a source element to a complex element in your Return type. The element will be added as a child of the complex element and mapped accordingly.
Objectives
In this exercise, you will:
Create four types of mappings.
Review the results.
Instructions
Right-click the MyQueries folder, choose New Data Service, and use CustomerInfo.ds in the Name field.
In Design View, associate the CustomerInfo data service with the CUSTOMER.xsd schema. The schema is located in MyQueries\schemas.
Add a new function to the CustomerInfo data service and name it getAllCustomers.
Figure 18-1 Design View of CustomerInfo Data Service
Click the getAllCustomers() function to open XQuery Editor View.
Add a for node to the work area by completing the following steps:
In the Data Services Palette, open the CUSTOMER.ds folder, located in DataServices\CustomerDB.
Drag and drop CUSTOMER() into XQuery Editor View. This creates a For:$CUSTOMER source node.
Create a simple mapping. Drag and drop each element in the CUSTOMER source node onto the corresponding element in the Return type.
Note:
You do not need to map the LOGIN_ID element.
Figure 18-2 Simple Mapping
Create an induced mapping, by completing the following steps:
Delete all the simple mappings. (Right-click a map line and select Delete from the pop-up menu.)
Drag and drop the CUSTOMER* element (source node) onto the CUSTOMER element in the Return type.
Notice that the mappings are automatically generated for each element, because the source and target element names are the same.
Figure 18-3 Induced Mapping
Create an overwrite mapping, by completing the following steps:
In the Return type right-click the CUSTOMER element and choose Add Child Element.
Double-click the NewChildElement, enter Addresses, and press Enter.
In the Data Services Palette, open the ADDRESS.ds icon, which is located in the DataServices\CustomerDB folder.
Drag and drop ADDRESS() into XQuery Editor View.
Press Ctrl, and then drag and drop ADDRESS* element (source node) onto the Addresses element in the Return type.
Notice that the entire complex ADDRESS* element is brought to the target, where it overwrites the element, instead of adding it as a child.
Figure 18-4 Overwrite Mapping
18.3 Creating a Basic Parameterized Function
A parameterized query lets you filter returned data based on specific criteria, such as a particular order number, customer name, or customer number.
Objectives
In this exercise, you will:
Create a parameterized function that returns all orders for a particular customer.
Test the function.
Review the XQuery source code.
Instructions
In Design View: Add a new function to the CustomerInfo data service and name it getCustomerByName.
Click getCustomerByName() to open XQuery Editor View for that function.
Add a for node, by completing the following steps:
In the Data Services Palette, open the CUSTOMER.ds folder, which is located in the DataServices\CustomerDB folder.
Drag and drop CUSTOMER() into XQuery Editor View. This creates a For:$CUSTOMER source node.
Create an induced mapping. Drag and drop the CUSTOMER* element (source node) onto the CUSTOMER element in the Return type.
Add a parameter, by completing the following steps:
Right-click an empty spot in XQuery Editor View.
Choose Add Parameter.
Enter FirstName in the Parameter Name field.
Select xs:string as the Primitive Type.
Click OK. (You will need to move the nodes until all are visible because the new parameter node may be placed behind the CUSTOMER node.)
Add a where clause, by completing the following steps:
Drag and drop the parameter's string element onto FIRST_NAME element (source node). Make sure that you release the mouse button when the FIRST_NAME element is highlighted. This action creates a filter for the FIRST_NAME element based on the parameter that is passed to the function.
Confirm that the where clause is correctly set by clicking the $CUSTOMER source node's header. The Expression Editor will open and you should see the following where clause:
$FirstName = $CUSTOMER0/FIRST_NAME
Figure 18-5 First Name Parameter and WHERE Clause
Add a second where clause, by completing the following steps:
Add a new parameter, entering LastName, and selecting xs:string as the Primitive Type.
Click the $CUSTOMER node's header. The Expression Editor opens.
Triple-click inside the where field and place your cursor at the very end, after FIRST_NAME.
Select the "and" logical conjunction from the pop-up operator list (the "..." icon). You can now define the where clause to filter data by last name.
Note:
An alternative method is to simply enter "and" in the field.
Click the string element in the second parameter. The variable name $LastName appears at the end of the where clause.
Choose eq: Compare Single Values from the popup operator list.
Note:
An alternative method is to simply enter eq in the field.
Click the LAST_NAME element in the For:$CUSTOMER node. You should see the following in the where clause field:
$FirstName = $CUSTOMER/FIRST_NAME and $LastName = $CUSTOMER/LAST_NAME
Click the green check button to accept the changes.
Figure 18-6 Query Editor View of Parameterized Query
Test the function, by completing the following steps:
Open CustomerInfo.ds in Test View.
Select getCustomerByName(FirstName, LastName) from the drop-down list.
Enter Jack in $FirstName field.
Enter Black in the $LastName field.
Click Execute.
Confirm the results, which should be as displayed in Figure 18-7.
Figure 18-7 Parameterized Query Results
Open CustomerInfo.ds in Source View to view the generated XQuery. The query should be similar to that displayed in Figure 18-8.
Note:
The automatic namespace assignments may not match.
Figure 18-8 Parameterized Function Source Code
18.4: Creating a String Function with a Built-In XQuery Function
The XQuery language provides more than 100 functions. BEA provides some additional, special purpose functions. In this exercise, you will build a query that uses the built-in XQuery startWith() function to create business logic sufficient to retrieve records based on an OR condition.
Objectives
In this exercise, you will:
Create a string function that will find customers by their social security number.
Test the function.
Review the XQuery source code.
Instructions
Add a new function to the CustomerInfo data service and name it getCustomerBySSN.
Click getCustomerBySSN() to open XQuery Editor View to that function.
Add a for clause, by completing the following steps:
In the Data Services Palette, open the CUSTOMER.ds folder, which is located in DataServices\CustomerDB.
Drag and drop CUSTOMER() into XQuery Editor View. This creates a For:$CUSTOMER node.
4. Create an induced map. Drag and drop the CUSTOMER* element (source) onto the CUSTOMER element in the Return type.
Add a new parameter, entering SSN as the Parameter Name, and selecting xs:string as the Primitive Type.
Add a where clause that uses a built-in XQuery function, by completing the following steps:
Click the $CUSTOMER node's header. The Expression Editor opens.
Click the Add Where Clause icon.
In XQuery Function Palette, expand the String Functions folder.
Drag and drop the following function into the where clause field.
fn:starts-with($arg1 as xs:string?, $arg2 as xs:string?) as xs:boolean
Confirm that the where clause now includes the following built-in function:
fn:starts-with($arg1, $arg2)
Edit the where clause, so that it reads as follows:
fn:starts-with($CUSTOMER/SSN, $SSN)
Click the green check button to accept the changes.
Figure 18-9 Built-In Function Where Clause
Test the function, by completing the following steps:
Open CustomerInfo.ds in Test View.
Select getCustomerBySSN() from the Function drop-down list.
Enter 647 in the xs:string SSN field.
Click Execute.
Confirm the results, which should be as displayed in Figure 18-10.
Figure 18-10 Built-In Function Test Results
Open CustomerInfo.ds in Source View to view the generated XQuery. The query should be similar to that displayed in Figure 18-11.
Note:
The automatic namespace assignments may not match.
Figure 18-11 Source View of Built-In String Function
18.5: Creating a Date Function
A date function lets you retrieve data based on date parameters.
Objectives
In this exercise, you will:
Create a date function that will find customers by the year that they were born.
Test the function.
Review the XQuery source code.
Instructions
Add a new function to the CustomerInfo data service and name it getCustomerByBirthYear.
Click getCustomerByBirthYear() to open XQuery Editor View to that function.
Add a for clause, by completing the following steps:
In the Data Services Palette, open the CUSTOMER.ds folder, which is located in DataServices\CustomerDB.
Drag and drop CUSTOMER() into XQuery Editor View. This creates a for node for the CUSTOMER() function.
Create an induced mapping. Drag and drop the CUSTOMER* element (source) onto the CUSTOMER element (Return).
Create a new parameter, enter BirthYear as the Parameter Name, and select xs:integer as the Primitive Type.
Add a where clause, by completing the following steps:
Click the $CUSTOMER node's header. The Expression Editor opens.
Click the Add Where Clause icon.
In XQuery Function Palette, expand the Duration, Date, and Time Functions folder.
Drag and drop the built-in following function into the where clause field.
fn:year-from-date($arg as xs:date?) as xs:integer?
Click the green check button to accept the changes.
Figure 18-12 Where Clause Using a Built-In Date Function
Test the function, by completing the following steps:
Open CustomerInfo.ds in Test View.
Select getCustomerByBirthYear() from the function drop-down list.
Enter 1970 in the $arg0 field.
Click Execute.
Confirm the results, which should be as displayed in Figure 18-13. There should be five customer profiles returned.
Figure 18-13 Date Function Test Results
Open CustomerInfo.ds in Source View to view the generated XQuery. The query should be similar to that displayed in Figure 18-14.
Note:
The automatic namespace assignments may not match.
Figure 18-14 Date Function Source View
18.6: Creating Outer Joins and Order By Expressions
Outer joins return all records from one table even it doesn't contain values that match those in the other table. For example, an outer join of customers and orders reports all customers—even those without orders.
Objectives
In this exercise, you will:
Create a function that:
Returns customer information and their addresses (there may be more than 1).
Nests address information inside customer information.
Orders customers by first name and last name, in ascending order.
Orders addresses by zip code, in descending order.
Test the function.
Review the XQuery source code.
Instructions
Add a new data service to the MyQueries folder and name it CustomerAddresses.
Associate the CustomerAddresses() data service with the CUSTOMERADDRESS.xsd schema. The schema is located in MyQueries\schemas.
Add a new function to the CustomerAddresses data service and name it getCustomerAddresses.
Figure 18-15 Design View of CustomerAddresses Data Service
Click getCustomerAddresses() to open XQuery Editor View for that function.
Add two for nodes to the work area, by completing the following steps:
In the Data Services Palette, expand DataServices\CustomerDB.
Open the CUSTOMER.ds folder (located in the CustomerDB folder), and then drag and drop CUSTOMER() into XQuery Editor View.
Open the ADDRESS.ds folder (located in the CustomerDB folder), and then drag and drop ADDRESS() into XQuery Editor View.
Figure 18-16 Source Nodes
Create an induced mapping for the CUSTOMER node. Drag and drop the CUSTOMER* element (source) onto the CUSTOMER element (Return).
Create an induced mapping for the ADDRESS node. Drag and drop the ADDRESS* element (source) onto the ADDRESS element (Return).
Note:
Do not drop the source element onto the ADDRESSES element.
Create a source node relationship. Drag and drop the CUSTOMER_ID element in the $CUSTOMER node onto the corresponding element in the $ADDRESS node.
Figure 18-17 Mapped and Joined Source Nodes
Add an OrderBy clause, by completing the following steps:
Click the ADDRESS node's header. The Expression Editor opens.
Click the Order By Clause icon.
Click inside the Order By Clause field.
Enter $ADDRESS/ZIPCODE descending in the field.
Click the green check button to accept the changes.
Figure 18-18 OrderBy Clause
Test the function, by completing the following steps:
Open CustomerAddresses.ds in Test View.
Select getCustomerAddresses() from the function drop-down list.
Click Execute.
Confirm the results. Addresses should be nested after the customer's information.
Figure 18-19 Order By Test Results
Open CustomerAddresses.ds in Source View to view the generated XQuery.
Note:
The automatic namespace assignments may not match.
Figure 18-20 CustomerAddresses() Source View
18.7: Creating Group By and Aggregate Expressions
Sometimes, you may want to group data according to particular data elements, such as grouping customers by state and country.
Objectives
In this exercise, you will:
Create a query using the group by operator and sum() function that generates a report of customers grouped by state and city, showing total sales by city.
Test the function.
Review the XQuery source code.
Instructions
Create a new data service in the MyQueries folder and name it CustomerOrders.
Associate the CustomerOrders data service with the CUSTOMER_ORDER.xsd schema. The schema is located in MyQueries\schemas.
Create a new function and name it getCustomerOrderAmount.
Figure 18-21 Design View of Customer Orders Data Service
Click getCustomerOrderAmount to open XQuery Editor View for that function.
Add a for node, by completing the following steps:
In the Data Services Palette, open the CUSTOMER_ORDER.ds folder, which is located in DataServices\ApparelDB.
Drag and drop CUSTOMER_ORDER() into XQuery Editor View.
Create a GroupBy clause, by completing the following steps:
Right-click the C_ID element in the $CUSTOMER_ORDER source node.
Choose Create Group By. A GroupBy node is created.
Create a simple mapping. Drag and drop the TOTAL_ORDER_AMT from the Group section of the GroupBy node onto the corresponding element in the Return type.
Create a simple mapping. Drag and drop the C_ID element in the By section of the GroupBy node to the corresponding element in the Return type.
Figure 18-22 GroupBy Node Added and Mapped
Modify a Return expression, by completing the following steps:
Click the TOTAL_ORDER_AMOUNT, located in the Return node. The Expression Editor opens. Every element in a Return type has an underlying expression. In this case the expression is:
{fn:data($CUSTOMER_ORDER_group/TOTAL_ORDER_AMT)}
Edit the expression so that it changes fn:data() to fn:sum(), as follows:
{fn:sum($CUSTOMER_ORDER_group/TOTAL_ORDER_AMT)}
Click the green check button to accept the changes.
Figure 18-23 Aggregate Expression
Test the function, by completing the following steps:
Open CustomerOrders.ds in Test View.
Select getCustomerOrderAmount() from the Function drop-down list.
Click Execute.
Confirm the results.
Figure 18-24 Aggregate Test Results
Open CustomerOrders.ds in Source View to view the generated XQuery.
Note:
The automatic namespace assignments may not match that shown in the exercise.
Figure 18-25 Source View of the CustomerOrders Data Service
18.8: Creating Constant Expressions
Creating a data service query that uses a constant expression enables a quick and easy way to locate specific information. For example, you can use a constant expression to identify all customers who ship by Ground method.
Objectives
In this exercise, you will:
Create a non-parameterized function that will return all customers whose default shipping method is GROUND.
Test the function.
View the XQuery source code.
Instructions
Add a new function to the CustomerInfo data service and name it getGroundCustomers.
Click the getGroundCustomers() function to open the XQuery Editor View.
Add a for node, by completing the following steps:
In the Data Services Palette, open the CUSTOMER.ds folder, which is located in the DataServices\CustomerDB folder.
Drag and drop CUSTOMER() into XQuery Editor View.
Create an induced mapping. Drag and drop the entire CUSTOMER* element (source node) onto the CUSTOMER element (Return).
Add a where clause, by completing the following steps:
Click the CUSTOMER node's header. The Expression Editor opens.
Click the Add Where Clause icon.
Enter the following expression as a where clause:
$CUSTOMER/DEFAULT_SHIP_METHOD eq "GROUND"
Click the green check mark icon to accept the where clause for the customer object.
Figure 18-26 Constant Function with Default Expression
Test the function. The results should be as displayed in Figure 18-27.
Figure 18-27 Test Results of a Constant Expression
Open CustomerInfo.ds in Source View. The code should be as displayed in Figure 18-28.
Figure 18-28 Source Code
Lesson Summary
In this lesson you learned how to:
Use the graphical XQuery Editor View to create parameterized, string, and date functions; outer joins, aggregate, and order by and constant expressions.
Use the XQuery Function Palette to add built-in XQuery functions to a query.
Data Service, and use CustomerInfo.ds in the Name field.
