XQuery Counting Operations

2
votes

Can someone please help point me in the right direction? I am working on some XQuery expressions but so far, I have spent hours on end trying to evaluate the following:

  1. List all the characters that appear in plays with forty or more characters
  2. List the play(s) having the largest casts, i.e. contain the largest number of characters

Which I respectively interpret as:

  1. Find all the values of the attribute ‘characterName’ where the value of each distinct ‘playName’ attribute occurs 40 times or more
  2. Find all the values of the attribute ‘playName’ where the number of distinct values of the attribute ‘characterName’ is/are highest

Given the XML document below:

Thank you in advance for any advice.

<charactersTable>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c1" characterName="Falstaff">
         <plays>
            <Title playName="Henry4p1" />
            <Title playName="Henry4p2" />
            <Title playName="Henry5" />
            <Title playName="Wives" />
         </plays>
         <speeches>
            <numberOfSpeeches>471</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c2" characterName="Richard III (Duke of Gloucester)">
         <plays>
            <Title playName="Henry6p3" />
            <Title playName="Richard3" />
         </plays>
         <speeches>
            <numberOfSpeeches>409</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c3" characterName="Henry V">
         <plays>
            <Title playName="Henry4p1" />
            <Title playName="Henry4p2" />
            <Title playName="Henry5" />
         </plays>
         <speeches>
            <numberOfSpeeches>377</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c4" characterName="Hamlet">
         <plays>
            <Title playName="Hamlet" />
         </plays>
         <speeches>
            <numberOfSpeeches>358</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c5" characterName="Othello">
         <plays>
            <Title playName="Othello" />
         </plays>
         <speeches>
            <numberOfSpeeches>274</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c6" characterName="Iago">
         <plays>
            <Title playName="Othello" />
         </plays>
         <speeches>
            <numberOfSpeeches>272</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c7" characterName="Antony">
         <plays>
            <Title playName="Antony" />
            <Title playName="Caesar" />
         </plays>
         <speeches>
            <numberOfSpeeches>253</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c8" characterName="Timon">
         <plays>
            <Title playName="Timon" />
         </plays>
         <speeches>
            <numberOfSpeeches>210</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c9" characterName="Cleopatra">
         <plays>
            <Title playName="Antony" />
         </plays>
         <speeches>
            <numberOfSpeeches>204</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c10" characterName="Rosalind">
         <plays>
            <Title playName="AYLI" />
         </plays>
         <speeches>
            <numberOfSpeeches>201</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c11" characterName="Brutus">
         <plays>
            <Title playName="Caesar" />
         </plays>
         <speeches>
            <numberOfSpeeches>194</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c12" characterName="Vincentio">
         <plays>
            <Title playName="Measure" />
         </plays>
         <speeches>
            <numberOfSpeeches>194</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c13" characterName="Coriolanus">
         <plays>
            <Title playName="Coriolanus" />
         </plays>
         <speeches>
            <numberOfSpeeches>189</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c14" characterName="Lear">
         <plays>
            <Title playName="Lear" />
         </plays>
         <speeches>
            <numberOfSpeeches>188</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c15" characterName="Henry VI">
         <plays>
            <Title playName="Henry6p1" />
            <Title playName="Henry6p2" />
            <Title playName="Henry6p3" />
            <Title playName="Richard3" />
         </plays>
         <speeches>
            <numberOfSpeeches>183</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c16" characterName="Earl of Warwick">
         <plays>
            <Title playName="Henry4p2" />
            <Title playName="Henry5" />
            <Title playName="Henry6p1" />
            <Title playName="Henry6p2" />
            <Title playName="Henry6p3" />
         </plays>
         <speeches>
            <numberOfSpeeches>182</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c17" characterName="Queen Margaret">
         <plays>
            <Title playName="Henry6p1" />
            <Title playName="Henry6p2" />
            <Title playName="Henry6p3" />
            <Title playName="Richard3" />
         </plays>
         <speeches>
            <numberOfSpeeches>169</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c18" characterName="Richard Plantagenet (Duke of Gloucester)">
         <plays>
            <Title playName="Henry6p1" />
            <Title playName="Henry6p2" />
            <Title playName="Henry6p3" />
            <Title playName="Richard3" />
         </plays>
         <speeches>
            <numberOfSpeeches>166</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c19" characterName="Desdemona">
         <plays>
            <Title playName="Othello" />
         </plays>
         <speeches>
            <numberOfSpeeches>165</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c20" characterName="Romeo">
         <plays>
            <Title playName="Romeo" />
         </plays>
         <speeches>
            <numberOfSpeeches>163</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c21" characterName="Menenius Agrippa">
         <plays>
            <Title playName="Coriolanus" />
         </plays>
         <speeches>
            <numberOfSpeeches>162</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c22" characterName="Biron">
         <plays>
            <Title playName="LLL" />
         </plays>
         <speeches>
            <numberOfSpeeches>159</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c23" characterName="Hostess Quickly">
         <plays>
            <Title playName="Henry4p1" />
            <Title playName="Henry4p2" />
            <Title playName="Henry5" />
            <Title playName="Wives" />
         </plays>
         <speeches>
            <numberOfSpeeches>158</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c24" characterName="Petruchio">
         <plays>
            <Title playName="Shrew" />
         </plays>
         <speeches>
            <numberOfSpeeches>158</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c25" characterName="Henry IV">
         <plays>
            <Title playName="Henry4p1" />
            <Title playName="Henry4p2" />
            <Title playName="Richard2" />
         </plays>
         <speeches>
            <numberOfSpeeches>154</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c26" characterName="Pandarus">
         <plays>
            <Title playName="Troilus" />
         </plays>
         <speeches>
            <numberOfSpeeches>153</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c27" characterName="Cressida">
         <plays>
            <Title playName="Troilus" />
         </plays>
         <speeches>
            <numberOfSpeeches>152</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c28" characterName="Sir Toby Belch">
         <plays>
            <Title playName="12Night" />
         </plays>
         <speeches>
            <numberOfSpeeches>152</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c29" characterName="Valentine">
         <plays>
            <Title playName="2Gents" />
         </plays>
         <speeches>
            <numberOfSpeeches>149</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c30" characterName="Proteus">
         <plays>
            <Title playName="2Gents" />
         </plays>
         <speeches>
            <numberOfSpeeches>147</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c31" characterName="Macbeth">
         <plays>
            <Title playName="Macbeth" />
         </plays>
         <speeches>
            <numberOfSpeeches>146</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c32" characterName="King Edward IV (Plantagenet)">
         <plays>
            <Title playName="Henry6p2" />
            <Title playName="Henry6p3" />
            <Title playName="Richard3" />
         </plays>
         <speeches>
            <numberOfSpeeches>144</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c33" characterName="Parolles">
         <plays>
            <Title playName="AWW" />
         </plays>
         <speeches>
            <numberOfSpeeches>141</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c34" characterName="Cassius">
         <plays>
            <Title playName="Caesar" />
         </plays>
         <speeches>
            <numberOfSpeeches>140</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c35" characterName="Robert Shallow">
         <plays>
            <Title playName="Henry4p2" />
            <Title playName="Wives" />
         </plays>
         <speeches>
            <numberOfSpeeches>136</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c36" characterName="Don Pedro">
         <plays>
            <Title playName="Ado" />
         </plays>
         <speeches>
            <numberOfSpeeches>135</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c37" characterName="Benedick">
         <plays>
            <Title playName="Ado" />
         </plays>
         <speeches>
            <numberOfSpeeches>134</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c38" characterName="Troilus">
         <plays>
            <Title playName="Troilus" />
         </plays>
         <speeches>
            <numberOfSpeeches>131</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c39" characterName="Isabella">
         <plays>
            <Title playName="Measure" />
         </plays>
         <speeches>
            <numberOfSpeeches>129</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c40" characterName="Queen Elizabeth">
         <plays>
            <Title playName="Henry6p3" />
            <Title playName="Richard3" />
         </plays>
         <speeches>
            <numberOfSpeeches>129</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c41" characterName="Earl of Kent">
         <plays>
            <Title playName="Lear" />
         </plays>
         <speeches>
            <numberOfSpeeches>127</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c42" characterName="Claudio">
         <plays>
            <Title playName="Ado" />
         </plays>
         <speeches>
            <numberOfSpeeches>125</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c43" characterName="Leontes">
         <plays>
            <Title playName="WT" />
         </plays>
         <speeches>
            <numberOfSpeeches>125</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c44" characterName="Duke of Gloucester">
         <plays>
            <Title playName="Henry5" />
            <Title playName="Henry6p1" />
            <Title playName="Henry6p2" />
         </plays>
         <speeches>
            <numberOfSpeeches>122</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c45" characterName="Pistol">
         <plays>
            <Title playName="Henry4p2" />
            <Title playName="Henry5" />
            <Title playName="Wives" />
         </plays>
         <speeches>
            <numberOfSpeeches>122</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
   <characters>
      <character characterUri="http://cct.brookes.ac.uk/p00601/characters/c46" characterName="Pericles">
         <plays>
            <Title playName="Pericles" />
         </plays>
         <speeches>
            <numberOfSpeeches>121</numberOfSpeeches>
         </speeches>
      </character>
   </characters>
</charactersTable>
1
xmlxquery

1 Answers

1
votes

The threshold of 40 characters seems very high to me, your data contains plays with at most 7 characters. The query

for $character in /charactersTable/characters/character
for $title in $character/plays/Title/@playName
group by $title
order by count($character) descending, $title
return concat($title, ': n = ',  count($character), '; ',
    string-join(sort($character/@characterName), ', '))

returns:

Henry4p2: n = 7; Earl of Warwick, Falstaff, Henry IV, Henry V, Hostess Quickly, Pistol, Robert Shallow
Henry6p3: n = 7; Earl of Warwick, Henry VI, King Edward IV (Plantagenet), Queen Elizabeth, Queen Margaret, Richard III (Duke of Gloucester), Richard Plantagenet (Duke of Gloucester)
Henry5: n = 6; Duke of Gloucester, Earl of Warwick, Falstaff, Henry V, Hostess Quickly, Pistol
Henry6p2: n = 6; Duke of Gloucester, Earl of Warwick, Henry VI, King Edward IV (Plantagenet), Queen Margaret, Richard Plantagenet (Duke of Gloucester)
Richard3: n = 6; Henry VI, King Edward IV (Plantagenet), Queen Elizabeth, Queen Margaret, Richard III (Duke of Gloucester), Richard Plantagenet (Duke of Gloucester)
Henry6p1: n = 5; Duke of Gloucester, Earl of Warwick, Henry VI, Queen Margaret, Richard Plantagenet (Duke of Gloucester)
Henry4p1: n = 4; Falstaff, Henry IV, Henry V, Hostess Quickly
Wives: n = 4; Falstaff, Hostess Quickly, Pistol, Robert Shallow
Ado: n = 3; Benedick, Claudio, Don Pedro
Caesar: n = 3; Antony, Brutus, Cassius
Othello: n = 3; Desdemona, Iago, Othello
Troilus: n = 3; Cressida, Pandarus, Troilus
2Gents: n = 2; Proteus, Valentine
Antony: n = 2; Antony, Cleopatra
Coriolanus: n = 2; Coriolanus, Menenius Agrippa
Lear: n = 2; Earl of Kent, Lear
Measure: n = 2; Isabella, Vincentio
12Night: n = 1; Sir Toby Belch
AWW: n = 1; Parolles
AYLI: n = 1; Rosalind
Hamlet: n = 1; Hamlet
LLL: n = 1; Biron
Macbeth: n = 1; Macbeth
Pericles: n = 1; Pericles
Richard2: n = 1; Henry IV
Romeo: n = 1; Romeo
Shrew: n = 1; Petruchio
Timon: n = 1; Timon
WT: n = 1; Leontes

You can formulate your two queries in a similar style to the one I showed above. The first one gathers all characters of each play, filters the plays by cast size and then returns all distinct characters (try a more sensible cast size, e.g. 5):

for $character in /charactersTable/characters/character
for $title in $character/plays/Title/@playName
group by $title
group by $cast := count($character)
where $cast >= 40
return distinct-values($character/@characterName)

The second one finds out the cast size of all plays, groups plays with the same size together and returns the group with the highest value:

(
  for $character in /charactersTable/characters/character
  for $title in $character/plays/Title/@playName
  group by $title
  group by $cast := count($character)
  order by $cast descending
  return string-join($title, ', ')
)[1]