Examples of Simple Queries

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

• Example: Return Customers by Name
• Example: Query Customers by ID and Sort by State

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

Example: Return Customers by Name

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

Build the Query

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

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

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

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

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

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

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

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

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

   

   
    

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

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

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

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

   

   
    

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

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

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

 

View the XQuery and Run the Query to Test it

Now that you have built the query, you can switch to the Test tab to view the generated XQuery and run the query to see the kind of result it returns.

1. Click on the Test tab.

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

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

   

   
    

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

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

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

 

---

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

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

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

   

   
    

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

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

 

---

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

Example: Query Customers by ID and Sort by State

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

This example shows how to do the following:

• Project output
• Specify the order of the result

Open the Data Sources and Add a Target Schema

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

2. On the Builder Toolbar—>Sources tab, click Relational Databases and open two data sources:

   • Double-click on the PB-WL relational database to open the schema for this data source.
   • Double-click on the PB-BB relational database to open the schema for this data source.

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

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

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

Map Nodes from Source to Target Schema to Project Output

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

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

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

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

Join Two Sources

To create a join between Wireless (PB-WL) and Broadband (PB-BB) on customer IDs, do the following:

• Drag and drop Broadband (PB-BB) CUSTOMER_ID (under CUSTOMER*) onto the associated Wireless (PB-WL) CUSTOMER_ID element.

The following shows the mappings in the Data View Builder.

Figure 3-9 Example: Query Customers by ID and Sort Output by State

 

Specify the Order of the Result Using the Sort By Features

To order the output alphabetically by State do the following:

1. Click the Sort By tab.

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

2. From the drop-down menu choose CUSTOMER*, and then click into the Direction cell next to STATE and set STATE to Ascending.

   This will cause the query to display the results in ascending order by state.

View the XQuery and Run the Query to Test it

Now that you have built the query, you can switch to the Test tab to view the generated XQuery and run the query to see the kind of result it returns.

1. Click on the Test tab.

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

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

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

<customers>
	{
	for $PB_WL.CUSTOMER_1 in document("PB-WL")/db/CUSTOMER
	let $CUSTOMER_2 := 
	               for $PB_BB.CUSTOMER_3 in document("PB-BB")/db/CUSTOMER
	               where ($PB_BB.CUSTOMER_3/CUSTOMER_ID  eq  $PB_WL.CUSTOMER_1/CUSTOMER_ID)
	               return
	               xf:true()
	where xf:not(xf:empty($CUSTOMER_2))
	return
	<CUSTOMER>
		<FIRST_NAME>{ xf:data($PB_WL.CUSTOMER_1/FIRST_NAME) }</FIRST_NAME>
		<LAST_NAME>{ xf:data($PB_WL.CUSTOMER_1/LAST_NAME) }</LAST_NAME>
		<STATE>{ xf:data($PB_WL.CUSTOMER_1/STATE) }</STATE>
	</CUSTOMER>
	sortby(STATE ascending)
	}
</customers>

 

---

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

   Querying these data sources as described in this example produces the XML query result shown in the following code listing.

Listing 3-5 XML Result for Example: Query Customers by ID and Sort by State

<customers>
  <CUSTOMER>
    <FIRST_NAME>JOHN_3</FIRST_NAME>
    <LAST_NAME>KAY_3</LAST_NAME>
    <STATE>AZ</STATE>
  </CUSTOMER>
  <CUSTOMER>
    <FIRST_NAME>JOHN_8</FIRST_NAME>
    <LAST_NAME>KAY_8</LAST_NAME>
    <STATE>AZ</STATE>
  </CUSTOMER>
  <CUSTOMER>
    <FIRST_NAME>JOHN_10</FIRST_NAME>
    <LAST_NAME>KAY_10</LAST_NAME>
    <STATE>CA</STATE>
  </CUSTOMER>
  <CUSTOMER>
    <FIRST_NAME>JOHN_5</FIRST_NAME>
    <LAST_NAME>KAY_5</LAST_NAME>
    <STATE>CA</STATE>
  </CUSTOMER>
  <CUSTOMER>
    <FIRST_NAME>JOHN_4</FIRST_NAME>
    <LAST_NAME>KAY_4</LAST_NAME>
    <STATE>NV</STATE>
  </CUSTOMER>
  <CUSTOMER>
    <FIRST_NAME>JOHN_9</FIRST_NAME>
    <LAST_NAME>KAY_9</LAST_NAME>
    <STATE>NV</STATE>
  </CUSTOMER>
  <CUSTOMER>
    <FIRST_NAME>JOHN_1</FIRST_NAME>
    <LAST_NAME>KAY_1</LAST_NAME>
    <STATE>TX</STATE>
  </CUSTOMER>
  <CUSTOMER>
    <FIRST_NAME>JOHN_6</FIRST_NAME>
    <LAST_NAME>KAY_6</LAST_NAME>
    <STATE>TX</STATE>
  </CUSTOMER>
  <CUSTOMER>
    <FIRST_NAME>JOHN_2</FIRST_NAME>
    <LAST_NAME>KAY_2</LAST_NAME>
    <STATE>WA</STATE>
  </CUSTOMER>
  <CUSTOMER>
    <FIRST_NAME>JOHN_7</FIRST_NAME>
    <LAST_NAME>KAY_7</LAST_NAME>
    <STATE>WA</STATE>
  </CUSTOMER>
</customers>

 

---