In the previous lesson, you built XQueries using XQuery Editor View. Sometimes, it is necessary to programmatically build a query or modify its code. In this lesson, you will learn how to use Source View to create and edit query functions.
After completing this lesson, you will be able to:
Source View lets you view and/or modify the data service's XQuery source code. In general, a data service is simply a file that contains XQuery code. Although ALDSP provides extensive visual design tools for developing a data service, sometimes you may need to work directly with XQuery syntax.
Two-way editing is supported—changes you make in Source View are reflected in XQuery Editor View, and vice versa. The source code is commented to help you edit the source correctly.
Within Source View, you can use the XQuery Construct Palette, which lets you add any of several built-in generic FLWOR statements to the XQuery syntax. You can then customize the generic statement to match your particular needs.
To add a FLWOR construct, drag and drop the selected item into the appropriate declare function space.
If XQuery Construct Palette is not open, choose View Windows XQuery Construct Palette.
For each of the queries created in this lesson, you will define a function that returns results nested within the Return type. To enable that, you need to create a data service with an undefined XML type. By leaving the XML type's schema undefined, you can modify the Return type on an ad hoc basis, without a need to be concerned about synchronizing the XML and Return types.
There are two basic types of queries: those without parameters and those with parameters. In the previous lesson, you used XQuery Editor View's graphical tools to define a query with parameters. In this exercise, you will use Source Editor to programmatically define a parameterized query.
Note: | Namespaces may differ for your application. |
$p_firstname as xs:string, $p_lastname as xs:string
The code should be similar to the following:
declare function tns:getCustomerByName($p_firstname as xs:string, $p_lastname as xs:string) as element(ns0:Results) {
<tns0:Results>
{
}
</tns0:Results>
for $var in ()
where true()
return ()
$customer/FIRST_NAME eq $p_firstname and $customer/LAST_NAME eq $p_lastname
declare function tns:getCustomerByName($p_firstname as xs:string, $p_lastname as xs:string) as element(tns0:Results) {
<tns:Results>
{
for $customer in (ns1:CUSTOMER())
where ($customer/FIRST_NAME eq $p_firstname and $customer/LAST_NAME eq $p_lastname)
return
($customer)
}
</tns:Results>
XQuery provides numerous string functions that can be incorporated into your business logic.
$fullname as xs:string, $ssn as xs:string
fn:contains(fn:upper-case(fn:concat($customer/FIRST_NAME,"
",$customer/LAST_NAME)), fn:upper-case($fullname) ) or
fn:starts-with($customer/SSN, $ssn)
Note: | You can either type the code in or build the clause by using the following built-in functions, located in the XQuery Function Palette: |
fn:concat fn:starts-with
fn:contains fn:upper-case
Note: | The full name is created "on-the-spot" by concatenating FIRST_NAME and LAST_NAME elements to the local (XQuery engine internal) variable such as $p_name. Upper case is used to normalize names. |
declare function tns:getCustomerByNameOrSSN($fullname as xs:string, $ssn as xs:string) as element(ns0:Results) {
<ns0:Results>
{
for $customer in (ns1:CUSTOMER())
where ( fn:contains(fn:upper-case(fn:concat($customer/FIRST_NAME,"
",$customer/LAST_NAME)), fn:upper-case($fullname) ) or
fn:starts-with($customer/SSN, $ssn) )
return
($customer)
}
</ns0:Results>
Outer joins allow you to get results from the joined objects even if the primary key is not represented in both objects. For example, an outer join of customers and orders reports all customers—even those without orders.
Note: | Namespaces may differ for your application. |
declare function tns:getCustomerAddresses() as element(ns0:Results) {
for $var in ()
order by ()
return ()
for $customer in (ns1:CUSTOMER())
$customer/FIRST_NAME, $customer/LAST_NAME
return
<CUSTOMER>
<FIRST_NAME>{fn:data($customer/FIRST_NAME) }</FIRST_NAME>
<LAST_NAME>{fn:data($customer/LAST_NAME)}</LAST_NAME>
{
for $address in ()
where ($address/CUSTOMER_ID eq $customer/CUSTOMER_ID)
order by $address/ZIPCODE ascending
return
$address
}
</CUSTOMER>
Note: | You can either type the code in, or use the XQuery Function Palette and XQuery Construct Palette to build up your query function. |
for $address in (ns2:ADDRESS())
declare function tns:getCustomerAddresses() as element(ns0:Results) {
<tns0:Results>
{
for $customer in (ns1:CUSTOMER())
order by $customer/FIRST_NAME, $customer/LAST_NAME
return
<CUSTOMER>
<FIRST_NAME>{ fn:data($customer/FIRST_NAME) }</FIRST_NAME>
<LAST_NAME>{fn:data($customer/LAST_NAME)}</LAST_NAME>
{
for $address in (ns2:ADDRESS())
where ($address/CUSTOMER_ID eq $customer/CUSTOMER_ID)
order by $address/ZIPCODE ascending
return
$address
}
</CUSTOMER>
}
</tns0:Results>
Inner joins mandate that the only items that are returned are with a corresponding entry (such as a primary key in the relational world) in another data source. The following are introduced:
Note: | Namespaces may differ for your application. |
declare function tns:getTop10Customers() as element(ns0:Results) {
let $top10:=
for $var in ()
where true()
order by ()
return ()
for $customer in (ns1:CUSTOMER())
for $order in (ns3:CUSTOMER_ORDER())
where ($customer/CUSTOMER_ID eq $order/CUSTOMER_ID)
order by $order/TOTAL_ORDER_AMOUNT descending
return
<CUSTOMER>
<CUSTOMER_NAME>
{fn:concat($customer/FIRST_NAME," ", $customer/LAST_NAME)}
</CUSTOMER_NAME>
<ORDER_ID>{fn:data($order/ORDER_ID)}</ORDER_ID>
<TOTAL_ORDERS>{fn:data($order/TOTAL_ORDER_AMOUNT)}</TOTAL_ORDERS>
</CUSTOMER>
return fn:subsequence($top10, 1, 10)
Note: | You can either type the code in, or use the XQuery Function Palette and XQuery Construct Palette to build up your query. |
declare function tns:getTop10Customers() as element(ns0:Results) {
<tns0:Results>
{
let $top10:=
for $customer in (ns1:CUSTOMER())
for $order in (ns3:CUSTOMER_ORDER())
where ($customer/CUSTOMER_ID eq $order/CUSTOMER_ID)
order by $order/TOTAL_ORDER_AMOUNT descending
return
<CUSTOMER>
<CUSTOMER_NAME>
{fn:concat($customer/FIRST_NAME," ", $customer/LAST_NAME)}
</CUSTOMER_NAME>
<ORDER_ID>{fn:data($order/ORDER_ID)}</ORDER_ID>
<TOTAL_ORDERS>
{fn:data($order/TOTAL_ORDER_AMOUNT)}
</TOTAL_ORDERS>
</CUSTOMER>
return fn:subsequence($top10, 1, 10)
}
</tns0:Results>
Retrieving customers grouped by states and cities is not only often needed; it is also a classic database exercise. The following are introduced:
for $var in ()
group $var as $varGroup by () as $var2
return ()
for $address in ns2:ADDRESS()
group $address as $stateGroup by $address/STATE as $state
Note: | Your source is invalid until you complete the next step. |
for $address in ns2:ADDRESS()
return
<state>
<name>{$state}</name>
<number>{fn:count($stateGroup/CUSTOMER_ID)}</number>
{
Note: | The clause includes the fn:count() built-in function, available from the XQuery Function Palette. |
for $address1 in ns2:ADDRESS()
where $address1/STATE eq $state
group $address1 as $cityGroup by $address1/CITY as $city
return
<cities>
<city>{$city}</city>
<number>{fn:count($cityGroup/CUSTOMER_ID)}</number>
</cities>
}
</state>
declare function tns:getNumCustomersByState() as element(ns0:Results) {
<tns0:Results>
{
for $address in ns2:ADDRESS()
group $address as $stateGroup by $address/STATE as $state
return
<state>
<name>{$state}</name>
<number>{fn:count($stateGroup/CUSTOMER_ID)}</number>
{
for $address1 in ns2:ADDRESS()
where $address1/STATE eq $state
group $address1 as $cityGroup by $address1/CITY as $city
return
<cities>
<city>{$city}</city>
<number>{fn:count($cityGroup/CUSTOMER_ID)}</number>
</cities>
}
</state>
}
</tns0:Results>
};
This example shows how you can create switch-like conditions when building your query. The If-Then-Else-If concept is introduced.
Note: | Namespaces may differ for your application. |
for $customer in ns1:CUSTOMER()
for $orders in ns3:CUSTOMER_ORDER()
where $customer/CUSTOMER_ID eq $orders/CUSTOMER_ID
group $orders as $orderGroup by fn:concat($customer/FIRST_NAME," ",$customer/LAST_NAME) as $customer_name
let $sum := fn:sum($orderGroup/TOTAL_ORDER_AMOUNT)
return
<CUSTOMER_RATING>
<CUSTOMER_ID>{$customer_name}</CUSTOMER_ID>
<RATING> {if ($sum>=10000) then
"GOLD"
else if ($sum<5000) then
"REGULAR"
else
"SILVER"
}
</RATING>
</CUSTOMER_RATING>
declare function tns:getCustomerLevels() as element(ns0:Results) {
<tns0:Results>
{
for $customer in ns1:CUSTOMER()
for $orders in ns3:CUSTOMER_ORDER()
where $customer/CUSTOMER_ID eq $orders/CUSTOMER_ID
group $orders as $orderGroup by fn:concat($customer/FIRST_NAME,"
",$customer/LAST_NAME) as $customer_name
let $sum := fn:sum($orderGroup/TOTAL_ORDER_AMOUNT)
return
<CUSTOMER_RATING>
<CUSTOMER_ID>{$customer_name}</CUSTOMER_ID>
<RATING> {
if ($sum>=10000) then
"GOLD"
else if ($sum<5000) then
"REGULAR"
else
"SILVER"
}
</RATING>
</CUSTOMER_RATING>
}
</tns0:Results>
};
This example demonstrates how to integrate data from two different data sources and present the results in a single report that lets you view the data source information as two separate variables.
$customer_id as xs:string
for $customer in ns1:CUSTOMER()
let $applOrder:= for $order1 in ns4:CUSTOMER_ORDER()
where $customer/CUSTOMER_ID = $order1/C_ID
return
$order1
let $elecOrder := for $order2 in ns3:CUSTOMER_ORDER()
where ($order2/CUSTOMER_ID eq $customer/CUSTOMER_ID)
return
$order2
where ($customer/CUSTOMER_ID eq $customer_id)
return
<CUSTOMER>
{$customer}
<Orders>
{$applOrder, $elecOrder }
</Orders>
</CUSTOMER>
Note: | ns3:CUSTOMER_ORDER() refers to CUSTOMER_ORDER.ds in ElectronicsDB folder |
declare function tns:getCombinedOrders($customer_id as xs:string) as element(ns0:Results) {
<tns0:Results>
{
for $customer in ns1:CUSTOMER()
let $applOrder:= for $order1 in ns4:CUSTOMER_ORDER()
where ($order1/C_ID eq $customer/CUSTOMER_ID)
return
$order1
let $elecOrder := for $order2 in ns3:CUSTOMER_ORDER()
where ($order2/CUSTOMER_ID eq $customer/CUSTOMER_ID)
return
$order2
where ($customer/CUSTOMER_ID eq $customer_id)
return
<CUSTOMER>
{$customer}
<Orders>
{ $applOrder, $elecOrder }
</Orders>
</CUSTOMER>
}
</tns0:Results>
};
In this lesson you, learned how to: