XQuery produces incorrect output

Question

The example xml looks like this:

<sales>
............
     <customer custid="108">
        <name>NORTH WOODS HEALTH AND FITNESS SUPPLY CENTER</name>
        <address>98 LONE PINE WAY</address>
        <city>HIBBING</city>
        <state>MN</state>
        <zip>55649</zip>
        <area>612</area>
        <phone>566-9123</phone>
        <repid>7844</repid>
        <creditlimit>8000</creditlimit>
        <ord ordid="613">
            <orderdate>1987-02-01</orderdate>
            <shipdate>1987-02-01</shipdate>
            <total>6400</total>
            <item itemid="1">
                <product_ref ref="100871"/>
                <actualprice>5.6</actualprice>
                <qty>100</qty>
                <itemtot>560</itemtot>
            </item>
        </ord>
    </customer>
    <product prodid="100860">
        <descrip>ACE TENNIS RACKET I</descrip>
        <price>
            <stdprice>35</stdprice>
            <minprice>28</minprice>
            <startdate>1986-06-01</startdate>
        </price>
    </product>
.........
</sales>

let $product := doc('sales.xml')/sales/product
for $item in doc('sales.xml')/sales/customer/ord/item
where $item/actualprice < $product[@prodid=$item/product_ref/@ref]/price/minprice
return $item

And I get as result this:

<item itemid="1">
    <product_ref ref="100861"/>
    <actualprice>35</actualprice>
    <qty>1</qty>
    <itemtot>35</itemtot>
</item>
<item itemid="3">
    <product_ref ref="101863"/>
    <actualprice>10</actualprice>
    <qty>150</qty>
    <itemtot>1500</itemtot>
</item>
<item itemid="7">
    <product_ref ref="101863"/>
    <actualprice>12.5</actualprice>
    <qty>200</qty>
    <itemtot>2500</itemtot>
</item>
<item itemid="5">
    <product_ref ref="101863"/>
    <actualprice>9</actualprice>
    <qty>100</qty>
    <itemtot>900</itemtot>
</item>

But the items with ref=101863 are not right. Only the item with id=5 is less then minprice. Why does this error occur? Tried a lot of different queries but it gives me always the same result. It works fine with ref=100861.

Answer 1

Assuming the query isn't schema-aware, you are comparing the two prices as strings, not as numbers. You need to convert both to numbers before comparison.