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!

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. — Dimitre Novatchev, Dec 09 '12 at 04:14

Daniel Haley · Accepted Answer · 2012-12-09T00:42:01.487

5

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

edited Dec 09 '12 at 00:42

answered Dec 08 '12 at 23:27

Daniel Haley

51,389
6
69
95

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! :) – thrash Dec 08 '12 at 23:29
@user1886061 - See my updated answer about using an external doc. – Daniel Haley Dec 08 '12 at 23:54
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 – thrash Dec 09 '12 at 00:19
@user1886061 - Close. You'd have to do another `for` instead of `let`. See my update for the corrected version. – Daniel Haley Dec 09 '12 at 00:42

score 4 · Answer 2 · answered Dec 09 '12 at 04:12

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

score 2 · Answer 3 · answered Dec 08 '12 at 23:24

2

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 ''

answered Dec 08 '12 at 23:24

koopajah

23,792
9
78
104

Not too sure why but this isnt working for me, it just says no results found :/ – thrash Dec 08 '12 at 23:31
Which language are you using to send the xquery query? – koopajah Dec 08 '12 at 23:31
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! :) – thrash Dec 08 '12 at 23:33
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) ? – thrash Dec 08 '12 at 23:36
I think so acccording to this : http://www.datypic.com/books/xquery/chapter07.html but you must also change the second line which accesses the document too – koopajah Dec 08 '12 at 23:40
You could download/install BaseX which is a free and opensource XML database if you want to investigate further : http://basex.org/ – koopajah Dec 08 '12 at 23:43

xQuery - count function

3 Answers3