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!
Answer
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