XQUERY 3.0 Multi-level SORT / ORDER BY

Question

I have an XQUERY 3.0 as follows on an XML file with 3 Purchase Order recs with 5 items:

 for $PurchaseOrder in doc("C:\Users\thebluephantom\Desktop\order.xml")//PurchaseOrder
   order by xs:integer($PurchaseOrder/Item/Quantity) ascending
   return $PurchaseOrder/Items/Item/Quantity

that does not return the results as I want. It has a typical nested structure e.g.

<PurchaseOrders>  
  <PurchaseOrder PurchaseOrderNumber="99503" OrderDate="1999-10-20">  
    <Address Type="Shipping">  
      <Name>Ellen Adams</Name>  
      ... 
    </Address>  
      <Address Type="Billing">  
        <Name>Tai Yee</Name>  
        <Street>8 Oak Avenue</Street>  
        ...
    </Address>  
    <DeliveryNotes>Please leave packages in shed by driveway.  </DeliveryNotes>  
    <Items>  
      <Item PartNumber="872-AA">  
      <ProductName>Lawnmower</ProductName>  
      <Quantity>100</Quantity>  
      <USPrice>148.95</USPrice>  
    <Comment>Confirm this is electric</Comment>  
  </Item>  
    <Item PartNumber="926-AA">  
      <ProductName>Baby Monitor</ProductName>  
      <Quantity>20</Quantity>  
      <USPrice>39.98</USPrice>  
      <ShipDate>1999-05-21</ShipDate>  
  </Item>  
</Items>  
 </PurchaseOrder>  
 <PurchaseOrder PurchaseOrderNumber="99505" OrderDate="1999-10-22">  
   ...

I get the result on 3 POs and 5 Order Items:

<Quantity>100</Quantity>
<Quantity>20</Quantity>
<Quantity>1</Quantity>
<Quantity>1000</Quantity>
<Quantity>1</Quantity>

Looking at a number of tutorials on groupings etc. I am not able to find out if this is in fact possible.

Mads Hansen · Accepted Answer · 2017-02-20T14:57:26.913

1

The XPath in your Order By is missing the Items axis, so it's selecting nothing and you are getting your results in document order. If you correct the XPath, then there will be a sequence of values selected in the xs:integer() invocation and will throw an error.

You could rewrite it like this:

for $quantity in 
         doc("C:\Users\thebluephantom\Desktop\order.xml")/PurchaseOrders/PurchaseOrder/Items/Item/Quantity
order by xs:integer($quantity) ascending
return $quantity

edited Feb 20 '17 at 14:57

answered Feb 19 '17 at 19:23

Mads Hansen

63,927
12
112
147

1) Unfortunately that does not work, get an optimized Query using BaseX as () – thebluephantom Feb 20 '17 at 09:39
2) xs:integer throw an error --> never came across that in any tutorial – thebluephantom Feb 20 '17 at 09:39
Whoops, I had been using a local variable to load the content instead of `doc()` and had messed up the XPath. Just added `/PurchaseOrders` – Mads Hansen Feb 20 '17 at 14:58
For clarity: for $quantity in doc("C:\Users\thebluephantom\Desktop\order.xml")//PurchaseOrders/PurchaseOrder/Items/Item/Quantity order by xs:integer($quantity) ascending return $quantity – thebluephantom Feb 20 '17 at 16:38
Also, a suitable tutorial is not so easy to find. – thebluephantom Feb 20 '17 at 16:39

XQUERY 3.0 Multi-level SORT / ORDER BY

1 Answers1