Transforming Data Using the XQuery Mapper

# Examples: Manipulating and Constraining Data Using XQuery

This section provides example scenarios for the Sample Project located in your BEA XQuery Mapper installation. To learn more about opening the Sample Project, see XQuery Mapper Sample Project.

This section includes the following topics:

# Combining Data From Different Schemas

You can use the XQuery Mapper to combine the contents of two different schemas, as shown in the following Figure 3-1.

In this example, customer data (as described in the CustInfo.xsd schema) is merged with a repeating element (line-items, as described in the PO.xsd schema) to form a single XML document valid against the POCustInfo.xsd schema.

Figure 3-1 Combining Data From Different Schemas

#### To Combine Data from Different Schemas

1. Launch Eclipse and navigate to the Sample Project.
2. Right-click the XQuery Transformation folder.
3. Choose New—>XQuery Transformation.
4. Verify name of parent folder.
5. For this example, the parent folder should be Samples/XQueryTransformations

6. In the Filename path, enter combineData.
7. Select the following source files:
8. CustInfo.xsd\customer
PO.xsd\purchase-order
9. Select the following target schema and element:
10. POCustInfo.xsd\purchase-order

After selecting the source and target data, the combineData.xq file is created.

11. Select the Source elements described in the following table and drag them to the Target pane.
12.  Source Links to.... Target customer purchase-order\customer customer\customer-id purchase-order\customer\customer-id customer\customer-name purchase-order\customer\customer-name customer\customer-address purchase-order\customer\customer-address purchase-order\line-item purchase-order\line-items\line-item purchase-order\line-items\line-item\part-no purchase-order\line-items\line-item\part-no purchase-order\line-items\line-item\quantity purchase-order\line-items\line-item\quantity

As shown in the following figure, a dotted line represents a Structural link. This type of link is created between two parent structures that do not map data directly.

As shown in the following figure, a solid line represents a Data link. This type of link converts the value of the source node directly to the value of the target node.

The map between the Source and Target elements is shown in the following figure.

Figure 3-2 Data Transformation in Design View

For information about testing XQuery Transformations, see Testing XQuery Files.

# Mapping Repeating Elements and Creating a Join

In this section, PriceQuote.xsd , AvailableQuote.xsd, and taxrate.xsd are joined to create a single Quote.xsd file.

This example includes the following steps:

## Step 1. Create a New XQuery File

In this section you will create an XQuery transformation using the AvailQuote.xsd, PriceQuote.xsd, and taxrate.xsd files. After doing so, you will map several priceQuote and availRequest source elements to corresponding target elements.

1. Launch Eclipse and navigate to the Sample Project.
2. Right-click the XQuery Transformation folder.
3. Choose New—>XQuery Transformation.
4. Verify that the parent folder is Samples/XQueryTransformations
5. In the Filename path, enter Join.
6. Select the following source schema files and elements:
7. PriceQuote.xsd\priceQuote
PriceQuote.xsd\taxRate
AvailQuote.xsd\availRequest 
8. Select the following target schema and element:
9. Quote.xsd\quote

After selecting the source and target data, the Join.xq file is created in the Samples/XQueryTransformations folder.

10. Click the Source elements described in the following table and drag and drop them on the Target element indicated to create a link between the elements.

11.

12. The Design tab displays the map you created as shown in the following figure.
13. Figure 3-3 Join Example

## Step 2. Add a Conditional Constraint

The source documents priceQuote and availRequest share the common element, widgetId. In this sub-section, you add a conditional constraint that specifies if the widgetId of the availRequest element is equal to the widgetId of the priceRequest element, then the merged repeating element quoteResponse should be returned.

1. Open the join.xq in the Design view.
2. In the Source pane, click the following element:
3. priceRequests/priceRequest/widgetId
4. Drag-and-drop priceRequests/priceRequest/widgetId onto the following element in the Source pane:
5. availRequest/widgetId 

A line between the two widgetId nodes (to indicate that you merged the elements) is displayed, as shown in the following figure.

Figure 3-4 Adding a Conditional Constraint to a Join

7. View the changes by clicking the Source tab.
8. The link between the widgetId nodes generates:

• The where clause in the for loop.
• This where clause constrains or limits the output of the for loop.
• The where clause specifies that if the expression in the where clause is true, the for loop will output the contents of the return.
• For this example, if the widgetId of the availRequest element is equal to the widgetId of the priceRequest element, the following XML data is returned: </quoteResponse>

The quoteResponse element is empty. Use the procedure in Step 3. Add Links to Populate Empty Element to add data links that will populate the quoteResponse

element.

Use the procedure in this section to add data links to populate the quoteResponse element.

#### To Populate the QuoteResponse Element

1. Open Join.xq in the Design view.
2. Click the Source elements described in the following table and drag and drop them on the Target element indicated to create a link between the elements.
3.  Source Element Links to.... Target Element priceQuote\priceRequests\priceRequest\price quote\quoteResponse\unitprice priceQuote\priceRequests\priceRequest\widgetid quote\quoteResponse\widgetid availRequest\requestedQuantity quote\quoteResponse\requestedQuantity availRequest\quantityAvail quote\quoteResponse\fillOrder availRequest\shipDate quote\quoteResponse\shipDate

After creating the preceding links, the following map is displayed in the Design tab.

Figure 3-5 Populating the QuoteResponse Element

## Step 4. Calculate Total Cost

In this step, you will add the function for calculating the total cost of the purchase order to the XQuery source.

#### To Calculate Total Cost

1. Open the Join.xq file in Source view.
2. Add the following function declaration to the XQuery:

Listing 3-1 calculateTotalPrice Function

declare function calculateTotalPrice($taxRate as xs:float,$quantity as xs:float,$price as xs:float)   as xs:float {  let$taxQuantity := ($taxRate *$quantity)
    let $totalTax := ($taxQuantity * $price)  let$costNoTax := ($quantity *$price)
    let	$totalCost := ($totalTax + $costNoTax)   return$totalCost
};
1. Open the Join.xq file in Design view.
2. Note: Your Join.xq file now includes two function declarations: calculateTotalPrice and Join. Note that when you have more than one function in an XQ file, the function with the same name as the XQ file is rendered in the Design View. (In this case, the Join function is displayed in the Design view.)

3. In the Target pane, select the totalCost node. Keep it selected for the next step.
4. Navigate to the Target Expression tab and choose General.
5. To calculate the totalCost, add the following General Expression:
6. calculateTotalPrice($taxRate,$availRequest/ns1:requestedQuanity,$priceRequest/ns0:price) Figure 3-6 General Expression for totalCost Element 7. Click Apply. 8. The expression is added to the totalCost element in your XQuery. 9. Save your changes. 10. The design view reflects the new calculation for totalCost. Figure 3-7 totalCost Calculation in Design View ## Step 5. Add a Constraint With Multiple Conditions Creating a constraint using the Where Clause Expression pane of the Constraints tab adds a where clause to the XQuery for loops—limits the target repeating elements that are returned during run time. During run time, the for loop will iterate only over those repeating elements that meet the complex condition. In this section, you will add another condition (resulting in a complex condition) to the where of the for loop to further limit what is returned by the for loop. #### To Add a Constraint with Multiple Conditions 1. Open the join.xq file. 2. Select the link between: 3. availRequest and quote\quoteResponse The single condition that makes up the where clause is displayed in Where Clause Expression pane of the Constraints tab. data($priceRequest/ns0:widgetId) = data($availRequest/ns1:widgetId) 4. Select the availRequest/requestedQuanity node and drag-and-drop it into the Left Hand Expression section of the Where Clause Expression pane to create: 5. data($availRequest/ns1:requestedQuanity)
6. Select the < operator.
7. Remove the text in the Right Hand Expression section of the Where Clause Expression pane.
8. In the Right Hand Expression section of the Where Clause Expression pane, enter "50".
9. Note: You must use the quotes around 50. That is, enter "50", not 50.

10. From the Join Type field select the AND option.
11. The Join Type determines how the conditions that make up where clause are evaluated during run time.

12. Click Add. The second condition is added to the where clause of the for loop.
13. Save your work. This step completes the creation of the following where clause:
14. where (data($availRequest/ns1:widgetId) = data($priceRequest/ns0:widgetId)
and data($availRequest/ns1:requestedQuanity) < "50") #### To Test the XQuery For information about testing XQuery transformations, see Testing XQuery Files. Specifically for this example, complete the following steps to set up the test to test that the XQuery works when both the constraints you configured in Step 5. Add a Constraint With Multiple Conditions are met: 1. In the Source Data pane in the Test view, select priceQuote in the Source variable field and click Generate Data. 2. Note the value of the widgetId element in the test XML 3. <ns0:widgetId>value</ns0:widgetId> 4. In the Source Data pane, select availRequest in the Source variable field and click Generate Data. 5. Note the value of the widgetId element in the test XML. Edit the value to match the value displayed in the priceQuote test XML file (step 2). 6. <ns0:widgetId>value</ns0:widgetId> 7. Locate the requestedQuanity element and edit the value to a value less than 50. For example: 8. <ns0:requestedQuanity>25</ns0:requestedQuanity> 9. In the Result Data pane, click Test XQuery and view the results of the XQuery in the Result Data pane. # Using the Union Option of the Constraints Tab In this example, you will use the Union option in the Constraints tab to construct an XQuery that maps data of the same type into larger sets of data. #### To Combine Sets of Data of the Same Type 1. Launch Eclipse and navigate to the Sample Project. 2. Right-click the XQuery Transformation folder. 3. Choose New—>XQuery Transformation. 4. Verify the parent folder. 5. In this example the parent folder is Samples/XQueryTransformations. 6. In the Filename path, enter union. 7. Select the following source file (select PO.xsd\purchase-order twice): 8. PO.xsd\purchase-order PO.xsd\purchase-order 9. Select the following target schema and element: 10. Order.xsd\order After selecting the source and target data, the union.xq file is created. 11. Select the Source elements described in the following table and drag them to the Target pane. 12.  Source Element Links to.... Target Element purchase-order\line-items\line-item order\items\item purchase-order1\line-items\line-item order\items\item The following graphic displays the Design tab of the linking. Figure 3-8 Creating a Union [ 13. Verify that the link between the $purchase-order/line-items/line-item (repeating element in the Source pane) and the order/items/item (repeating element in the Target pane) is selected before you proceed.
14. In the Constraint Type pane, select Union.
15. Because the two structural links have the union constraint applied to them, a set of implied data links between the second set of sub-elements is generated as shown in Figure 3-9.

As shown in the following figure, the solid gray lines represents implied links that were created by selecting union in the Constraints Tab.

# Creating a Transformation Between a Repeating Source and Non-Repeating Target

This example shows how to map a repeating element a non-repeating single element. In this example, you will create a transformation that takes the value of a repeating XML element (defined by a source schema) and maps it to a single element in a target XML document (defined in a target schema) as shown in the following figure.

Figure 3-10 Repeating Source Group to Non-Repeating Target Element

#### To Create a Map Between a Repeating Source Group and a Non-Repeating Target Element

1. Launch Eclipse and navigate to the Sample Project.
2. Right-click the XQuery Transformation folder.
3. Choose New—>XQuery Transformation.
4. Verify the parent folder.
5. In this example the parent folder is Samples/XQueryTransformations

6. In the Filename field, enter repeatToNonRepeat.
7. Select the following source file:
8. Dates.xsd\dates 
9. Select the following target schema and element:
10. PODate.xsd\PODate
11. In the Source pane, select the repeating element (dates/date) and drag it the first single element (PODate/billing-date) in the Target pane.
12. Keep this link selected for the next step.

13. Select the Constraints tab.
14. From the Source pane, select the dates/date/type node and drop it into the Left Hand Expression pane of the Where Clause Expression in the Constraints tab.
15. Select the operator: =
16. In the Right Hand Expression pane of the Where Clause Expression in the Constraints tab, enter the string "BILLING".
17. Use quotes when entering a date string, as shown in the following figure.

Figure 3-11 Use Quotes when Entering String Functions

19. The constraint created in the preceding steps specifies that the value of the dates/date/type element in an XML document is compared to the value "BILLING".

In the next steps, you will add XQuery code to the for loop to return specified data when the value of the dates/date/type element equals "BILLING".

20. In the Source pane, select the dates/date/value element and drag to the PODate/billing-date element in the Target pane.
21. A data link is created.

During run time, this data link will return the value of datesDoc/date/value as the value of billing-date if the constraint: data($date/ns0:type) = "BILLING" evaluates to true. 22. In the Source pane, select the repeating element (dates/date) and drag it the second single element (PODate/delivery-date) in the Target pane. 23. A dashed line linking the two elements is displayed. Keep this link selected for the next step. 24. From the Source pane select the dates/date/type node and drop it into the Left Hand Expression pane of the Where Clause Expression in the Constraints tab. 25. Select the operator: = 26. In the Right Hand Expression pane of the Where Clause Expression in the Constraints tab, enter the string "DELIVERY". The next step will add the XQuery code to return data if during run time the constraint is equal to true. 27. Note: Use quotes when entering string values. 28. Click Add. 29. During run time, the constraint created in step, tests if datesDoc/date/type is equal to the string: "DELIVERY". 30. In the Source pane, select the datesDoc/date/value element and drag it the PODate/delivery-date element in the Target pane. 31. A data link is created. During run time, this data link will return the value of dates/date/value as the delivery-date if the constraint: data($date/ns0:type) = "DELIVERY" evaluates to true.

# Creating a Transformation Between a Non-Repeating Source and Repeating Target

This example shows how to map a a non-repeating element to a a repeating element. In this example, you will create a transformation that during run time will take a single source element and maps it to repeating target element as shown in the following figure.

Figure 3-12 Non-Repeating Source Element to Repeating Target Group

#### To create and a Non-Repeating Source Element and a Repeating Target Group

1. Launch Eclipse and navigate to the Sample Project.
2. Right-click the XQuery Transformation folder.
3. Choose New—>XQuery Transformation.
4. Verify the parent folder.
5. In this example the parent folder is Samples/XQueryTransformations.

6. In the Filename path, enter nonRepeatToRepeat.
7. Select the following source file:
8. PODate.xsd\PODate
9. Select the following target schema and element:
10. Dates.xsd\dates

After selecting the source and target data, the nonRepeatToRepeat.xq file is created.

11. Select the Source elements described in the following table. Drag them from the Source pane to the Target pane.
12.  Source Pane Links to.... Target Pane pODate/billing-date dates/date pODate/delivery-date dates/date

The following XQuery code is generated:

<ns1:dates>{	for $PODate in$PODateDoc/ns0:billing-date union $PODateDoc/ns0:delivery-date return <ns1:date/>}</ns1:dates> During run time, the for loop in the preceding XQuery code is executed twice. The first time the for loop is run, the iteration variable $PODate is equal to the first element in the union: $PODateDoc/ns0:billing-date and the second time the for loop is run the iteration variable $PODate is equal to the second element in the union: $PODateDoc/ns0:delivery-date. The XML data returned by the preceding query returns two empty elements: <ns1:date/>. The following steps will add the XQuery code to return the billing and delivery dates to the query. 13. In the Source pane, select the pODate/billing-date element and drag it the dates/value element in the Target pane. 14. Two data links are created as shown in the following figure. Figure 3-13 Creating a Union for Structural Links The structural links (pODate/billing-date to dates/date) and (pODate/delivery-date dates/date) are joined when you created the link from the pODate/billing-date element to the dates/value element (dates/value), a second data link between the pODate/delivery-date element and dates/value element is automatically created. 15. In the Source pane, select the pODate/billing-date element and drag it the dates/type element in the Target pane. 16. Two data links are created. Keep the pODate/billing-date to dates/type link selected for the next step. 17. Select the Target Expression tab. 18. Select the If Then Else option. 19. The XQuery if-then-else construct is added to the link. For example, the following XQuery source code segment for the link is replaced: data($PODate)

By the following XQuery source code segment for the link:

if (xf:boolean("true")) then	data($PODate)else () 20. Click the If Condition. 21. The If Condition pane is displayed. 22. In this step, you add a condition to the if section of the if-then-else: 1. Select the Expression Functions view. 2. Expand XQuery Functions—>Node Functions. 3. Select the local-name function and drag it into the Left Hand Expression pane of the If Condition pane. Leave the $node-var argument selected.
4. Select the Expression Variables view.
5. From the Source, drag-and-drop the pODate Structural Link variable over the $node-var argument of the local-name function in the If Condition pane. 6. Select the operator: = 7. In the Right Hand Expression section of the If Condition pane, enter "BILLING DATE" then click Add. 8. Note: Use quotes when entering string values. As shown in the following figure, the condition is added to the if section of the if-then-else. Figure 3-14 If Condition for Billing Date 23. Click the Then Expression. 24. In the Then Expression section, replace the existing text with the string: "BILLING" 25. Replace the following text: data($PODate)

With the following string:

"BILLING"

Note: You must use quotes around the "BILLING" string.

26. Click Apply.
27. Click the Else Expression.
28. In the Else Expression section, enter the string "DELIVERY"
29. Note: You must enter quotes around the "DELIVERY" string.

30. Click Apply.
31. In the Expression Structure pane, click If Then Else.
32. The XQuery code is displayed in the Expression Structure pane, as shown in the following figure.

Figure 3-15 Viewing the If-Then-Else Sample Results

# Creating a Nested If-Then-Else Expression

In this example, you will be creating an XQuery transformation that calculates price based on a widget ID and state tax rate. Using the XQuery Mapper, you will create an expression structure that represents the following if-then-else logic:

• If the widget ID is between 0-200, then the price is $10.00 • Else if the widget ID is between 201-400, then price is$20.00

#### To Create the First If Expression

1. Click the Target Expression Tab.
2. Choose the Expression Type If Then Else.
3. In the Expression Structure pane, verify that the If Condition is highlighted.

4. From the Source pane, drag and drop the widgetID element to the Left Hand Expression pane.
5. Select the operator: <
6. In the Right Hand Expression pane, enter "0".
7. Note: Use quotes when entering a numeric value.

9. Select the operator: <=
10. In the Right Hand Expression pane, enter "200".
11. Note: Use quotes when entering a numeric value.

13. Click the Then Expression.
14. In the Edit Then Expression pane, enter "$10.00". 15. Note: Delete existing data in the Edit Then Expression pane. Use quotes when entering "$10.00".

16. Click Apply.
17. In the Expression Structure pane highlight If Then Else.
18. Validate that your if then statement appears as shown in the following:

Figure 3-16 If Then Expression Structure

## Step 3. Create First Nested If-Then-Else Condition

In this step, you will be creating a nested If expression that states: If the widget ID is between 201-400, then the price is $20.00. To accomplish this, you will insert a nested If-Then-Else inside the Else Expression created in Step 2. Create First If Condition. #### To Create the First Nested If-Then-Else Condition 1. In the Expression Structure pane select, Else Expression. 2. Right-click and choose Insert Nested If-Then-Else. 3. In the Nested If-Then-Else expression, highlight the If Condition. 4. From the Source pane, drag and drop the widgetID element to the Left Hand Expression pane. 5. Select the operator: < 6. In the Right Hand Expression pane, enter "201". 7. Note: Use quotes when entering a numeric value. 8. Click Add. 9. Select the operator: <= 10. In the Right Hand Expression pane, enter "400". 11. Note: Use quotes when entering a numeric value. 12. Click Add. 13. Click the Then Expression. 14. In the Edit Then Expression pane, enter "$20.00".
15. Note: Use quotes when entering a numeric value.

16. Click Apply.
17. In the Expression Structure pane highlight If Then Else.
18. Validate that your statement appears as shown in the following:

Figure 3-17 Nested If Then Else Expression Structure

## Step 4. Create Second Nested If-Then-Else Condition

In this step, you will be creating an If expression that states: If the widget ID is between 401-600, then the price is $30.00. To accomplish this, you will insert a nested If-Then-Else inside the Then Expression created in Step 3. Create First Nested If-Then-Else Condition. #### To Create the Second Nested If-Then-Else Condition 1. In the Expression Structure pane select, Else Expression. 2. Note: Be sure to select the Else Expression created in Step 3. Create First Nested If-Then-Else Condition 3. Right-click and choose Insert Nested If-Then-Else. 4. From the Source pane, drag and drop the widgetID element to the Left Hand Expression pane. 5. In the Nested If-Then-Else expression, highlight the If Condition. 6. Select the operator: < 7. In the Right Hand Expression pane, enter "401". 8. Note: Use quotes when entering a numeric value. 9. Click Add. 10. Select the operator: <= 11. In the Right Hand Expression pane, enter "600". 12. Note: Use quotes when entering a numeric value. 13. Click Add. 14. Click the Then Expression. 15. In the Edit Then Expression pane, enter "$30.00".
16. Note: Use quotes when entering a numeric value.

17. Click Apply.
18. In the Expression Structure pane highlight If Then Else.
19. Validate that your statement appears as shown in the following figure.

Figure 3-18 Second Nested If-Then-Else Expression

# Using Recursive Schemas

This example shows how to create a map with schemas that have recursive elements. A recursive element contains a child element of the same type as the parent as shown in the following figure. In this example, the product element is a recursive element because it is of type: productType and productType contains a child-product element which is also of type productType (productType refers to itself).

Figure 3-19 Recursive Schemas Example

#### To Create a Transformation Using Recursive Schemas

1. Launch Eclipse and navigate to the Sample Project.
2. Right-click the XQuery Transformation folder.
3. Choose New—>XQuery Transformation.
4. Verify the parent folder.
5. In this example the parent folder is, Samples/XQueryTransformations.

6. In the Filename path, enter recursive.
7. Select the following source file:
8. SupplierAcme.xsd\supplier-acme
9. Select the following target schema and element:
10. Product.xsd\product

After selecting the source and target data, the recursive.xq file is created.

11. Select the Source elements described in the following table. Drag them from the Source pane to the Target pane.
12.  Source Pane Links to.... Target Pane supplier-acme\part-description-00100 product\part-description supplier-acme\part-description-00101 product\child-produc\part-description supplier-acme\part-description-00101 product\child-product\child-product\part-description

As shown in the following figure, the product element contains the recursive child-product element.

Figure 3-20 Mapping Recursive Elements

# Creating Group By Key Fields XQuery Constructs

You can use the Group by Key Fields functionality to group data based on one or more key values. However, the Group By functionality is not supported graphically in the XQuery Mapper and there is no representation of the XQuery in the Mapper's Design View. Therefore you must write the Group By XQuery construct in the XQuery Mapper's Source View.

In this example, the input-warehouse-id and the input-location-desc elements are used as the key fields to group the data in the output document:

• The first and third instances of the input-line-item repeating element in the input XML document (see Listing 3-2) contain the same values for the input-warehouse-id element and the input-location-desc elements. (The values are Warehouse1 and Location1 respectively.)
• As a result of the XQuery described in this example, the first and third instances of the line items are grouped. That is, these elements are grouped with the Warehouse1 and Location1 keys in the target (output) document (see Listing 3-3).

Listing 3-2 Example Input XML Document

<input-warehouse-inventory xmlns="http://www.creditpo.org/repkeyin">
<input-line-item>
<input-warehouse-id>Warehouse1</input-warehouse-id>									
    <input-location-desc>Location1</input-location-desc>
<input-part-no>1</input-part-no>
<input-quantity>10</input-quantity> 
</input-line-item>
<input-line-item>
<input-warehouse-id>Warehouse2</input-warehouse-id>
    <input-location-desc>Location2</input-location-desc>
<input-part-no>2</input-part-no>
<input-quantity>20</input-quantity>
</input-line-item>
<input-line-item>
<input-warehouse-id>Warehouse1</input-warehouse-id>									
    <input-location-desc>Location1</input-location-desc>
<input-part-no>3</input-part-no>
<input-quantity>30</input-quantity>  
</input-line-item>            
</input-warehouse-inventory>

Listing 3-3 Example Output Document

<ns0:output-inventory xmlns:ns0="http://www.creditpo.org/repkeyout";>
  <ns0:output-warehouse-inventory>
    <ns0:output-warehouse-id>Warehouse1</ns0:output-warehouse-id>
    <ns0:output-location-desc>Location1</ns0:output-location-desc>
    <ns0:output-line-item>
      <ns0:output-part-no>1</ns0:output-part-no>
      <ns0:output-quantity>10</ns0:output-quantity>
    </ns0:output-line-item>
    <ns0:output-line-item>
      <ns0:output-part-no>3</ns0:output-part-no>
      <ns0:output-quantity>30</ns0:output-quantity>
    </ns0:output-line-item>
  </ns0:output-warehouse-inventory>
  <ns0:output-warehouse-inventory>
    <ns0:output-warehouse-id>Warehouse2</ns0:output-warehouse-id>
    <ns0:output-location-desc>Location2</ns0:output-location-desc>
    <ns0:output-line-item>
      <ns0:output-part-no>2</ns0:output-part-no>
      <ns0:output-quantity>20</ns0:output-quantity>
    </ns0:output-line-item>
  </ns0:output-warehouse-inventory>
</ns0:output-inventory>

#### To Create a Group By XQuery Construct

1. Launch Eclipse and navigate to the Sample Project.
2. Right-click the XQuery Transformation folder.
3. Choose New—>XQuery Transformation.
4. Verify the parent folder.is Samples/XQueryTransformations.
5. In the Filename path, enter groupby.
6. Select the following source file and element:
7. regroupKeyFldIn.xsd\input-warehouse-inventory
8. Select the following target schema and element:
9. regroupKeyFldOut.xsd\output-inventory

After selecting the source and target data, the groupby.xq file is created.

Note: At this point, if you were working in the XQuery Mapper in the BEA WebLogic Integration 8.1 release, you could highlight the link between input-warehouse-id and output-warehouse-inventory and choose the Constraint Type Group by Key Fields graphically. However, the Design View representation for this is not available in this release. However, the Group by type is supported—you must however write the XQuery for it as described in the following steps.

10. Click the Source tab.
11. Replace the existing XQuery code with the following code.

Listing 1 XQuery for Group By Key Construct

declare namespace ns0 = "http://www.creditpo.org/repkeyin";
declare namespace ns1 = "http://www.creditpo.org/repkeyout";
declare function Regrouping($input-warehouse-inventory as element(ns0:input-warehouse-inventory))  as element(ns1:output-inventory) {  <ns1:output-inventory>  {  for$input-line-item in $input-warehouse-inventory/ns0:input-line-item  group$input-line-item as $group by $input-line-item/ns0:input-warehouse-id as $key0, $input-line-item/ns0:input-location-desc as $key1  return  <ns1:output-warehouse-inventory>  <ns1:output-warehouse-id>{ data($key0) }</ns1:output-warehouse-id>
                        <ns1:output-location-desc>{ data($key1) }</ns1:output-location-desc>  {  for$group0 in $group return  <ns1:output-line-item>  <ns1:output-part-no>{ xs:byte( data($group0/ns0:input-part-no) ) }</ns1:output-part-no>
	                            <ns1:output-quantity>{ xs:byte( data($group0/ns0:input-quantity) ) }</ns1:output-quantity>  </ns1:output-line-item>  }  </ns1:output-warehouse-inventory>  }  </ns1:output-inventory>  }; declare variable$input-warehouse-inventory as element(ns0:input-warehouse-inventory) external;
Regrouping(\$input-warehouse-inventory)

The changes are not visible in the Design view. Use the following procedure to Test the XQuery.

#### Testing the Group By Key Fields XQuery Construct

1. With the groupby.xq file open in the Source view, click the Test tab.
2. In the Source Data tab, click Import . . .
3. Import the Regrouping.xml file provided in the Sample project. That is, import Samples/XML/Regrouping.xml.

In the Result Data pane, click Test XQuery. The resulting data is displayed in the Result Data tab. It displays the line items grouped by key fields similar to that shown in Listing 3-3 (input-warehouse-id is the first key and input-location-desc is the second).

# Testing XQuery Files

After creating an XQuery transformation in the Design tab, you can test it using the Test tab. When testing XQuery transformations, you can see if the expected XML or Non-XML output is properly generated. You can use the auto-generated XML files or you can use your own custom XML and Non-XML testing files. For more information about XQuery testing, see Testing Map Transformations.

This sections contains the following topics:

#### Using Auto-Generated XML Files for Testing

The XQuery Mapper automatically generates XML files for testing purposes. Non-XML test files are not automatically generated. For more information, see Using Custom MFL (Non-XML) Files for Testing.

1. Open the XQuery file for which you are testing.
2. Click the Test tab.
3. A test XML file becomes available in the Source Variable drop-down menu. The test XML file is based on the source schema This file is not automatically saved, and therefore must be save manually using the Export icon.

4. In the Source Variable drop-down menu, select the source test XML file.
5. Optionally, in the Source Data pane, you can select from the following options:
• Generate Data: Select to refresh or regenerate the auto-generated XML file.
• Import: Select if you are importing custom test XML files (not using the auto-generated XML files.) For more information, see Using Custom XML Files for Testing.
• Export: Select to save the auto-generated test XML data.
• Auto Validate: Select to have source test XML files validated against source schema. The Auto Validate option can be permanently turned on, or you can select it on a case-by-case basis.
• Note: You can also manually edit the test XML by clicking the Source tab.

6. In the Result Data pane, click Test XQuery.
7. The XQuery runs with the source data to generate the results. The results are displayed in the Result Data pane. If an error has occurred, you will see an error message that indicates the problem.

8. Optionally, in the Result Data pane, you can select from the following options:
• Export: Select to save the test XML data.
• Validate: Validates test XML against target schema. If expected element or attribute is missing, then an error will appear.

#### Using Custom XML Files for Testing

If the auto-generated XML files do not meet your business requirements, you can import custom XML files.

1. Open the XQuery file for which you are testing.
2. Click the Test tab.
3. In the Source Data pane, choose Import.
4. The Import XML File dialog box opens. Use this to locate the XML files used for testing purposes. After selecting the appropriate file it becomes visible in the Source Data pane.

You can also cut and paste test data into the Source Data pane.

5. After importing the test file, you have the following options:
• Generate Data: Select to refresh or regenerate the XML file.
• Export: Select to save.
• Auto Validate: Select to have source test XML files validated against source schema. The Auto Validate option can be permanently turned on, or it can be selected on a case-by-case basis.
6. In the Result Data pane, select Test XQuery.
7. The Source XML is tested against the target XML and the results are displayed.

8. Optionally, in the Result Data pane, you can select from the following options:
• Export: Select to re-save the imported test XML data.
• Validate: Validates the test XML against the target schema. If an expected element or attribute is missing, then an error is displayed.

#### Using Custom MFL (Non-XML) Files for Testing

The XQuery Mapper does not automatically generate Non-XML files. Therefore, when testing Non-XML schema, you must import custom Non-XML test data.

1. Open the XQuery file you want to test.
2. Click the Test tab.
3. In the Source Data pane, choose Import.
4. The Import XML File dialog box is displayed. Use this to locate Non-XML files used for testing.

Note: The Files of type drop-down menu must be changed to *.*

5. After importing the test file, you have the following options:
• Import: Select to import another MFL test file.
• Export: Select to save the MFL test data.
6. In the Result Data pane, select Test XQuery.
7. The Source MFL is tested against the target MFL and the results are displayed.

8. Optionally, in the Result Data pane, you can select the following:
9. Export: Select to save the test data.