xQuery - count function

Question

Im looking to form an xQuery which returns the category of a book if this category contains more than x amount of books. For example if I have 4 categories; music, film, education, health and they all have 1 book in, apart from music which has 3, im looking to list that category. Ive tried a range of queries but cant seem to get it right, I just get no result everytime, I believe I need to be using distinct-value at some point? Not too sure.

An example of the source can be seen below, im testing the query in an editor which doesnt save the source as a file so for me to test the xQuery has to start with:

e.g. for $x in /bookstore/book...

<bookstore>        
<book category="Music">
            <year>  2005  </year>
            <price>  50  </price>
        </book>
    <book category="Music">
            <year>  2010  </year>
            <price>  35  </price>
        </book>
    <book category="Music">
            <year>  1982  </year>
            <price>  70  </price>
        </book>
    <book category="Film">
            <year>  2000  </year>
            <price>  10  </price>
        </book>
    <book category="Health">
            <year>  1965  </year>
            <price>  50  </price>
        </book>
    <book category="Education">
            <year>  2012  </year>
            <price>  70  </price>
        </book>
</bookstore>

Any help is massively appreciated!

Piscator

You may be interested in a quite different solution, that is a pure XPath 2.0 expression and doesn't use distinct-values() at all. – Marcelo

Answer 1

Similar to @koopajah's answer and since I already wrote it I'll submit it...

for $category in distinct-values(/*/book/@category)
where count(/*/book[@category=$category]) >= 3
return
    <results>{$category}</results>

With an external doc...

let $doc := doc('input.xml')

for $category in distinct-values($doc/*/book/@category)
where count($doc/*/book[@category=$category]) >= 3
return
    <results>{$category}</results>

Modified xquery from comment...

for $x in doc('input.xml')/bookstore 
for $y in distinct-values($x/book/@category) 
where count($x/book[@category=$y]) >= 3 
return $y 

Photoreconnaissance

Not too sure why but this isnt working for me, it just says no results found :/ With a bit of fiddling I managed to get it work, thanks a lot! :) – Piscator

@user1886061 - See my updated answer about using an external doc. – Photoreconnaissance

Thank you, I really appreciate it but would the following also work? for $x in doc(‘input.xml’) /bookstore/ let $y in distinct-values($x/book/@category) where count($x/book[@category=$y]) >= 3 return $y – Piscator

@user1886061 - Close. You'd have to do another for instead of let. See my update for the corrected version. – Photoreconnaissance

Answer 2

Use this XQuery, that is also a pure XPath 2.0 expression. Note distinct-values() isn't used:

(/*/*/@category)[index-of(/*/*/@category, .)[3]]/string()

When evaluated on the provided XML document:

<bookstore>
    <book category="Music">
        <year>  2005  </year>
        <price>  50  </price>
    </book>
    <book category="Music">
        <year>  2010  </year>
        <price>  35  </price>
    </book>
    <book category="Music">
        <year>  1982  </year>
        <price>  70  </price>
    </book>
    <book category="Film">
        <year>  2000  </year>
        <price>  10  </price>
    </book>
    <book category="Health">
        <year>  1965  </year>
        <price>  50  </price>
    </book>
    <book category="Education">
        <year>  2012  </year>
        <price>  70  </price>
    </book>
</bookstore>

the wanted, correct result is produced:

Music

Marcelo

Answer 3

I've just tried this query in BaseX database and it seems to work as you expect:

for $cat in distinct-values(/bookstore/book/@category)
let $nbBook := count(/bookstore/book[@category=$cat])
return if($nbBook > 1) then $cat else ''

Subdual

Not too sure why but this isnt working for me, it just says no results found :/ – Piscator

Which language are you using to send the xquery query? – Subdual

No worries, I managed to get it working in the end, I changed a few things and it just seemed to work! So thanks a lot! :) – Piscator

Oh just one quick question, if I were to save it to an external file what would I change the top line to? I would test it myself but the only program I have doesnt allow you to load an XML file you just have to input it. Would it just be: for $cat in distinct-values(doc(file.xml)/bookstore/book/@category) ? – Piscator

I think so acccording to this : datypic.com/books/xquery/chapter07.html but you must also change the second line which accesses the document too – Subdual

You could download/install BaseX which is a free and opensource XML database if you want to investigate further : basex.org – Subdual