XQuery Most Recent Order for Each Customer

Question

I am trying to use Altova XMLSpy and XQuery 1.0 to return the most recent order for each customer.

In SQL the query is like this:

SELECT `Order ID`, `Customer ID`, `Employee ID`, `Order Date`
FROM Orders AS O1
WHERE `Order Date` =
  (SELECT MAX(`Order Date`)
   FROM Orders AS O2
   WHERE O2.[Customer ID] = O1.[Customer ID]);

Which returns 16 rows but I cannot get anything similar to work in XQuery.

I have tried multiple variations of code and I think the closest I have got is this:

<result>
{
    for $cust in distinct-values(doc("Orders.xml")//Orders/Customer_x0020_ID)
    return
    <Customer>
    {
        for $order in doc("Orders.xml")//Orders
        where $cust = $order/Customer_x0020_ID
        return max(xs:string($order/Order_x0020_Date))

    }
    </Customer>

}
</result>

Apologies for the terrible tag names the XML has been exported from MS Access.

Please help! Thanks in advance.

<Orders>
  <Order_x0020_ID>30</Order_x0020_ID>
  <Employee_x0020_ID>9</Employee_x0020_ID>
  <Customer_x0020_ID>27</Customer_x0020_ID>
  <Order_x0020_Date>2006-01-15T00:00:00</Order_x0020_Date>
</Orders>

Edit: After trying joemfb's solution I receive all orders for each customer when I need only the most recent (or max date):

<Customer>
    <Order_x0020_ID>57</Order_x0020_ID>
    <Customer_x0020_ID>27</Customer_x0020_ID>
    <Employee_x0020_ID>9</Employee_x0020_ID>
    <Order_x0020_Date>2006-04-22T00:00:00</Order_x0020_Date>
    <Order_x0020_ID>30</Order_x0020_ID>
    <Customer_x0020_ID>27</Customer_x0020_ID>
    <Employee_x0020_ID>9</Employee_x0020_ID>
    <Order_x0020_Date>2006-01-15T00:00:00</Order_x0020_Date>
</Customer>
<Customer>
    <Order_x0020_ID>80</Order_x0020_ID>
    <Customer_x0020_ID>4</Customer_x0020_ID>
    <Employee_x0020_ID>2</Employee_x0020_ID>
    <Order_x0020_Date>2006-04-25T17:03:55</Order_x0020_Date>
    <Order_x0020_ID>58</Order_x0020_ID>
    <Customer_x0020_ID>4</Customer_x0020_ID>
    <Employee_x0020_ID>3</Employee_x0020_ID>
    <Order_x0020_Date>2006-04-22T00:00:00</Order_x0020_Date>
    <Order_x0020_ID>61</Order_x0020_ID>
    <Customer_x0020_ID>4</Customer_x0020_ID>
    <Employee_x0020_ID>9</Employee_x0020_ID>
    <Order_x0020_Date>2006-04-07T00:00:00</Order_x0020_Date>
    <Order_x0020_ID>34</Order_x0020_ID>
    <Customer_x0020_ID>4</Customer_x0020_ID>
    <Employee_x0020_ID>9</Employee_x0020_ID>
    <Order_x0020_Date>2006-02-06T00:00:00</Order_x0020_Date>
    <Order_x0020_ID>31</Order_x0020_ID>
    <Customer_x0020_ID>4</Customer_x0020_ID>
    <Employee_x0020_ID>3</Employee_x0020_ID>
    <Order_x0020_Date>2006-01-20T00:00:00</Order_x0020_Date>
</Customer>

Answer 1

Update: I've modified the query to return all the elements for only the most recent order. This query is a little awkward, since your source XML doesn't group elements by order.

<result>
{
  for $cust in distinct-values(doc("Orders.xml")//Orders/Customer_x0020_ID)
  return
    <Customer>
    {
      let $date :=
      (
        for $cid in doc("Orders.xml")//Orders/Customer_x0020_ID[. eq $cust]
        let $date := $cid/following-sibling::Order_x0020_Date[1]
        order by xs:dateTime($date) descending
        return $date
      )[1]
      return
      (
        $date/preceding-sibling::Order_x0020_ID[1],
        $date/preceding-sibling::Customer_x0020_ID[1],
        $date/preceding-sibling::Employee_x0020_ID[1],
        $date
      )
    }
    </Customer>
}
</result>