Bookmark and Share Share

Using XQuery for Outer Joins

The <join> command in EMML works like an inner join for SQL - only items that have values that match the join condition are selected. For outer joins, you want to see all items from one set of results and join this with other service results even if there is no matching result.

You can get the affect of an outer join using XQuery and the <constructor> statement to build the join results.

Sample Service Results

This sample of an outer join uses three "results" from services that are shown for clarity as variables:

Example: Suppliers Results 
<variable name="suppliers" type="document"> 
  <suppliers> 
    <supplier> 
      <supplierId>s1</supplierId> 
      <supplierName>Genoa and Sons</supplierName> 
    </supplier> 
    <supplier> 
      <supplierId>s2</supplierId> 
      <supplierName>Angora, Ltd.</supplierName> 
    </supplier> 
    <supplier> 
      <supplierId>s3</supplierId> 
      <supplierName>Desideratti</supplierName> 
    </supplier> 
  </suppliers>  
</variable>
Example: Items Results 
<variable name="items" type="document"> 
  <items> 
    <item> 
      <partId>p1</partId> 
      <supplierId>s1</supplierId> 
      <price>10</price> 
    </item> 
    <item> 
      <partId>p2</partId> 
      <supplierId>s1</supplierId> 
      <price>10</price> 
    </item> 
    <item> 
      <partId>p3</partId> 
      <supplierId>s1</supplierId> 
      <price>10</price> 
    </item> 
    <item> 
      <partId>p2</partId> 
      <supplierId>s2</supplierId> 
      <price>11</price> 
    </item> 
  </items> 
</variable>
Example: Parts Results 
<variable name="parts" type="document"> 
  <parts> 
    <part> 
      <partId>p1</partId> 
      <description>Dupioni Silk, grey/blue</description> 
    </part> 
    <part> 
      <partId>p2</partId> 
      <description>Angora wool, cream</description> 
    </part> 
    <part> 
      <partId>p3</partId> 
      <description>Cashmere, pink</description> 
    </part> 
    <part> 
      <partId>p4</partId> 
      <description>Paisley, gold and blue</description> 
    </part> 
    <part> 
      <partId>p5</partId> 
      <description>Georgette, white</description> 
    </part> 
  </parts> 
</variable>

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>