Simple aggregation using xquery

Discussion in 'XML' started by Suma, Jul 24, 2006.

  1. Suma

    Suma Guest

    I dont know xquery , so i was thinking someone in the forum will help
    me out with this easy question

    Suppose I have an instance document given below
    <Root>
    <Author Name='Alice' NumberOfBooks = '1'>
    <Author Name='Bob' NumberOfBooks = '4'>
    <Author Name='Collin' NumberOfBooks = '3'>
    <Author Name='David' NumberOfBooks = '2'>
    </Root>

    Is there a way i can get the total number of books published by all
    authors using a single xquery statement. (similiar to say SQL - select
    sum(col) semantics)

    What i DO NOT WANT to do is gettting all the nodes and doing the sum
    at the client/my application. I would rather have the XML engine handle
    the aggregation


    Suma
     
    Suma, Jul 24, 2006
    #1
    1. Advertisements

  2. Suma

    SL Guest

    "Suma" a écrit :
    sum(/Root/Author/@NumberOfBookds)
     
    SL, Jul 24, 2006
    #2
    1. Advertisements

  3. Suma

    Suma Guest

    Great thanks
    Now what if i need to do a aggregate over groups?
    My repro was simpler than what i intend to do
    <Root>
    <Author ="Alice" Attrib="1">
    <Author ="Alice" Attrib="2">
    <Author ="Bob" Attrib="3">
    <Author ="Bob" Attrib="4">
    </Root>

    I need to return
    Alice 3
    Bob 7
    and
    so on
    Suma
     
    Suma, Jul 24, 2006
    #3
  4. Suma

    SL Guest

    "Suma" a écrit :
    ^^^
    This is not well-formed XML. I assume you have:
    <Author name="Bob" Attrib="4">
    ^^^^
    instead
    It is an XPath issue :

    sum(/Root/Author[@name='Alice']/@Attrib)

    If you want to loop over all possible names, XQuery provide many
    functionnalities, such as the so-called "Flower expression":

    for $author in Book/Author/@name
    return
    <nbrOfBooks>{sum(/Root/Author[@name=$author]/@Attrib)}</nbrOfBooks>

    (I havn't practiced XQuery for some time, and this is not tested!)
     
    SL, Jul 24, 2006
    #4
  5. Suma

    SL Guest

    Sorry:

    - for $author in Book/Author/@name
    + for $author in /Book/Author/@name
    return
    - <nbrOfBooks>{sum(/Root/Author[@name=$author]/@Attrib)}</nbrOfBooks>
    + <nbrOfBooks>{sum(/Book/Author[@name=$author]/@Attrib)}</nbrOfBooks>
     
    SL, Jul 24, 2006
    #5
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.