xquery for grouping data from xml file

Question

I am trying to run an xquery for my xml file. This is my input xml file:

<?xml version="1.0" ?>
<authors>
   <author><name> Steven</name>
     <title>advanced</title>
     <title>TCPIP</title>
   </author>
   <author><name> George</name>
     <title>DataMining</title>
     <title>Economic</title>
   </author>
   <author><name> Den</name>
    <title>TCPIP</title>
    <title>Economic</title>
   </author>
</authors>

I am trying to group each title and count the number of authors for each title. Books should be listed in increasing order of number of authors. For each book, output its title, the number of authors and the name of all its authors in alphabetic order. Finally, the output should be like this:

<bib>
 <book authors="1">
  <title>Advanced</title>
  <author>Steven</authhor>
 </book>
 <book authors="1">
  <title>DataMining</title>
  <author>George</author>
 </book>
 <book authors="2">
  <title>TCPIP</title>
  <author>Den</author>
  <author>Steven</author>
 </book>
 <book authors="2">
  <title>Economic</title>
  <author>Den</author>
  <author>George</author>
 </book>
</bib>

This is my code which doesn't work :(. is there any help. please if it is possible correct my code instead of writing a new code, because i want the answer be look like to my answer.

<bib>
{for $a in doc('test')//authors
let $T:={for $c in doc ('test')//authors/author[title=$a/author/title]
order by count($T)
return <book authors="{count($T)}">
{<title> {distinct-values($T/title)}
for $x in $T
order by $x/name()
return <author>{$x/name()}</author>}
</book>}
</bib>

I think the problem is for the part that I try to use the let command. As I know I can group data by let command. I tried for each title in XML file to grab all authors and after that count the number of authors and sort them by name. I use this link: http://basex.org/products/live-demo/ to test my answer.

Your input XML and expected output XML is syntactically incorrect (<name> is not closed, </> is invalid). More importantly (but quite likely related to the invalid XML), I don't get how the authors and the titles are connected. Normally you would have some kind of hierarchy, but here the authors and titles are all siblings. However, one first hint: The problem you describe screams for a group by expression, you should definitely rewrite this. As BaseX supports XQuery 3.0 it also does support group by — dirkk
Like dirkk said, you should rewrite this. You can still use distinct-values() instead of group by if you want, but the biggest issue is that you're trying to create an output focused on the titles of the books, yet you start out looping over authors names. Try looping over the titles to build the book elements and then looping over the authors (who authored that title) to build the author elements. — Daniel Haley

dirkk dirkk · Accepted Answer · 2013-12-04T11:17:55

As Daniel Haley suggested, you should do it the other way round, i.e. iterating over each title and looking for the authors which write this specific title. You can use distinct-values or group by. The following should work:

<bib>
{
  let $titles := distinct-values(doc('test')//title)
  for $t in $titles
  let $authors := doc('test')//author[title = $t]
  order by count($authors)
  return
    <book authors="{count($authors)}">
      <title>{$t}</title>
      {
        for $a in $authors
        order by $a/name
        return <author>{$a/name/string()}</author>
      }
    </book>
}
</bib>

xquery for grouping data from xml file

1 Answers