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:
You can use XQuery Mapper to combine content from two different schemas, as shown in the following figure.
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.
XQuery Transformations
folder./XQuery Transformation/XQueryTransformations
.combineData
as the file name and click Next.
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.
|
||
|
||
|
||
|
||
|
||
|
||
|
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.
For information about testing XQuery transformations, see Testing Data Transformations.
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:
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.
XQuery Transformations
folder./XQuery Transformation/XQueryTransformations
.Join
as the file name and click Next.
The Join.xq
file is created in the XQueryTransformation/XQueryTransformations folder
.
The links are displayed, as shown in the following figure.
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
.
Join.xq
in the Design view.priceQuote1/priceRequests/priceRequest/widgetId
element from the Source pane and drop it on the availRequest1/widgetId
element of the Source pane.
A connecting line appears between the two widgetId
nodes in the Source pane, as shown in the following figure.
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.
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:
The links between the Source and Target elements are displayed, as shown in the following figure.
In this step, we add a function to calculate the total value of the quote.
Join.xq
file in Source view. Join
function call. You can, for example, insert it just before the Join
function declaration.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
};
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. |
xf:calculateTotalPrice($taxRate1,$availRequest/ns1:requestedQuanity,$priceRequest/ns0:price)
The expression is added to the totalCost element in the XQuery.
The Design view shows the calculation for the totalCost target element.
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.
join.xq
file.availRequest1
source element and the quote\quoteResponse
target element.
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)
availRequest1/requestedQuanity
element from the Source pane and drop it in the Left Hand Expression area of the Where Clause pane.The left hand expression of the where clause is created as follows:
data($availRequest/ns1:requestedQuanity)
<
operator.“50”
.Note: | Enter the number 50 within quotation marks (“50” , not 50 ). |
The Join Type determines how the conditions that make up the where
clause are evaluated at run time.
where
clause of the for
loop.
This step completes the creation of the following where
clause.
where (data($availRequest/ns1:widgetId) = data($priceRequest/ns0:widgetId)
and data($availRequest/ns1:requestedQuanity) < "50")
Perform the following steps to verify that the XQuery works when both the conditions of the where
clause you created are satisfied.
priceQuote
in the Source Variable field, and click the Generate Data icon.widgetId
element in the test XML data.<ns0:widgetId>value</ns0:widgetId>
availRequest
in the Source Variable field, and click the Generate Data icon.widgetId
element in the test XML data to match the value displayed in the priceQuote
test XML data.<ns0:widgetId>value</ns0:widgetId>
For example: <ns0:requestedQuanity>25</ns0:requestedQuanity>
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.
XQuery Transformations
folder./XQuery Transformation/XQueryTransformations
.union
as the file name and click Next.PO.xsd\purchase-order
twice, and then click Next.Note: | To add an element more than once, you must change the parameter name. |
The following figure shows how the links appear in the Design view.
$purchase-order1/line-items/line-item
source element and the order/items/item
target element.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.
For information about testing XQuery transformations, see Testing Data 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.
XQuery Transformations
folder./XQuery Transformation/XQueryTransformations
.repeatToNonRepeat
as the file name and click Next.Dates.xsd\dates
as the source schema and click Next.PODate.xsd\PODate
as the target schema and click Finish.
The repeatToNonRepeat.xq
file is created and displayed, as shown in the following figure.
dates1/date
repeating element in the Source pane and the PODate/billing-date
element in the Target pane.Keep this link selected for the next step.
dates1/date/type
element from the Source pane to the Left Hand Expression area of the Where Clause pane in the Constraints view.=
operator."BILLING"
(including the quotation marks), and click Add.dates1/date/value
element in the Source pane and the PODate/billing-date
element in the Target pane.
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
.
dates1/date
repeating element in the Source pane and PODate/delivery-date
element in the Target pane.Keep this link selected for the next step.
dates1/date/type
element from the Source pane to the Left Hand Expression area of the Where Clause pane in the Constraints view.=
operator."DELIVERY"
(including the quotation marks), and click Add.dates1/date/value
repeating element in the Source pane and PODate/delivery-date
element in the Target pane.
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
.
For information about testing XQuery files, see Testing Data Transformations.
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.
XQuery Transformations
folder./XQuery Transformation/XQueryTransformations
.nonRepeatToRepeat
as the file name and click Next.PODate.xsd\PODate
as the source schema and click Next.Dates.xsd\dates
as the target schema and click Finish.nonRepeatToRepeat.xq
file is created and displayed in the Design view.The following listing shows the XQuery code that is generated.
<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.
pODate1/billing-date
source element and the dates/value
target element in the Target pane.Two data links are created, as shown in the following figure.
The following structural links were joined when we created the link from pODate1/billing-date
to dates/value
.
pODate1/billing-date
source element and the dates/type
target element.
Keep the pODate1/billing-date
to dates/type
link selected for the next step.
The following XQuery if-then-else expression is added to the link:
if (fn:boolean(“true”)) then
data($PODate)
else
()
if
section of the if-then-else expression.The Edit If Condition pane is displayed.
local-name
function to the Left Hand Expression area of the Edit If Condition pane. Leave the $node-var
argument in the function selected.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.=
operator.“billing-date”
(including quotation marks), and then click Add.
The following condition is added to the if
section of the if-then-else expression:
fn:local-name($PODate)=“billing-date”
The Edit Then Condition pane is displayed.
“BILLING”
(including quotation marks), and then click the Apply icon.The Edit Else Condition pane is displayed.
“DELIVERY”
(including quotation marks), and then click the Apply icon.The following XQuery code is displayed in the Expression Structure pane.
if (fn:local-name($PODate) = "billing-date") then
“BILLING”
else
“DELIVERY”
For information about testing XQuery files, see Testing Data Transformations.
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:
In this step, we create an XQuery transformation by using the PurchaseAgree.xsd
and Supplier.xsd
schemas.
XQuery Transformations
folder./XQuery Transformation/XQueryTransformations
.ifthenelse
as the file name and click Next.Supplier.xsd\Supplier
as the source type and click Next.PurchaseAgree.xsd\PurchaseOrder
as the target type and click Finish.
The ifThenElse.xq
file is created.
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.
supplier1/products/product/price
source element and the PurchaseOrder/products/product/price
target element.The Edit If Condition pane is displayed.
supplier1\products\product\widgetID
element from the Source pane and drop it in the Left Hand Expression pane.>=
operator.“0”
(including quotation marks), and click Add.<=
operator.“200”
.The Edit Then Condition pane is displayed.
“$10.00”
.The if-then expression is displayed as shown in the following listing.
if ((xs:string(data($product/ns0:widgetId)) >= "0"
and xs:string(data($product/ns0:widgetId)) <= "200")) then
"$10.00"
else
()
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.
widgetID
element and drop it in the Left Hand Expression pane.>=
operator.“201”
(including quotation marks), and click Add.<=
operator.“400”
.“$20.00”
.The if-then-else expression appears as shown in the following listing.
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
()
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.
Else
clause of the nested if-then-else expression created in Step 3. Create the First Nested If-Then-Else ConditionIf
condition in the nested if-then-else expression that we just created.widgetID
and drop it in the Left Hand Expression pane.>=
operator.“401”
, and then click Add.<=
operator.“600”
, and then click Add.“$30.00”
, and click the Apply icon.The nested if-then-else expression is as shown in the following listing.
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.
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.
XQuery Transformations
folder.flwor
as the file name and click Next.Quote.xsd\quote
as the source type and click Next.Quote.xsd\quote
as the target type and click Finish.
The flwor.xq
file is created, as shown in the following figure.
quote\quoteResponse
repeating element in the target pane.The view changes, as shown in the following figure.
The Edit For Clause pane is displayed.
quote
.quote1
node.quote1/quoteresponse
from the Expression Variables view, and drop it in the Single Expression field.Note: | For this example, the let clause is not essential. It is used here merely to illustrate how to design it in XQuery Mapper. |
The Edit Let Clause pane is displayed.
widget
.quote
node within the Structural Link folder.quote/widgetID
from the Expression Variables view and drop it in the Single Expression field.The Edit Where Condition pane is displayed.
quote/totalCost
from the Structural Link folder of the Expression Variables view, and drop it in the Left Hand Expression field.>
operator.2000
in the Right Hand Expression area.The Edit Order By Clause pane is displayed.
$widget
, which is the name of the variable that is declared in the let
clause.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.
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.
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).
<?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:
XQuery Transformations
folder./XQuery Transformation/XQueryTransformations
.recursive
as the file name and click Next.SupplierAcme.xsd\supplier_acme
as the source schema and click Next.Product.xsd\product
as the target schema and click Finish.
The recursive.xq
file is created.
The following figure shows the links from the source elements to the recursive child-product
target elements.
For information about testing XQuery files, see Testing Data Transformations.
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.
<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.
<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:
XQuery Transformations
folder./XQuery Transformation/XQueryTransformations
.groupby
as the file name and click Next.regroupKeyFldIn.xsd\input-warehouse-inventory
as the source schema, and click Next.regroupKeyFldOut.xsd\output-inventory
as the target schema, and click Finish.
The groupby.xq
file is created.
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.
groupby.xq
file open in the Source view, select the Test view.Regrouping.xml
file provided in the sample project (from the XML Transformation/XML/
folder).The result of the XQuery is displayed, as shown in Listing 3-10.