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
- Mapping Repeating Elements and Creating a Join
- Using the Union Option of the Constraints Tab
- Creating a Transformation Between a Repeating Source and Non-Repeating Target
- Creating a Transformation Between a Non-Repeating Source and Repeating Target
- Creating a Nested If-Then-Else Expression
- Using Recursive Schemas
- Creating Group By Key Fields XQuery Constructs
- Testing XQuery Files
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
- For more information about launching Eclipse, see Launching the XQuery Mapper.
- For more information about opening the Sample Project, see XQuery Mapper Sample Project.
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.
CustInfo.xsd\customer
PO.xsd\purchase-order
POCustInfo.xsd\purchase-order
|
|
||
|
|
||
|
|
||
|
|
||
|
|
||
|
|
||
|
|
To learn more about links, see Understanding Design View Graphical Representations.
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
- Step 2. Add a Conditional Constraint
- Step 3. Add Links to Populate Empty Element
- Step 4. Calculate Total Cost
- Step 5. Add a Constraint With Multiple Conditions
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.
- For more information about launching Eclipse, see Launching the XQuery Mapper.
- For more information about opening the Sample Project, see XQuery Mapper Sample Project.
After selecting the source and target data, the
Join.xqfile is created in the Samples/XQueryTransformations folder.- 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.
PriceQuote.xsd\priceQuote
PriceQuote.xsd\taxRate
AvailQuote.xsd\availRequest
Quote.xsd\quote
|
|
||
|
|
||
|
|
||
|
|
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.
To Add a Constraint
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
- 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>
priceRequests/priceRequest/widgetId
availRequest/widgetId
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
Step 3. Add Links to Populate Empty Element
Use the procedure in this section to add data links to populate the quoteResponse element.
To Populate the QuoteResponse Element
- 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.
|
|
||
|
|
||
|
|
||
|
|
||
|
|
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
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
};
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.)
calculateTotalPrice($taxRate,$availRequest/ns1:requestedQuanity,$priceRequest/ns0:price)
Figure 3-6 General Expression for totalCost Element
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
-
The single condition that makes up the
whereclause is displayed in Where Clause Expression pane of the Constraints tab.data($priceRequest/ns0:widgetId) = data($availRequest/ns1:widgetId) - Select the availRequest/requestedQuanity node and drag-and-drop it into the Left Hand Expression section of the Where Clause Expression pane to create:
The Join Type determines how the conditions that make up
whereclause are evaluated during run time.
data($availRequest/ns1:requestedQuanity)
Note: You must use the quotes around 50. That is, enter "50", not 50.
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:
- In the Source Data pane in the Test view, select priceQuote in the Source variable field and click Generate Data.
- 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).
<ns0:widgetId>value</ns0:widgetId>
<ns0:widgetId>value</ns0:widgetId>
<ns0:requestedQuanity>25</ns0:requestedQuanity>
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
- For more information about launching Eclipse, see Launching the XQuery Mapper.
- For more information about opening the Sample Project, see XQuery Mapper Sample Project.
- Verify that the link between the
$purchase-order/line-items/line-item(repeating element in the Source pane) and theorder/items/item(repeating element in the Target pane) is selected before you proceed. 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.
Figure 3-9 Creating Implied Links
PO.xsd\purchase-order
PO.xsd\purchase-order
Order.xsd\order
|
|
||
|
|
[ 
For more information about testing XQuery transformations see Testing XQuery Files.
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
- For more information about launching Eclipse, see Launching the XQuery Mapper.
- For more information about opening the Sample Project, see XQuery Mapper Sample Project.
- In the Source pane, select the repeating element (
dates/date) and drag it the first single element (PODate/billing-date) in the Target pane. - From the Source pane, select the
dates/date/typenode and drop it into the Left Hand Expression pane of the Where Clause Expression in the Constraints tab. - In the Right Hand Expression pane of the Where Clause Expression in the Constraints tab, enter the string
"BILLING". Use quotes when entering a date string, as shown in the following figure.
Figure 3-11 Use Quotes when Entering String Functions
The constraint created in the preceding steps specifies that the value of the
dates/date/typeelement 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/typeelement equals "BILLING".- In the Source pane, select the
dates/date/valueelement and drag to thePODate/billing-dateelement in the Target pane. -
During run time, this data link will return the value of
datesDoc/date/valueas the value ofbilling-dateif the constraint:data($date/ns0:type) = "BILLING"evaluates to true. - In the Source pane, select the repeating element (
dates/date) and drag it the second single element (PODate/delivery-date) in the Target pane. - From the Source pane select the
dates/date/typenode and drop it into the Left Hand Expression pane of the Where Clause Expression in the Constraints tab. - 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.
Dates.xsd\dates
PODate.xsd\PODate
For more information about testing XQuery files, see Testing XQuery Files.
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
- For more information about launching Eclipse, see Launching the XQuery Mapper.
- For more information about opening the Sample Project, see XQuery Mapper Sample Project.
- Select the Source elements described in the following table. Drag them from the Source pane to the Target pane.
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
forloop in the preceding XQuery code is executed twice. The first time theforloop is run, the iteration variable$PODateis equal to the first element in the union:$PODateDoc/ns0:billing-dateand the second time theforloop is run the iteration variable$PODateis 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.
- In the Source pane, select the
pODate/billing-dateelement and drag it thedates/valueelement in the Target pane. 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-datetodates/date) and (pODate/delivery-datedates/date) are joined when you created the link from thepODate/billing-dateelement to thedates/valueelement (dates/value), a second data link between thepODate/delivery-dateelement anddates/valueelement is automatically created.- In the Source pane, select the
pODate/billing-dateelement and drag it thedates/typeelement in the Target pane. 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
()- Select the local-name function and drag it into the Left Hand Expression pane of the If Condition pane. Leave the
$node-varargument selected.
PODate.xsd\PODate
Dates.xsd\dates
For more information about testing XQuery files, seeTesting XQuery Files.
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
- Else If the widget ID is between 401-600, then price is $30.00
This example includes the following steps:
- Step 1. Create New XQuery Transformation
- Step 2. Create First If Condition
- Step 3. Create First Nested If-Then-Else Condition
- Step 4. Create Second Nested If-Then-Else Condition
Step 1. Create New XQuery Transformation
In this step, you will create a new XQuery transformation using the PurchaseAgree.xsd and Supplier.xsd.
To Create a New XQuery Transformation
- For more information about launching Eclipse, see Launching the XQuery Mapper.
- For more information about opening the Sample Project, see XQuery Mapper Sample Project.
Supplier.xsd\Supplier
PurchaseAgree.xsd\PurchaseOrder
|
|
||
|
|
Step 2. Create First If Condition
In this step, you will be creating an If expression that states: If the widget ID is between 0-200, then the price is $10.00.
To Create the First If Expression
Note: Use quotes when entering a numeric value.
Note: Use quotes when entering a numeric value.
Note: Delete existing data in the Edit Then Expression pane. Use quotes when entering "$10.00".
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
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
Note: Be sure to select the Else Expression created in Step 3. Create First Nested If-Then-Else Condition
Note: Use quotes when entering a numeric value.
For more information about testing XQuery files, seeTesting XQuery Files.
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
- For more information about launching Eclipse, see Launching the XQuery Mapper.
- For more information about opening the Sample Project, see XQuery Mapper Sample Project.
- Select the Source elements described in the following table. Drag them from the Source pane to the Target pane.
SupplierAcme.xsd\supplier-acme
Product.xsd\product
|
|
||
|
|
||
|
|
As shown in the following figure, the product element contains the recursive child-product element.
Figure 3-20 Mapping Recursive Elements
For more information about testing XQuery files, seeTesting XQuery Files.
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-idelement and theinput-location-descelements. (The values areWarehouse1andLocation1respectively.) - 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
Warehouse1andLocation1keys 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
- For more information about launching Eclipse, see Launching the XQuery Mapper.
- For more information about opening the Sample Project, see XQuery Mapper Sample Project.
After selecting the source and target data, the
groupby.xqfile 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-idandoutput-warehouse-inventoryand 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.
regroupKeyFldIn.xsd\input-warehouse-inventory
regroupKeyFldOut.xsd\output-inventory
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
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
- Using Custom XML Files for Testing
- Using Custom MFL (Non-XML) Files for Testing
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.
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.
- 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.
Using Custom XML Files for Testing
If the auto-generated XML files do not meet your business requirements, you can import custom XML files.
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.
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.
