Sum of XML duration elements in SQL2008
I have a XML column that holds information about my games. Here's a sample of the information looks like.
<game xmlns="http://my.name.space" >
<move>
<player>PlayerA</player>
<start movetype="Move">EE5</start>
<end movetype="Move">DF6</end>
<movetime>PT1S</movetime>
</move>
<move>
<player>PlayerB</player>
<start movetype="Move">CG7</start>
<end movetype="Move">DE6</end>
<movetime>PT3S</movetime>
</move>
<move>
<player>PlayerA</player>
<start movetype="Move">FD3</start>
<end movetype="Move">EG8</end>
<movetime>PT4S</movetime>
</move>
</game>
I'm trying to design an XML query to take the sum of my movetime element. Basically I need the sum of each players move time. So using the above 开发者_如何学运维sample, PlayerA would have a total move time of 5 seconds and PlayerB would have a total move time of 3 seconds.
Here's the XML query that I've been currently been working with
SELECT GameHistory.query('declare default element namespace "http://my.name.space"; data(/game/move/movetime)') AS Value FROM GamesWHERE Id=560
I'm a newbie to XSLT / XPATH functions :P
UPDATE
The movetime element currently contains a full duration; so if the player takes 1:1:23 to make a move then it will save as PT1H1M23S
Although if it's easier, I do have the power to change this so that it stores total seconds only.
I'm not sure whether this works in SQL server, but a simple query to return the sum of PlayerAs moves is:
declare default element namespace "http://my.name.space";
sum(/game/move[player="PlayerA"]/movetime/xs:dayTimeDuration(.))
Note that xs:dayTimeDuration used to be called xdt:dayTimeDuration; I'm not sure which version SQL server uses.
XQuery will only allow you to sum xs:dayTimeDuration and xs:yearMonthDuration values, so this is why I have added an explicit cast to dayTimeDuration.
精彩评论