> Transforming Data Using XQuery Mapper > Examples: Data Transformation Using XQuery Mapper

Transforming Data Using XQuery Mapper

     Previous  Next    Open TOC in new window    View as PDF - New Window  Get Adobe Reader - New Window
Content starts here

Examples: Data Transformation Using XQuery Mapper

The examples described here are based on the sample project that is included in the product. For information about opening the sample project, see Creating the XQuery Mapper Sample Project.

Examples are provided for the following scenarios:

Combining Data from Different Schemas

You can use XQuery Mapper to combine content from two different schemas, as shown in the following figure.

Figure 3-1 Combining Data From Different Schemas

Combining Data From Different Schemas

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

  1. Launch WorkSpace Studio and open the sample project.
  2. Right-click the XQuery Transformations folder.
  3. Choose New > XQuery Transformation.
  4. Verify the name of the parent folder. For this example, the parent folder is /XQuery Transformation/XQueryTransformations.
  5. Enter combineData as the file name and click Next.
  6. Select the following source elements and click Next:
    • CustInfo.xsd\customer
    • PO.xsd\purchase-order
  7. Select POCustInfo.xsd\purchase-order as the target element and click Finish.

    8. The combineData.xq file is created in the /XQuery Transformation/XQueryTransformations folder.

    The source and target elements that you selected are displayed in the Design view, as shown in the following figure.

    Figure 3-2 Design View of XQuery Transformation


    Design View of XQuery Transformation

  8. Create links between the following source and target elements by dragging elements from the Source pane to the Target pane.
    Source Element
    		 
    Target Element
    customer1

    Mapping Recursive Elements
    purchase-order\customer
    customer1\customer-id

    Mapping Recursive Elements
    purchase-order\customer\customer-id
    customer1\customer-name

    Mapping Recursive Elements
    purchase-order\customer\customer-name
    customer1\customer-address

    Mapping Recursive Elements
    purchase-order\customer\customer-address
    purchase_order1\line-items\line-item

    Mapping Recursive Elements
    purchase-order\line-items\line-item
    purchase-order1\line-items\line-item\part-no

    Mapping Recursive Elements
    purchase-order\line-items\line-item\part-no
    purchase-order1\line-items\line-item\quantity

    Mapping Recursive Elements
    purchase-order\line-items\line-item\quantity
    9. Note: Dotted lines represent Structural links, which are created between parent structures and do not map data directly.
    Note: Solid lines represent Data links, which convert the value of the source node directly to the value of the target node.
    Note: For more information, see Graphical Features in Design View.

    The links between the Source and Target elements are displayed, as shown in the following figure.

    Figure 3-3 Data Transformation in Design View


    Data Transformation in Design View

  9. Save the changes.

For information about testing XQuery transformations, see Testing Data Transformations.

Mapping Repeating Elements and Creating Joins

You can join data from XML files that are valid against different schemas (in this example, PriceQuote.xsd, AvailableQuote.xsd, and taxrate.xsd), and create an XML file that is valid against a single schema: Quote.xsd.

This example includes the following steps:

Step 1. Create an XQuery File

In this step, we create an XQuery transformation by using the AvailQuote.xsd, PriceQuote.xsd, and taxrate.xsd schemas. Then, we map several priceQuote and availRequest source elements to corresponding target elements.

  1. Launch WorkSpace Studio and open the sample project.
  2. Right-click the XQuery Transformations folder.
  3. Choose New > XQuery Transformation.
  4. Verify the name of the parent folder. For this example, the parent folder is /XQuery Transformation/XQueryTransformations.
  5. Enter Join as the file name and click Next.
  6. Select the following source elements and click Next:
    • PriceQuote.xsd\priceQuote
    • PriceQuote.xsd\taxRate
    • AvailQuote.xsd\availRequest
  7. Select Quote.xsd\quote as the target element and click Finish.

    8. The Join.xq file is created in the XQueryTransformation/XQueryTransformations folder.

  8. Create links between the following source and target elements by dragging elements from the Source pane to the Target pane.
    Source Element
    Target Element
    priceQuote1\customerName
    quote\name
    priceQuote1\shipAddress\street
    quote\address
    priceQuote1\shipAddress\city
    quote\address
    priceQuote1\shipAddress\state
    quote\address
    priceQuote1\shipAddress\zip
    quote\address
    priceQuote1\priceRequests\priceRequest
    quote\quoteResponse
    availRequest1
    quote\quoteResponse

    9. The links are displayed, as shown in the following figure.

    Figure 3-4 Data Transformation in Design View


    Data Transformation in Design View

  9. Save the changes.

Step 2. Add a Constraint

The priceQuote/priceRequests and availRequest source elements share the common element widgetId. In this step, we add a constraint that if widgetId of the availRequest schema is equal to widgetId of the priceQuote/priceRequests element, then the query must return the target repeating element, quoteResponse.

  1. Open Join.xq in the Design view.
  2. Drag the priceQuote1/priceRequests/priceRequest/widgetId element from the Source pane and drop it on the availRequest1/widgetId element of the Source pane.

    3. A connecting line appears between the two widgetId nodes in the Source pane, as shown in the following figure.

    Figure 3-5 Adding a Constraint


    Adding a Constraint

  3. Save the changes.
  4. View the changes in the Source view.

    5. The link between the two widgetId nodes is represented by a where clause within the for loop. The where clause specifies that the for loop must return the result of the expression only if the where clause is true. In this example, if widgetId of the availRequest element is equal to widgetId of the priceRequest element, the expression returns the XML data specified in the quoteResponse element.

    Note: You can also view the where clause in the Constraints view.

The quoteResponse element is currently empty. We add content to the element in the next step.

Step 3. Add Data to Return Element

In this step, we add data links in the quoteResponse target element.

Open Join.xq in the Design view and create links between the following source and target elements:

Source Element
Target Element
priceQuote1\priceRequests\priceRequest\widgetid
quote\quoteResponse\widgetid
priceQuote1\priceRequests\priceRequest\price
quote\quoteResponse\unitprice
availRequest1\requestedQuantity
quote\quoteResponse\requestedQuantity
availRequest1\quantityAvail
quote\quoteResponse\fillOrder
availRequest1\shipDate
quote\quoteResponse\shipDate

The links between the Source and Target elements are displayed, as shown in the following figure.

Figure 3-6 Adding Data in the QuoteResponse Element

Adding Data in the QuoteResponse Element

Step 4. Add Function to Calculate Value of Quote

In this step, we add a function to calculate the total value of the quote.

  1. Open the Join.xq file in Source view.
  2. Insert the following function declaration at any point in the source code between the namespace declarations and the Join function call. You can, for example, insert it just before the Join function declaration.
    3. Listing 3-1 calculateTotalPrice Function 
    declare function xf: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
    };
  3. Switch to the Design view.
    4. Note: Join.xq now includes two function declarations: calculateTotalPrice and Join. When more than one function exists in an XQuery 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.
  4. In the Target pane, select the totalCost node. Keep it selected for the next step.
  5. Select the Target Expression view and select the General option.
  6. Insert the following code in the General Expression pane.
    7. xf:calculateTotalPrice($taxRate1,$availRequest/ns1:requestedQuanity,$priceRequest/ns0:price)
  7. Click Apply.

    8. The expression is added to the totalCost element in the XQuery.

  8. Save the changes.

    9. The Design view shows the calculation for the totalCost target element.

    Figure 3-7 totalCost Calculation in Design View


    totalCost Calculation in Design View

Step 5. Add a Constraint with Multiple Conditions

You can create a constraint by using the Where Clause pane of the Constraints view to limit the target repeating elements that are returned by the XQuery. At run time, the for loop in the XQuery iterates over only those repeating elements that satisfy the where clause.

In this step, we add another condition (resulting in a complex condition) to the where clause of the for loop to further limit the data returned by the for loop.

  1. Open the join.xq file.
  2. In the Design view, select the link between the availRequest1 source element and the quote\quoteResponse target element.

    3. The single condition that makes up the where clause is displayed in Where Clause pane of the Constraints view. 

    data($priceRequest/ns0:widgetId) = data($availRequest/ns1:widgetId)
  3. Drag the availRequest1/requestedQuanity element from the Source pane and drop it in the Left Hand Expression area of the Where Clause pane.

    4. The left hand expression of the where clause is created as follows: 

    data($availRequest/ns1:requestedQuanity)
  4. Select the < operator.
  5. Remove the text in the Right Hand Expression area of the Where Clause pane, and enter “50”.
    6. Note: Enter the number 50 within quotation marks (“50”, not 50).
  6. From the Join Type field select the AND option.

    7. The Join Type determines how the conditions that make up the where clause are evaluated at run time.

  7. Click Add. The second condition is added to the where clause of the for loop.
  8. Save the changes.

    9. This step completes the creation of the following where clause.

    Listing 3-2 Where Clause 
    where (data($availRequest/ns1:widgetId) = data($priceRequest/ns0:widgetId)
    and data($availRequest/ns1:requestedQuanity) < "50")

Test the XQuery

Perform the following steps to verify that the XQuery works when both the conditions of the where clause you created are satisfied.

  1. Switch to the Test view.
  2. In the Source Data pane of the Test view, select priceQuote in the Source Variable field, and click the Generate Data icon.
  3. Note the value of the widgetId element in the test XML data.
    4. <ns0:widgetId>value</ns0:widgetId>
  4. In the Source Data pane, select availRequest in the Source Variable field, and click the Generate Data icon.
  5. Edit the value the value of the widgetId element in the test XML data to match the value displayed in the priceQuote test XML data.
    6. <ns0:widgetId>value</ns0:widgetId>
  6. Locate the requestedQuanity element and edit the value to a number less than 50.

    7. For example: <ns0:requestedQuanity>25</ns0:requestedQuanity>

  7. In the Result Data pane, click the Test XQuery icon and view the results of the XQuery.

Creating Unions

In this example, we use the Union option in the Constraints view to construct an XQuery that maps data of the same type into larger sets of data.

  1. Launch WorkSpace Studio and open the sample project.
  2. Right-click the XQuery Transformations folder.
  3. Choose New > XQuery Transformation.
  4. Verify the name of the parent folder. For this example, the parent folder is /XQuery Transformation/XQueryTransformations.
  5. Enter union as the file name and click Next.
  6. In the Source Types dialog box, select PO.xsd\purchase-order twice, and then click Next.
    7. Note: To add an element more than once, you must change the parameter name.
  7. Select Order.xsd\order as the target type and then click Finish.

    8. The union.xq file is created.

  8. Create links between the following source and target elements:
    Source Element
    Target Element
    purchase-order1\line-items\line-item
    order\items\item
    purchase-order2\line-items\line-item
    order\items\item

    9. The following figure shows how the links appear in the Design view.

    Figure 3-8 Creating a Union


    Creating a Union

  9. Select the link between the $purchase-order1/line-items/line-item source element and the order/items/item target element.
  10. In the Constraint Type pane of the Constraints view, select Union.
  11. Create links between the following source and target elements:
    Source Element
    Target Element
    purchase-order1\line-items\part-no
    order\items\item\part-number
    purchase-order1\line-items\quantity
    order\items\item\quantity-number
    12. Note: When you want to create links between source and target elements of the same name, you can use the Induce Map option instead of creating the links manually. For more information, see Right-Click Menu Options.

    Since the two structural links have a union constraint, a set of implied data links between the second set of subelements is generated as shown in Figure 3-9. The gray lines represent implied links that were created because Union was selected as the constraint type.

    Figure 3-9 Creating Implied Links


    Creating Implied Links

  12. Save the changes.

For information about testing XQuery transformations, see Testing Data Transformations.

Creating Repeating-Source to Nonrepeating-Target Transformations

In this example, we map a repeating source XML element to a nonrepeating target XML element.

The following figure depicts the transformations that we create in this example.

Figure 3-10 Repeating-Source– to– Nonrepeating Target Transformation

Repeating-Source– to– Nonrepeating Target Transformation

  1. Launch WorkSpace Studio and open the sample project.
  2. Right-click the XQuery Transformations folder.
  3. Choose New > XQuery Transformation.
  4. Verify the name of the parent folder. For this example, the parent folder is /XQuery Transformation/XQueryTransformations.
  5. Enter repeatToNonRepeat as the file name and click Next.
  6. Select Dates.xsd\dates as the source schema and click Next.
  7. Select PODate.xsd\PODate as the target schema and click Finish.

    8. The repeatToNonRepeat.xq file is created and displayed, as shown in the following figure.

    Figure 3-11 Repeating-Source– to– Nonrepeating-Target Data Transformation


    Repeating-Source– to– Nonrepeating-Target Data Transformation

  8. Create a link between the dates1/date repeating element in the Source pane and the PODate/billing-date element in the Target pane.

    9. Keep this link selected for the next step.

  9. Select the Constraints view.
  10. Drag the dates1/date/type element from the Source pane to the Left Hand Expression area of the Where Clause pane in the Constraints view.
  11. Select the = operator.
  12. In the Right Hand Expression area, enter "BILLING" (including the quotation marks), and click Add.
  13. Create a link between the dates1/date/value element in the Source pane and the PODate/billing-date element in the Target pane.

    14. The constraint that you created in the preceding steps specifies that the value of the dates1/date/type element in an XML document must be compared with the value "BILLING".

    At run time, if the value of the dates1/date/type element is "BILLING", the XQuery returns the value of dates1/date/value as the value of billing-date.

  14. Create a link between the dates1/date repeating element in the Source pane and PODate/delivery-date element in the Target pane.

    15. Keep this link selected for the next step.

  15. Drag the dates1/date/type element from the Source pane to the Left Hand Expression area of the Where Clause pane in the Constraints view.
  16. Select the = operator.
  17. In the Right Hand Expression area, enter "DELIVERY" (including the quotation marks), and click Add.
  18. Create a link between the dates1/date/value repeating element in the Source pane and PODate/delivery-date element in the Target pane.

    19. The constraint created in the preceding steps specifies that the value of the dates1/date/type element in an XML document must be compared to the value "DELIVERY".

    At run time, if the value of the dates1/date/type element is "DELIVERY", the XQuery returns the value of dates1/date/value as the value of delivery-date.

    Figure 3-12 Repeating-Source– to– Nonrepeating-Target Data Transformation


    Repeating-Source– to– Nonrepeating-Target Data Transformation

  19. Save the changes.

For information about testing XQuery files, see Testing Data Transformations.

Creating Nonrepeating-Source to Repeating-Target Transformation

In this example, we map a nonrepeating source element to a repeating target element.

The following figure depicts the transformations that we create in this example.

Figure 3-13 Nonrepeating-Source– to– Repeating-Target Transformation

Nonrepeating-Source– to– Repeating-Target Transformation

  1. Launch WorkSpace Studio and open the sample project.
  2. Right-click the XQuery Transformations folder.
  3. Choose New > XQuery Transformation.
  4. Verify the name of the parent folder. For this example, the parent folder is /XQuery Transformation/XQueryTransformations.
  5. Enter nonRepeatToRepeat as the file name and click Next.
  6. Select PODate.xsd\PODate as the source schema and click Next.
  7. Select Dates.xsd\dates as the target schema and click Finish.
  8. The nonRepeatToRepeat.xq file is created and displayed in the Design view.
  9. Create links between the following source and target elements:
    Source Pane
    Target Pane
    pODate1/billing-date
    dates/date
    pODate1/delivery-date
    dates/date

    10. The following listing shows the XQuery code that is generated.

    Listing 3-3 XQuery Code 
    <ns1:dates>
    {
      for $PODate in $PODate1/ns0:billing-date union $PODate1/ns0:delivery-date
      return
      <ns1:date/>
    }
    </ns1:dates>

    At run time, the for loop is executed twice. In the first execution, the iteration variable $PODate is equal to the first element in the union $PODate1/ns0:billing-date; in the second execution, $PODate is equal to $PODate1/ns0:delivery-date.

    The XQuery returns two empty XML elements with the tag <ns1:date/>.

    In the following steps, we add the XQuery code to return the billing and delivery dates to the query.

  10. Switch to the Design view.
  11. Create a link between the pODate1/billing-date source element and the dates/value target element in the Target pane.

    12. Two data links are created, as shown in the following figure.

    Figure 3-14 Creating a Union for Structural Links


    Creating a Union for Structural Links

    The following structural links were joined when we created the link from pODate1/billing-date to dates/value.

    • pODate1/billing-date to dates/date
    • pODate1/delivery-date to dates/date

      • A second data link between the pODate1/delivery-date element and dates/value element was created automatically.

  12. Create a link between the pODate1/billing-date source element and the dates/type target element.

    13. Two data links are created.

    Keep the pODate1/billing-date to dates/type link selected for the next step.

  13. Select the Target Expression view.
  14. Select the If Then Else option.

    15. The following XQuery if-then-else expression is added to the link: 

    if (fn:boolean(“true”)) then
        data($PODate)
    else
        ()
  15. In this step, we add a condition to the if section of the if-then-else expression.
    1. Select If Condition in the Expression Structure area.

      2. The Edit If Condition pane is displayed.

    2. In the Expression Functions view, expand Node Functions.
    3. Drag the local-name function to the Left Hand Expression area of the Edit If Condition pane. Leave the $node-var argument in the function selected.
    4. Select the Expression Variables view.
    5. Drag the PODate structural link variable to the $node-var argument of the local-name function in the Left Hand Expression area of the Edit If Condition pane.
    6. Select the = operator.
    7. In the Right Hand Expression area of the Edit If Condition pane, enter “billing-date” (including quotation marks), and then click Add.

      8. The following condition is added to the if section of the if-then-else expression: 

      fn:local-name($PODate)=“billing-date”
  16. Select Then Expression in the Expression Structure area.

    17. The Edit Then Condition pane is displayed.

  17. Replace the existing text with “BILLING” (including quotation marks), and then click the Apply icon.
  18. Select Else Expression in the Expression Structure area.

    19. The Edit Else Condition pane is displayed.

  19. Replace the existing text with “DELIVERY” (including quotation marks), and then click the Apply icon.
  20. Select If Then Else in the Expression Structure area.

    21. The following XQuery code is displayed in the Expression Structure pane. 

    if (fn:local-name($PODate) = "billing-date") then
    “BILLING”
    else
    “DELIVERY”
  21. Save the changes.

For information about testing XQuery files, see Testing Data Transformations.

Creating Nested If-Then-Else Expressions

In this example, we create an XQuery transformation that calculates price based on a widget ID and tax rate for a state. We create an if-then-else expression for the following logic:

This example includes the following steps:

Step 1. Create the XQuery Transformation

In this step, we create an XQuery transformation by using the PurchaseAgree.xsd and Supplier.xsd schemas.

  1. Launch WorkSpace Studio and open the sample project.
  2. Right-click the XQuery Transformations folder.
  3. Choose New > XQuery Transformation.
  4. Verify the name of the parent folder. For this example, the parent folder is /XQuery Transformation/XQueryTransformations.
  5. Enter ifthenelse as the file name and click Next.
  6. Select Supplier.xsd\Supplier as the source type and click Next.
  7. Select PurchaseAgree.xsd\PurchaseOrder as the target type and click Finish.

    8. The ifThenElse.xq file is created.

  8. Create links between the following source and target elements.
    Source Element
    Target Element
    supplier1/products/product
    PurchaseOrder/products/product
    supplier1/products/product/price
    PurchaseOrder/products/product/price

    9. The links are displayed, as shown in the following figure.

    Figure 3-15 XQuery Transformation for If-Then-Else Example


    XQuery Transformation for If-Then-Else Example

Step 2. Create the First “If” Condition

In this step, we create an If expression to specify that if the widget ID is from 0 to 200, then the price is $10.00.

  1. Select the link between the supplier1/products/product/price source element and the PurchaseOrder/products/product/price target element.
  2. Select the Target Expression view.
  3. Select the If Then Else expression type.
  4. In the Expression Structure pane, select If Condition.

    5. The Edit If Condition pane is displayed.

  5. Drag the supplier1\products\product\widgetID element from the Source pane and drop it in the Left Hand Expression pane.
  6. Select the >= operator.
  7. In the Right Hand Expression pane, enter “0” (including quotation marks), and click Add.
  8. Select the <= operator.
  9. In the Right Hand Expression pane, enter “200”.
  10. Click the arrow next to the Update button and select Add.
  11. In the Expression Structure pane, select Then Condition.

    12. The Edit Then Condition pane is displayed.

  12. In the Edit Then Expression pane, delete the existing data and enter “$10.00”.
  13. Click the Apply icon.
  14. In the Expression Structure pane, select If Then Else.

    15. The if-then expression is displayed as shown in the following listing.

    Listing 3-4 If-Then Expression 
    if ((xs:string(data($product/ns0:widgetId)) >= "0"
      and xs:string(data($product/ns0:widgetId)) <= "200")) then
      "$10.00"
    else
      ()

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

In this step, we create an If expression to specify that if the widget ID is from 201 to 400, then the price is $20.00. To accomplish this, we insert a nested if-then-else inside the Else expression we created in the previous step.

  1. In the Expression Structure pane, right-click Else Expression, and select Insert Nested If-Then-Else.
  2. In the nested If-Then-Else expression, select If Condition.
  3. From the Source pane, drag the widgetID element and drop it in the Left Hand Expression pane.
  4. Select the >= operator.
  5. In the Right Hand Expression pane, enter “201” (including quotation marks), and click Add.
  6. Select the <= operator.
  7. In the Right Hand Expression pane, enter “400”.
  8. Click the arrow next to the Update button and select Add.
  9. In the Expression Structure pane, select Then Expression.
  10. In the Edit Then Expression pane, enter “$20.00”.
  11. Click the Apply icon.
  12. In the Expression Structure pane, select If Then Else.

    13. The if-then-else expression appears as shown in the following listing.

    Listing 3-5 Nested If-Then-Else Expression 
    if ((xs:string(data($product/ns0:widgetId)) >= "0"
       and xs:string(data($product/ns0:widgetId)) <= "200")) then
          "$10.00"
    else
       if ((xs:string(data($product/ns0:widgetId)) >= "201" 
          and xs:string(data($product/ns0:widgetId)) <= "400")) then
          "$20.00"
       else
          ()

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

In this step, we create an If expression to specify that if the widget ID is from 401 to 600, then the price is $30.00. To accomplish this, we insert a nested if-then-else expression within the Else expression that we created in the previous step.

  1. In the Expression Structure pane, select the Else clause of the nested if-then-else expression created in Step 3. Create the First Nested If-Then-Else Condition
  2. Right-click and select Insert Nested If-Then-Else.
  3. Select the If condition in the nested if-then-else expression that we just created.
  4. From the Source pane, drag widgetID and drop it in the Left Hand Expression pane.
  5. Select the >= operator.
  6. In the Right Hand Expression pane, enter “401”, and then click Add.
  7. Select the <= operator.
  8. In the Right Hand Expression pane, enter “600”, and then click Add.
  9. In the Expression Structure pane, select Then Expression.
  10. In the Edit Then Expression pane, enter “$30.00”, and click the Apply icon.
  11. In the Expression Structure pane, select If Then Else.

    12. The nested if-then-else expression is as shown in the following listing.

    Listing 3-6 Nested If-Then-Else Expression 
    if ((xs:string(data($product/ns0:widgetId)) >= "0"
       and xs:string(data($product/ns0:widgetId)) <= "200")) then
          "$10.00"
    else
       if ((xs:string(data($product/ns0:widgetId)) >= "201"
          and xs:string(data($product/ns0:widgetId)) <= "400")) then
          "$20.00"
       else
          if ((xs:string(data($product/ns0:widgetId)) >= "401"
             and xs:string(data($product/ns0:widgetId)) <= "600")) then
             "$30.00"
          else
             ()

For information about testing XQuery files, see Testing Data Transformations.

Creating FLWOR Expressions

In this example, we use a For-Let-Where-Order By-Return expression to extract widget IDs from a quotation, for items with a total value more than 2000.

  1. Create the XQuery file.
    1. Launch WorkSpace Studio and open the sample project.
    2. Right-click the XQuery Transformations folder.
    3. Choose New > XQuery Transformation.
    4. Verify the name of the parent folder.
    5. Enter flwor as the file name and click Next.
    6. Select Quote.xsd\quote as the source type and click Next.
    7. Select Quote.xsd\quote as the target type and click Finish.

      8. The flwor.xq file is created, as shown in the following figure.

      Figure 3-16 XQuery Example - FLWOR Expression


      XQuery Example - FLWOR Expression

  2. Create the FLWOR expression.
    1. Click on the quote\quoteResponse repeating element in the target pane.
    2. In the Target Expression view, select the For...Return option.

      3. The view changes, as shown in the following figure.

      Figure 3-17 FLWOR Expression


      FLWOR Expression

    3. In the Expression Structure pane, select For Clause.

      4. The Edit For Clause pane is displayed.

    4. In the Variable field, replace the existing value with quote.
    5. In the Expression Variables view, expand the quote1 node.
    6. Drag quote1/quoteresponse from the Expression Variables view, and drop it in the Single Expression field.
    7. Click Update.
  3. Design the Let clause.
    4. Note: For this example, the let clause is not essential. It is used here merely to illustrate how to design it in XQuery Mapper.
    1. In the Expression Structure pane, select Let Clause.

      2. The Edit Let Clause pane is displayed.

    2. In the Variable field, replace the existing value with widget.
    3. Select the Expression Variables view.
    4. Expand the quote node within the Structural Link folder.
    5. Drag quote/widgetID from the Expression Variables view and drop it in the Single Expression field.
    6. Click Update.
  4. Design the Where clause.
    1. In the Expression Structure pane, right-click For...Return and select Insert Where Clause.
    2. Select Where Clause.

      3. The Edit Where Condition pane is displayed.

    3. Drag quote/totalCost from the Structural Link folder of the Expression Variables view, and drop it in the Left Hand Expression field.
    4. Select the > operator.
    5. Enter 2000 in the Right Hand Expression area.
    6. Click Add.
  5. Design the Order By clause.
    1. In the Expression Structure pane, right-click For...Return, and select Insert Order By Clause.
    2. Select Order By Clause.

      3. The Edit Order By Clause pane is displayed.

    3. In the Sort Order field, select ascending.
    4. In the Single Expression field, enter $widget, which is the name of the variable that is declared in the let clause.
    5. Click Update.
  6. Design the Return expression.
    1. In the Expression Structure pane, select Return Expression.
    2. In the Expression Variables view, expand the Structural Link folder.
    3. Drag quote from the Expression Variables view, and drop it in the Single Expression field.
    4. Click the Apply icon.
  7. Save the changes.

You can view the source code of the FLWOR expression by selecting For...Return in the Expression Structure pane. The code is as shown in the following listing.

Listing 3-7 Code for FLWOR Expression 
for $quote in ($quote1/quoteResponse)
let $widget := ($quote/widgetId)
where $quote/totalCost > 2000
order by $widget ascending
return
	$quote

For information about testing XQuery transformations, see Testing Data Transformations.

Using Recursive Schemas

In this example, we create a data transformation with schemas that have recursive elements.

An element in a schema is considered recursive when it contains a child element of the same type as the parent, as shown in the example in Listing 3-8. 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).

Listing 3-8 Example of Recursive Schema 
<?xml version=”1.0”?>
<xs:schema xmlns:xs=”http://www.w3.org/2001/XMLSchema”
   targetNamespace=”http://www.acme.org/Product”
   xmlns=”http://www.acme.org/Product” elementformDefault=”qualified”
   attributeFormDefault=”unqualified”>
   <xs:complexType name=”productType”>
      <xs:sequence>
         <xs:element name=”part-description” minOccurs=”0”
            maxOccurs=”unbounded” type=”xs:string” />
         <xs:element name=”child-product” minOccurs=”0”
            maxOccurs=”unbounded” type=”producttype” />
      </xs:sequence>
   </xs:complexType>
   <xs:element name=”product” type=”productType”>
   </xs:element>
</xs:schema>

Perform the following steps to create a transformation with recursive schemas:

  1. Launch WorkSpace Studio and open the sample project.
  2. Right-click the XQuery Transformations folder.
  3. Choose New > XQuery Transformation.
  4. Verify the name of the parent folder. For this example, the parent folder is /XQuery Transformation/XQueryTransformations.
  5. Enter recursive as the file name and click Next.
  6. Select SupplierAcme.xsd\supplier_acme as the source schema and click Next.
  7. Select Product.xsd\product as the target schema and click Finish.

    8. The recursive.xq file is created.

  8. Create links between the following source and target elements:
    Source Element
    Target Element
    supplier_acme1\part-description-00100
    product\part-description
    supplier_acme1\part-description-00101
    product\child-product\part-description
    supplier_acme1\part-description-00101
    product\child-product\child-product\part-description

The following figure shows the links from the source elements to the recursive child-product target elements.

Figure 3-18 Mapping Recursive Elements

Mapping Recursive Elements

  1. Save the changes.

For information about testing XQuery files, see Testing Data Transformations.

Grouping Data by Key Fields

You can use the Group by Key Fields feature to group data based on one or more key values.

Note: The Group-By feature is not supported graphically in XQuery Mapper and there is no representation of the XQuery in the Design view. You must write the Group-By expression in the Source view.

The following listing shows the XML document that we use as input in this example.

Listing 3-9 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>

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

The first and third instances of the input-line-item repeating element contain the same values for the input-warehouse-id and input-location-desc elements: Warehouse1 and Location1 respectively.

The goal of this example is to write an XQuery that groups the first and third instances of the line items, by using the Warehouse1 and Location1 keys in the output document, as shown in Listing 3-10.

Listing 3-10 Example Output XML 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>

Perform the following steps to create a Group-By expression:

  1. Launch WorkSpace Studio and open the sample project.
  2. Right-click the XQuery Transformations folder.
  3. Choose New > XQuery Transformation.
  4. Verify the name of the parent folder. For this example, the parent folder is /XQuery Transformation/XQueryTransformations.
  5. Enter groupby as the file name and click Next.
  6. Select regroupKeyFldIn.xsd\input-warehouse-inventory as the source schema, and click Next.
  7. Select regroupKeyFldOut.xsd\output-inventory as the target schema, and click Finish.

    8. The groupby.xq file is created.

  8. Select the Source view.
  9. Replace the existing code with the code in the following listing.
    10. Listing 3-11 XQuery Code for Group-By Expression 
    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)
  10. Save the changes.

    11. The changes are not visible in the Design view.

  11. With the groupby.xq file open in the Source view, select the Test view.
  12. In the Source Data pane, click the Import icon.
  13. Import the Regrouping.xml file provided in the sample project (from the XML Transformation/XML/ folder).
  14. In the Result Data pane, select the Test XQuery icon.

    15. The result of the XQuery is displayed, as shown in Listing 3-10.


  Back to Top       Previous  Next