Constructing the Join
Rather than using <join>, you use <constructor> to construct the result and XQuery to select from the results to create the join:
The <constructor> creates a root node, <result>, as literal XML and uses a dynamic mashup expression with XQuery inside to select all suppliers in the $suppliers variable and sort them by name:
<constructor outputvariable="$result">
<result>
{
for $s in $suppliers/suppliers/supplier
order by $s/supplierName
...
The XQuery expression returns a <supplier> node for each supplier selected:
...
<result>
{
for $s in $suppliers/suppliers/supplier
order by $s/supplierName
return
<supplier>
...
</supplier>
}
</result>
</constructor>
Within this literal <supplier> node, another dynamic mashup expression with an XQuery expression selects the matching items and parts, if any, to create the join:
...
<supplier>
{
$s/supplierName,
for $i in $catalog/items/item[supplierId = $s/supplierId],
$p in $parts/parts/part[partId = $i/partId]
order by $p/description
return $p/description
}
</supplier>
...
In the final result, each <supplier> node has a <supplierName> node. If there are matching items and parts, this is followed by <description> nodes for the parts, sorted in description order. If no items or parts match a supplier, there are no <description> node children.
The complete <constructor> statements looks like this:
<constructor outputvariable="$result">
<result>
{
for $s in $suppliers/suppliers/supplier
order by $s/supplierName
return
<supplier>
{
$s/supplierName,
for $i in $catalog/items/item[supplierId = $s/supplierId],
$p in $parts/parts/part[partId = $i/partId]
order by $p/description
return $p/description
}
</supplier>
}
</result>
</constructor>