CODEWITHSUNDEEP
xmlxquery
Truth
Truth

Reputation: 31

Using "group by" and nested FLWOR

I am struggling with understanding how to use nested FLWOR expressions and group by. Here is an example XML document and what and what I'm trying to do.

 <review>
 <critic name="Critic1">
 <book>
    <name>ABC</name>
    <rating>6</rating>
 </book>
 <book>
    <name>Monsters</name>
    <rating>2</rating>
 </book>
 <book>
    <name>Spring Poems</name>
    <rating>5</rating>
 </book>
 </critic>
 <critic name="Critic2">
 <book>
    <name>ABC</name>
    <rating>2</rating>
 </book>
 <book>
    <name>Cooking101</name>
    <rating>7</rating>
 </book>
 <book>
    <name>Aliens?</name>
    <rating>10</rating>
 </book>
 </critic>
 </review>

Basically what I am trying to do is group the data by each book name, and then under each book display each critic that reviewed it and the score they gave that book.

The query that I have so far is

 for $books in doc("reviews.xml")/review/critic/book
 let $critics := doc("reviews.xml")/review/critic
 let $d := $books/name
 group by $d
 order by $d
 return <book name="{$d}">{
     for $hasReview in $critics
     where $critics/book/name = $d
     group by $n := $d
     return <critic name="{$critics/@name}" rating="
            {$critics/book[name=$d]/rating}"/>

     }</book>

The output from this Query looks like

 <book name ="ABC">
     <critic name = "Critic1 Critic2" rating="6 2"/>
 </book>
 <book name ="Monsters">
     <critic name = "Critic1 Critic2" rating="2"/>
 </book>
 <book name ="Spring Poems">
     <critic name = "Critic1 Critic2" rating="5"/>
 </book>
 <book name ="Cooking101">
     <critic name = "Critic1 Critic2" rating="7"/>
 </book>
 <book name ="Aliens">
     <critic name = "Critic1 Critic2" rating="10"/>
 </book>

The output I am trying to obtain is something similar to this:

 <book name ="ABC">
     <critic name = "Critic1" rating="6"/>
     <critic name = "Critic2" rating="2"/>
 </book>
 <book name ="Monsters">
     <critic name = "Critic1" rating="2"/>
 </book>
 <book name ="Spring Poems">
     <critic name = "Critic1" rating="5"/>
 </book>
 <book name ="Cooking101">
     <critic name = "Critic2" rating="7"/>
 </book>
 <book name ="Aliens">
     <critic name = "Critic2" rating="10"/>
 </book>

Upvotes: 1

Views: 187

Answers (1)

Riiverside
Riiverside

Reputation: 798

I haven't used group by yet, because I never used any software that supported it. So I would to it this way:

for $bookName in distinct-values(doc("reviews.xml")/review/critic/book/name)
order by $bookName
return <book name="{$bookName}">{
    for $critic in doc("reviews.xml")/review/critic/book[name eq $bookName]
    return <critic name="{$critic/../@name}" rating="{$critic/rating}" />
}</book>

When trying this out on your sample data I got the following result:

<book name="ABC">
  <critic name="Critic1" rating="6"/>
  <critic name="Critic2" rating="2"/>
</book>
<book name="Aliens?">
  <critic name="Critic2" rating="10"/>
</book>
<book name="Cooking101">
  <critic name="Critic2" rating="7"/>
</book>
<book name="Monsters">
  <critic name="Critic1" rating="2"/>
</book>
<book name="Spring Poems">
  <critic name="Critic1" rating="5"/>
</book>

Not sure group by is even what you're looking for here. By looking at your output it seems like it strangely merges the attributes of the two critic nodes into one single attribute.(That may just be what it is supposed to do, but it's def not what you need)

Upvotes: 1

Related Questions