XML/XQuery/Join

Outer join

File: Data.xml
<order>
  <car model="A">
    <id>0001</id>
    <name language="en">name 1</name>
    <colorChoices>navy black</colorChoices>
  </car>
  <car model="B">
    <id>0002</id>
    <name language="en">name 2</name>
  </car>
  <car model="B">
    <id>0003</id>
    <name language="en">name 3</name>
  </car>
  <car model="C">
    <id>0004</id>
    <name language="en">name 4</name>
    <colorChoices>white gray</colorChoices>
    <desc>Our <i>favorite</i> shirt!</desc>
  </car>
</order>
File: prices.xml
<prices>
  <priceList effDate="2006-11-15">
    <prod id="0001">
      <price currency="USD">29.99</price>
      <discount type="CLR">10.00</discount>
    </prod>
    <prod id="0002">
      <price currency="USD">69.99</price>
    </prod>
    <prod id="0003">
      <price currency="USD">39.99</price>
      <discount type="CLR">3.99</discount>
    </prod>
  </priceList>
</prices>

File: Query.xquery
for $car in doc("Data.xml")//car
return <car id="{$car/id}">{
                attribute price
                   {for $price in doc("prices.xml")//prices/priceList/prod
                    where $car/id = $price/@id
                    return $price/price}
  }</car>

Three-way join in a where clause

File: order.xml

<order>
  <car model="A">
    <id>0001</id>
    <name language="en">name 1</name>
    <colorChoices>navy black</colorChoices>
  </car>
  <car model="B">
    <id>0002</id>
    <name language="en">name 2</name>
  </car>
  <car model="B">
    <id>0003</id>
    <name language="en">name 3</name>
  </car>
</order>
File: inventory.xml
<inventory id="inv0001" date="2008-12-19" loc="USA">
  <car model="A" id="0001" quantity="1" color="navy"/>
  <car model="B" id="0002" quantity="1" color="red"/>
  <car model="B" id="0003" quantity="2" color="red"/>
  <car model="C" id="0004" quantity="1" color="white"/>
  <car model="C" id="0004" quantity="1" color="gray"/>
  <car model="A" id="0001" quantity="1" color="black"/>
</inventory>

File: Query.xquery
for $car in doc("inventory.xml")//car,
    $car in doc("order.xml")//car,
    $price in doc("prices.xml")//prices/priceList/prod
where $car/@id = $car/id and $car/id = $price/@id
return <car id="{$car/@id}"
             name="{$car/name}"
             price="{$price/price}"/>

Two-way join in a predicate

File: order.xml

<order>
  <car model="A">
    <id>0001</id>
    <name language="en">name 1</name>
  </car>
  <car model="B">
    <id>0002</id>
    <name language="en">name 2</name>
  </car>
  <car model="B">
    <id>0003</id>
    <name language="en">name 3</name>
  </car>
</order>
File: inventory.xml
<inventory id="inv0001" date="2008-12-19" loc="USA">
  <car model="A" id="0001" quantity="1" color="navy"/>
  <car model="B" id="0002" quantity="1" color="red"/>
  <car model="B" id="0003" quantity="2" color="red"/>
  <car model="C" id="0004" quantity="1" color="white"/>
  <car model="C" id="0004" quantity="1" color="gray"/>
  <car model="A" id="0001" quantity="1" color="black"/>
</inventory>

File: Query.xquery
for $car in doc("inventory.xml")//car,
    $car in doc("Data.xml")//car[id = $car/@id]
return <car id="{$car/@id}"
             name="{$car/name}"
             quan="{$car/@quantity}"/>

Two-way join in a where clause

File: inventory.xml
<inventory id="inv0001" date="2008-12-19" loc="USA">
  <car model="A" id="0001" quantity="1" color="navy"/>
  <car model="B" id="0002" quantity="1"/>
  <car model="B" id="0003" quantity="2"/>
  <car model="C" id="0004" quantity="1" color="white"/>
  <car model="C" id="0004" quantity="1" color="gray"/>
  <car model="A" id="0001" quantity="1" color="black"/>
</inventory>

File: order.xml

<order>
  <car model="A">
    <id>0001</id>
    <name language="en">name 1</name>
  </car>
  <car model="B">
    <id>0002</id>
    <name language="en">name 2</name>
  </car>
  <car model="B">
    <id>0003</id>
    <name language="en">name 3</name>
  </car>
</order>

File: Query.xquery
for $car in doc("inventory.xml")//car,
    $car in doc("Data.xml")//car
where $car/@id = $car/id
return <car id="{$car/@id}"
             name="{$car/name}"
             quan="{$car/@quantity}"/>