Samples Tutorial

Building XQueries in Source View

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.

Objectives

After completing this lesson, you will be able to:

Use Source View to add, edit, or delete XQuery code that defines a data service's query functions including creating:

A new XML type
A parameterized Xquery
Inner and outer joins
A multi-level group by
If-then-else if constructs
A union and concatenation operation

Compare the coded query with the XQuery Editor View.

Overview

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.

Figure 19-1 Source View Sample

Source View Tools

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.

Figure 19-2 Query Construct Palette

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.

19.1 Creating a New XML Type

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.

Objectives

In this exercise, you will:

Create a new data service, called XQueries.ds.
Create a new, but undefined, XML type.

Instructions

Create a new data service in the MyQueries folder and name it XQueries.
Create a new XML type by completing the following steps:

Right-click the XQueries Data Service header.
Select Create XML Type.
Enter Results in the Return Type field.

Note:

Do not change the default settings for the Schema File and Target Namespace fields.

Click OK.

Figure 19-3 Create New XML Type

Create New XML Type

Confirm that the data service diagram is as displayed in Figure 19-4.

Figure 19-4 Design View: Undefined Results Type

Design View: Undefined Results Type

19.2 Creating a Basic Parameterized XQuery

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.

Objectives

In this exercise, you will:

Build a query that retrieves customer information based on first and last names.
View the results in XQuery Editor View.
Test the function.

Instructions

Note:

Namespaces may differ for your application.

Add a new function to XQueries.ds and name it getCustomerByName.
Open Source View.
Define the function declaration, by completing the following steps:

Add the following parameter to the first parenthesis:

$p_firstname as xs:string, $p_lastname as xs:string

Remove the asterisk (*), because you want this function to only return a single result.

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) {

Click the + symbol next to the getCustomerByName() function. This opens the function body.
Split the <tns0:RESULTS/> element into open and end tags, with curly braces in between for the XQuery. The code should be as follows (ignore the error indicator):

<tns0:Results>

</tns0:Results>

Open XQuery Construct Palette.
Drag and drop the FWR construct between the curly braces. The code should be as follows:

for $var in ()

where true()

return ()

Define the for clause by completing the following steps:

Change the variable to $customer.
In the Data Services Palette, expand CustomerDB\CUSTOMER.ds.
Drag and drop CUSTOMER() into the for clause's first empty parenthesis. The code should be similar to the following:

for $customer in (ns1:CUSTOMER())

where true ()

return ()

Replace the where clause true() code with the following:

$customer/FIRST_NAME eq $p_firstname and $customer/LAST_NAME eq $p_lastname

Set the return clause, by adding $customer between the parenthesis.
Confirm that the source code is as displayed in Figure 19-5; namespaces may be different for your application.

Figure 19-5 Parameterized Query Source Code

Parameterized Query Source Code

Build the DataServices project.
Open XQueries.ds in XQuery Editor View and review the graphical version of the XQuery code. It should be as displayed in Figure 19-6.

Figure 19-6 Query Editor View of Parameterized Function

Query Editor View of Parameterized Function

Test the function, by completing the following steps:

Open XQueries.ds in Test View.
Select getCustomersByName() from the Function drop-down list.
Enter the following parameters:

Firstname: Jack

Lastname: Black

Confirm the results.

Figure 19-7 Test Results of a Parameterized Function

Test Results of a Parameterized Function

(Optional) Open CustomerInfo.ds in XQuery Editor View and compare the diagrams for the two data services.

XQuery Code Reference for a Parameterized Function

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>

19.3 Creating a String Function

XQuery provides numerous string functions that can be incorporated into your business logic.

Objectives

In this exercise, you will:

Create a startwith() function that retrieves customer information by name or SSN.
Test the function.

Instructions

Add a new function to XQueries.ds and name it getCustomerByNameorSSN().
Open XQueries.ds in Source View.
Define the function declaration, by changing the parameter as follows:

$fullname as xs:string, $ssn as xs:string

Replace the contents of the where clause with the following:

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.

Leave the return clause as $customer so that all elements in the type are returned.
Confirm that the code is as follows (namespaces may be different for your application):

Figure 19-8 Source View of XQueries.ds

Source View of XQueries.ds

Open XQueries.ds in XQuery Editor View.

Figure 19-9 Query Editor View of XQueries.ds

Query Editor View of XQueries.ds

Test the query by completing the following steps:

Open XQueries.ds in Test View.
Enter a value in both Parameter fields. Neither field can be blank; however, because of the query logic, only one parameter needs to be matched.
Click Execute. The query should return results based on your keyword search parameters. See below for results in Test View and the underlying code.

Figure 19-10 Test Results of String Function

Test Results of String Function

XQuery Code Reference for a String Function

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>

19.4 Building an Outer Join and Using Order By

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.

Objectives

In this exercise, you will:

Build a query that retrieves all customers and lists their addresses, if any.
Shape the return data to include:

All customers, even those without known addresses.
Nest addresses with customers (there may be more than 1).
Order customers by first name and last name.
Order the addresses by zip code.

Test the function.

Instructions

Note:

Namespaces may differ for your application.

Add a new function to XQueries.ds and name it getCustomerAddresses.
Open XQueries.ds in Source View.
Define the function declaration by removing the asterisk (*). The code should be as:

declare function tns:getCustomerAddresses() as element(ns0:Results) {

Click the + symbol next to the getCustomerAddresses() function. This opens the function body.
Split the <tns0:RESULTS/> element into open and end tags, with curly braces in between for the XQuery.
Open XQuery Construct Palette, and then drag and drop the FOR construct between the curly braces. The code should be as follows:

for $var in ()

order by ()

return ()

Set the for clause, using a $customer variable that is associated with CUSTOMER() located in the CustomerDB\CUSTOMER.ds folder within the Data Services Palette.

for $customer in (ns1:CUSTOMER())

Set the order by clause, by replacing the (), as follows:

$customer/FIRST_NAME, $customer/LAST_NAME

Set the return clause, by replacing the (), as follows:

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.

Set the $address clause by associating it with ADDRESS(), which is located in CustomerDB\ADDRESS.ds folder within Data Services Palette.

for $address in (ns2:ADDRESS())

Confirm that the query is as shown in Figure 19-11; namespaces may be different for your application.

Figure 19-11 Source View of Outer View and Order By Function

Source View of Outer View and Order By Function

Open XQueries.ds in XQuery Editor View.

Figure 19-12 XQuery Editor View of Outer Join and Order By Function

XQuery Editor View of Outer Join and Order By Function

Open XQueries.ds in Test View and test the query; no parameters are required. The XQuery function appears below.

Figure 19-13 Test Results of Outer Join and Order By Function

Test Results of Outer Join and Order By Function

XQuery Code Reference for an Outer Join and Order By Function

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>

19.5 Creating an Inner Join and a Top N

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:

let clauses
Nested for clauses
concat() and subsequence() XQuery functions

Objectives

In this exercise, you will:

Build a query that retrieves the top 10 customers who have placed orders with the company.
Define the shape of the returned data to include:

Customer's full name.
Order ID.
Total order amount (in descending order).

Test the function.

Instructions

Note:

Namespaces may differ for your application.

Add a new function to XQueries.ds and name it getTop10Customers.
Open XQueries.ds in Source View.
Define the function declaration by removing the asterisk (*). The code should be as follows:

declare function tns:getTop10Customers() as element(ns0:Results) {

Click the + symbol next to the getTop10Customers() function. This opens the function body.
Add curly braces between the two tags.
After the opening curly brace, add the following let clause, which will hold the results of subsequent for clauses:

let $top10:=

Open XQuery Construct Palette and then drag and drop the FLWOR construct after the let clause. The code should be as follows:

for $var in ()

where true()

order by ()

return ()

Set the for clause using a $customer variable that is associated with CUSTOMER() located in the CustomerDB\CUSTOMER.ds folder within Data Services Palette.

for $customer in (ns1:CUSTOMER())

Create a second for clause, using a $order variable that is associated with CUSTOMER_ORDER() located in the ElectronicsDB\CUSTOMER_ORDER.ds folder within Data Services Palette.

for $order in (ns3:CUSTOMER_ORDER())

Set the where clause, by replacing the true() with the following code:

where ($customer/CUSTOMER_ID eq $order/CUSTOMER_ID)

Set the order by clause, by entering the following code in the ():

order by $order/TOTAL_ORDER_AMOUNT descending

Set the return clause, by entering the following code:

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.

Confirm that the source code is similar to that displayed in Figure 19-14; namespaces may vary.

Figure 19-14 Source Code for Inner Join and Top N Function

Source Code for Inner Join and Top N Function

Open XQueries.ds in XQuery Editor View.

Figure 19-15 XQuery Editor View of Inner Join and Top N Function

XQuery Editor View of Inner Join and Top N Function

Open XQueries.ds in Test View; no parameters are required to run your query. You should see a document containing the top 10 orders will appear, ordered by total amount. The XQuery function appears below.

Figure 19-16 Test View for Inner Join and Top N Function

Test View for Inner Join and Top N Function

XQuery Code Reference for Inner Join and Top N Function

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>

19.6 Creating a Multi-Level Group By

Retrieving customers grouped by states and cities is not only often needed; it is also a classic database exercise. The following are introduced:

Group by clause.
count() function.

Objectives

In this exercise, you will:

Create a query that determines the number of customers, by state and by city.
Test the function.

Instructions

Add a function to XQueries.ds and name it getNumCustomersByState().
Open XQueries.ds in Source View.
Define the function declaration, by removing the asterisk *.
Click the + symbol next to the getNumCustomersByState() function.
Split the <tns0:Results/> element into open and end tags, with curly braces in between.
Open XQuery Construct Palette and then drag and drop the for-group-return (FGR) construct between the curly braces:

for $var in ()

group $var as $varGroup by () as $var2

return ()

Set the for and group clauses as follows:

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.

Associate the for clause with ADDRESS() located in CustomerDB\Address.ds within the Data Services Palette as follows:

for $address in ns2:ADDRESS()

Set the return clause, as follows:

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.

Open XQuery Construct Palette and then drag and drop the FWGR construct after the open curly brace of the return clause:

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>

Make sure that the namespace in the second for clause is the same as the namespace in the first for clause.
Confirm that the code is as displayed in Figure 19-17(namespaces may be different for your application).

Figure 19-17 Source Code for Multi-Level Group By Function

Source Code for Multi-Level Group By Function

Open XQueries.ds in XQuery Editor View.

Figure 19-18 XQuery Editor View of Multi-Level Group By Function

XQuery Editor View of Multi-Level Group By Function

Open XQueries.ds in Test View and test the function; no parameters are required. You should see the state name, followed by the number of customers residing in that state, followed by the city name and number of customers residing in that city. The underlying XQuery also appears below.

Figure 19-19 Test View of Multi-Level Group By Function

Test View of Multi-Level Group By Function

XQuery Code Reference for Multi-Level Group By Function

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>

};

19.7 Using If-Then-Else If

This example shows how you can create switch-like conditions when building your query. The If-Then-Else-If concept is introduced.

Objectives

In this exercise, you will:

Create a function that returns different achievement levels as strings for a set of customers, based on their total order amount.
Test the function.

Instructions

Note:

Namespaces may differ for your application.

Add a new function to XQueries.ds and name it getCustomerLevels.
Open XQueries.ds in Source View.
Define the function declaration, by removing the asterisk (*).
Split the <tns0:Results/> element into open and end tags, with curly braces ( {} ) in between.
Add a for clause, using a $customer variable that is associated with CUSTOMER() located in CustomerDB\CUSTOMER.ds within Data Services Palette.

for $customer in ns1:CUSTOMER()

Add a second for clause, using an $orders variable that is associated with CUSTOMER_ORDER() located in the ElectronicsDB\CUSTOMER_ORDER.ds folder within Data Services Palette.

for $orders in ns3:CUSTOMER_ORDER()

Add where, let, and return clause code, placing it immediately after the second for clause:

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>

Confirm that the code is as displayed in Figure 19-20; namespaces may be different in your application.

Figure 19-20 Source View of If-Then-Else If Function

Source View of If-Then-Else If Function

Open XQueries.ds in XQuery Editor View.

Figure 19-21 XQuery Editor View of If-Then-Else If Function

XQuery Editor View of If-Then-Else If Function

Open XQueries.ds in Test View and test the function; no parameters are required. When you run the query you will see results organized according to the following levels of purchases:

Gold for total orders >= 10000
Silver for total orders >= 5000 and <10000
Regular for total orders below 5000

The customer's full name and level are also shown. The XQuery function appears below.

Figure 19-22 Test View of If-Then-Else If Function

Test View of If-Then-Else If Function

XQuery Code Reference for If-Then-Else Function

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>

};

19.8 Creating a Union and Concatenation

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.

Objectives

In this exercise, you will:

Create a function that gathers results from two order entry systems: RTLAPPLOMS and RTLELECOMS.
Test the function.

Instructions

Add a new function to XQueries.ds and name it getCombinedOrders.
Open XQueries.ds in Source View.
Define the function declaration, by removing the asterisk * and adding the following parameter:

$customer_id as xs:string

Split the <ns0:Results/> element into open and end tags, with curly braces ( {} ) in between.
Open XQuery Construct Palette and then drag and drop the FLWR construct between the curly braces.
Set the for clause using a $customer variable that is associated with CUSTOMER() located in the CustomerDB\CUSTOMER.ds folder within Data Services Palette.

for $customer in ns1:CUSTOMER()

Set the let clause, using a $applOrder variable that is associated with CUSTOMER_ORDER(), which is located in ApparelDB\CUSTOMER_ORDER.ds within Data Services Palette.

let $applOrder:= for $order1 in ns4:CUSTOMER_ORDER()

Set the where clause as follows:

where $customer/CUSTOMER_ID = $order1/C_ID

Set the return clause, as follows:

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

Confirm that the code is as displayed in Figure 19-23; the namespaces may vary in your application.

Figure 19-23 Source View for Union and Concatenation Operation

Source View for Union and Concatenation Operation

Open XQueries.ds in XQuery Editor View.

Figure 19-24 XQuery Editor View of Union and Concatenation Operation

XQuery Editor View of Union and Concatenation Operation

Open XQueries.ds in Test View, and then test the getCombinedOrders() function using CUSTOMER3 as the parameter. The XQuery function appears below.

Figure 19-25 Test View of Union and Concatenation Function

Test View of Union and Concatenation Function

XQuery Reference Code for Union and Concatenation Operation

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>

};

Lesson Summary

In this lesson you, learned how to:

Use Source View to add, edit, or delete XQuery code that defines a data service's query functions.
Compare the coded query with the XQuery Editor View.