XQuery: extract number from string for comparison

Question

I am very new to XQuery and frankly believe that the learning curve is incredibly steep.

I have an XML structure that looks something like this:

<root>
    <product>
        <size>500 units</size>
    </product>
    <product>
        <size>1000 units</size>
    </product>
    <product>
        <size>Unlimited units</size>
    </product>
</root>

I need to write XQuery statement that returns all the nodes where the numerical value of the size of less than 1000. So I somehow need to specify a numerical value (ignoring any text) to perform the "le" I guess.

In addition, there is a possibility that the node will not have any digits (for example, "Unlimited Units"), in which case it should be considered as having a value of 1,000,000.

? fn: replace (blah, '\ D', '') xs: int, .

.

Answer 1

XPath 1.0:

/root/product[not(number(substring-before(size, ' ')) >= 1000)]

, XPath XQuery, XQuery.

Answer 2

XQuery:

for $vProduct in /root/product
let $vUnits := number(substring-before($vProduct/size,'units'))
let $vSize := if ($vUnits)
              then $vUnits
              else 1000000
where $vSize le 1000
return $vProduct

:

<product>
    <size>500 units</size>
</product>
<product>
    <size>1000 units</size>
</product>

Answer 3

, fn:tokenize(subject, pattern, flags) ( tokenize) int. tokenize int . , , int.

To distinguish between cases where units are given and the case of "Unlimited units", you can regexp fn:matchwith "Unlimited units" or simply compare the string before tokenization. If the string is the "Unlimited Units" that you would consider, this is necessary, otherwise you will tokenize the string to retrieve the int value.

Answer 4

A little repetition for clarity

/root/product[not(substring-before(size," ") castable as xs:integer) or xs:integer(substring-before(size," ")) < 1000]

A little repetition for clarity