XQuery return elements based on an attribute

Question

I have an XML file, to which there are 4 main types of elements. Product, book, journal and musicCD.

Each book, journal and musicCD has an attribute IDREF that can be referenced to the attribute ID of a product.

What I'm trying to achieve is to find how many books, or journals, or music CDs for each product and then display products’ IDs and total number of items (books, journals and music CDs) for each product.

My XML file looks like this:

<bookshop>
    <product ID="185.3.16">
        <price currency="AU$">56.85</price>
        <comments>Best sell</comments>
    </product>
    <product ID="163.24.12">
        <price currency="NZ$">28.6</price>
        <comments />
    </product>
    <product ID="123.45.67">
        <price currency="AU$">15.50</price>
        <comments>I love it</comments>
    </product>
    <product ID="134.41.33">
        <price currency="AU$">25.50</price>
        <comments></comments>
    </product>
    <product ID="214.56.82">
        <price currency="US$">35.65</price>
        <comments>Good</comments>
    </product>
    <product ID="263.18.37">
        <price currency="AU$">46.35</price>
        <comments>You should see this</comments>
    </product>
    <product ID="285.32.74">
        <price currency="US$">38.95</price>
        <comments></comments>
    </product>
    <product ID="321.12.76">
        <price currency="US$">15.95</price>
        <comments>Popular</comments>
    </product>
    <product ID="342.22.42">
        <price currency="US$">10.95</price>
        <comments>Sweet</comments>
    </product>
    <product ID="332.17.25">
        <price currency="US$">19.95</price>
        <comments></comments>
    </product>
    <book IDREF="185.3.16">
        <isbn>1-292-06118-9</isbn>
        <title>Database Systems</title>
        <authors>
            <author>
                <fname>Thomas</fname>
                <lname>Connolly</lname>
            </author>
            <author>
                <fname>Carolyn</fname>
                <lname>Begg</lname>
            </author>
        </authors>
        <publisher>Pearson Pty Ltd</publisher>
        <year>2015</year>
        <topic>Computer Science</topic>
        <description>This is the 6th edition. You can register online   to access the examples</description>    
        <keywords>
            <keyword>Database</keyword>
            <keyword>XML</keyword>
            <keyword>Distributed</keyword>
        </keywords>
    </book>
    <book IDREF="163.24.12">
        <isbn>1-123-456-810</isbn>
        <title>Core Java</title>
        <authors>
            <author>
                <fname>Horstmann</fname>
                <lname>Cornell</lname>
            </author>
        </authors>
        <publisher>PH Pty Ltd</publisher>
        <year>2012</year>
        <topic>Computer Science</topic>
        <description>It covers JAVA programming and JAVA script</description>
        <keywords>
            <keyword>JAVA</keyword>
            <keyword>XML</keyword>
            <keyword>Script</keyword>
        </keywords>
    </book>
    <book IDREF="123.45.67">
        <isbn>1-123-456-789</isbn>
        <title>Algorithms</title>
        <authors>
            <author>
                <fname>James</fname>
                <lname>Bond</lname>
            </author>
            <author>
                <fname>Harry</fname>
                <lname>Potter</lname>
            </author>
            <author>
                <fname>William</fname>
                <lname>Stallings</lname>
            </author>
        </authors>
        <publisher>Pearson Pty Ltd</publisher>
        <year>2013</year>
        <topic>Computer Science</topic>
        <description>It contains algorithms and their applications. You can download examples from the website</description>
    </book>
    <book IDREF="134.41.33">
        <isbn>1-213-431-770</isbn>
        <title>C++ Programming</title>
        <authors>
            <author>
                <fname>Larry</fname>
                <lname>Peterson</lname>
            </author>
        </authors>
        <publisher>Pearson Pty Ltd</publisher>
        <year>2010</year>
        <topic>Computer Science</topic>
        <description>C++ programming and its applications</description>
        <keywords>
            <keyword>C++</keyword>
            <keyword>Class</keyword>
            <keyword>Overloading</keyword>
            <keyword>Inheritance</keyword>
        </keywords>
    </book>
    <journal IDREF="214.56.82">
        <issn>S-11-123-123-456</issn>
        <title>Handyman</title>
        <volume>12</volume>
        <issue>23</issue>
    </journal>
    <journal IDREF="214.56.82">
        <issn>S-11-123-123-456</issn>
        <title>Handyman</title>
        <volume>12</volume>
        <issue>24</issue>
    </journal>
    <journal IDREF="214.56.82">
        <issn>S-11-123-123-456</issn>
        <title>Handyman</title>
        <volume>12</volume>
        <issue>25</issue>
    </journal>
    <journal IDREF="263.18.37">
        <issn>D-10-123-124-456</issn>
        <title>Information processing</title>
        <volume>5</volume>
        <issue>33</issue>
    </journal>
    <journal IDREF="263.18.37">
        <issn>D-10-123-124-456</issn>
        <title>Information processing</title>
        <volume>5</volume>
        <issue>34</issue>
    </journal>
    <journal IDREF="285.32.74">
        <issn>C-9-123-456-666</issn>
        <title>Mathmetics and Computing</title>
        <volume>17</volume>
        <issue>65</issue>
    </journal>
    <journal IDREF="285.32.74">
        <issn>C-9-123-456-666</issn>
        <title>Mathmetics and Computing</title>
        <volume>17</volume>
        <issue>66</issue>
    </journal>
    <musicCD IDREF="321.12.76">
        <title>Music</title>
        <producer>ABC Pty Ltd</producer>
        <year>2010</year>
        <category>Pop</category>
    </musicCD>
    <musicCD IDREF="342.22.42">
        <title>Stars</title>
        <producer>BBC Pty Ltd</producer>
        <year>2012</year>
        <category>Classic</category>
    </musicCD>
    <musicCD IDREF="332.17.25">
        <title>Wiggle Wiggle</title>
        <producer>Wiggle Pty Ltd</producer>
        <year>2005</year>
        <category>Kids</category>
    </musicCD>
</bookshop>

I've made an attempt, but I don't know how to properly iterate through/match the ID's or how to do the counting.

for $p in distinct-values(//product/@ID)
return
<result>
    {
        for $i in //journal[@IDREF = $p]
        return 
        <ID>{$p}</ID>
        <count></count>
    }
</result>

and ideally I'm aiming for the output to look something like this mock data:

<result>
    <ID>185.3.16</ID>
    <count>2</count>
<result>
<result>
    <ID>163.24.12</ID>
    <count>0</count>
<result>
...

Jens Erat Jens Erat · Accepted Answer · 2016-05-31T14:21:25

First of all, your query has a syntax problem, because the return statement only expects a single result (sequence), so you'd have to replace

    for $i in //journal[@IDREF = $p]
    return 
    <ID>{$p}</ID>
    <count></count>

with

    for $i in //journal[@IDREF = $p]
    return (
      <ID>{$p}</ID>,
      <count></count>
    )

But you don't really need the explicit inner loop anyway. Furthermore, I don't think you have to use fn:distinct-values(...) here, as the IDs of the products should be distinct anyway (if they qualify as XML id attributes)! ID attributes are already expected to be unique. In the end, all you have to do is looping over all products and counting how much @IDREF attributes exist for this product.

for $product in //product
return
  <result>
    <ID>{ $product/@ID }</ID>
    <count>{ count(//@IDREF[. = $product/@ID]) }</count>
  </result>

Given that either the underlying XML schema properly defines id and idref attributes to be @ID and @IDREF, or your implementation picks them up anyway (for example, BaseX does), you could also use the fn:idref(...) function for counting, which is pretty much defined as the query used above:

    <count>{ count(idref($product/@ID))}</count>

XQuery return elements based on an attribute

1 Answers