SQL2008 中 XML 持续时间元素的总和

发布于 2024-08-26 09:34:08 字数 1226 浏览 1 评论 0原文

我有一个 XML 列，其中包含有关我的游戏的信息。这是信息的示例。

<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>

我正在尝试设计一个 XML 查询来获取 movetime 元素的总和。基本上我需要每个玩家移动时间的总和。因此，使用上述示例，玩家 A 的总移动时间为 5 秒，玩家 B 的总移动时间为 3 秒。

这是我目前正在使用的 XML 查询，

SELECT GameHistory.query('declare default element namespace "http://my.name.space"; data(/game/move/movetime)') AS Value FROM GamesWHERE Id=560

我是 XSLT / XPATH 函数的新手：P

UPDATE

movetime 元素当前包含完整的持续时间；因此，如果玩家需要 1:1:23 进行移动，那么它将保存为 PT1H1M23S

虽然如果更容易，我确实有能力更改此设置，以便它仅存储总秒数。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

若水微香 2024-09-02 09:34:08

我不确定这在 SQL Server 中是否有效，但返回 PlayerAs 移动总和的简单查询是：

declare default element namespace "http://my.name.space";
sum(/game/move[player="PlayerA"]/movetime/xs:dayTimeDuration(.))

请注意，xs:dayTimeDuration 过去被称为 xdt:dayTimeDuration;我不确定 SQL Server 使用哪个版本。

XQuery 只允许您对 xs:dayTimeDuration 和 xs:yearMonthDuration 值求和，因此这就是我向 dayTimeDuration 添加显式转换的原因。

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.

回复收藏 0 原文

~没有更多了~