By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,361 Members | 1,683 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,361 IT Pros & Developers. It's quick & easy.

Simple group by with Xquery

P: n/a
Hello,

I am newbie in Xquery and i would like to do a simple group by date and
ID.

so a piece of my xml file.
<Authors>
<Author>
<ID>1</ID>
<Date>30.07.2000</Date>
<NTS>56</NTS>
</Author>
<Author>
<ID>1</ID>
<Date>30.06.2000</Date>
<AI>0.00707</AI>
</Author>
<Author>
<ID>1</ID>
<Date>31.07.2000</Date>
<AI>0.00633</AI>
</Author>
<ID>1</ID>
<Date>31.05.2000</Date>
<TVS>2.33</TVS>
</Author>
<ID>1</ID>
<Date>30.06.2000</Date>
<TVS>2.54</TVS>
</Author>
<ID>1</ID>
<Date>31.07.2000</Date>
<TVS>2.46</TVS>
</Author>
....
</Authors>

I would like to group by date and author ID have the TVS, AI and NTS
for every date instead of having seperate records for each date

For example:

<Author>
<ID>1</ID>
<Date>30.07.2000</Date>
<NTS>56</NTS>
<AI>0.00633</AI>
<TVS>2.46</TVS></Author>

I tried to do several codes without success. Any suggestion for this
problem?

Ina

Jul 31 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
For info this my xquey stuff

<TEST>
{
for $i in doc("authors.xml")//Info/Auhors/Author
for $date in $i/Date
let $nts:= fn:sum($i/NTS)
let $ai := fn:sum($i/AI)
let $tvs := fn:sum($i/TVS)

return
<Test>
{ $i/ID }
{ $date }
<NTS>{ $nts }</NTS>
<AI>{ $ai }</AI>
<TVS>{ $tvs }</TVS>
</Test>
}
</TEST>

I do no know how to do that in sql

select Date, ID, sum(isnull(NTS,0)) NTS, sum( isnull(AI,0)) AI,
sum(isnull(TVS,0) TVS
from Author
group by Date, ID

in******@gmail.com wrote:
Hello,

I am newbie in Xquery and i would like to do a simple group by date and
ID.

so a piece of my xml file.
<Authors>
<Author>
<ID>1</ID>
<Date>30.07.2000</Date>
<NTS>56</NTS>
</Author>
<Author>
<ID>1</ID>
<Date>30.06.2000</Date>
<AI>0.00707</AI>
</Author>
<Author>
<ID>1</ID>
<Date>31.07.2000</Date>
<AI>0.00633</AI>
</Author>
<ID>1</ID>
<Date>31.05.2000</Date>
<TVS>2.33</TVS>
</Author>
<ID>1</ID>
<Date>30.06.2000</Date>
<TVS>2.54</TVS>
</Author>
<ID>1</ID>
<Date>31.07.2000</Date>
<TVS>2.46</TVS>
</Author>
...
</Authors>

I would like to group by date and author ID have the TVS, AI and NTS
for every date instead of having seperate records for each date

For example:

<Author>
<ID>1</ID>
<Date>30.07.2000</Date>
<NTS>56</NTS>
<AI>0.00633</AI>
<TVS>2.46</TVS></Author>

I tried to do several codes without success. Any suggestion for this
problem?

Ina
Jul 31 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.