XQuery How can you add up values from similar XML structures with a unique identifier

I have similiar XML structures eg.

<PLAYERS>
<PLAYER>
<NAME>Jack</NAME>
<SCORE>120</SCORE>
</PLAYER>
<PLAYER>
<NAME>Joe</NAME>
<SCORE>100</SCORE>
</PLAYER>
<PLAYER>
<NAME>Jane</NAME>
<SCORE>170</SCORE>
</PLAYER>
</PLAYERS>


and

<PLAYER>
<NAME>Joe</NAME>
<SCORE>40</SCORE>
</PLAYER>
<PLAYER>
<NAME>Jane</NAME>
<SCORE>20</SCORE>
</PLAYER>


I want to get the first data structure, but with the scores added up by the matching element NAME. For this example it would be:

NAME

<PLAYERS>
<PLAYER>
<NAME>Jack</NAME>
<SCORE>120</SCORE>
</PLAYER>
<PLAYER>
<NAME>Joe</NAME>
<SCORE>140</SCORE>
</PLAYER>
<PLAYER>
<NAME>Jane</NAME>
<SCORE>190</SCORE>
</PLAYER>
</PLAYERS>


As you can see, each name which is not present in the second data set should still be showed in the final result. I came up with a few ideas, but the FLOWR expression used in XQuery is a bit confusing for me. Thought about using two for loops.

Any solutions?

1 Answer
1

It is a grouping problem you can solve with group by clause https://www.w3.org/TR/xquery-31/#id-group-by:

group by

declare namespace output = "http://www.w3.org/2010/xslt-xquery-serialization";

declare option output:method 'xml';
declare option output:indent 'yes';


declare variable $players2 as element(PLAYER)* := (<PLAYER>
<NAME>Joe</NAME>
<SCORE>40</SCORE>
</PLAYER>,
<PLAYER>
<NAME>Jane</NAME>
<SCORE>20</SCORE>
</PLAYER>);

<PLAYERS>

for $players in (PLAYERS/PLAYER, $players2)
group by $name := $players/NAME
return
<PLAYER>

$players[1]/NAME,
<SCORE>

sum($players/SCORE)


</SCORE>

</PLAYER>

</PLAYERS>


https://xqueryfiddle.liberty-development.net/b4GWV5

for $x in ($y, $z)

(PLAYERS/PLAYER, $players2)

PLAYER

PLAYERS/PLAYER

$players2

for $players in (PLAYERS/PLAYER, $players2)

$players

(PLAYERS/PLAYER, $players2)

group by

$players

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.