xmlxquery

How to get sum of nodes values in XQuery?


In below xml, there are two types of information related to parts and orders. XML:

<mo>
        <parts>
        <part pno="10506">          
            <pname>Land Before Time I</pname>
            <qoh>200</qoh>
            <price>319.99</price>
            <level>20</level>
        </part>
        <part pno="10000">          
            <pname>Bottle</pname>
            <qoh>2</qoh>
            <price>3.00</price>
            <level>2</level>
        </part>
        <part pno="10508">          
            <pname>Land Before Time 3</pname>
            <qoh>202</qoh>
            <price>2.00</price>
            <level>22</level>
        </part>
        <part pno="10509">          
            <pname>Cycle</pname>
            <qoh>202</qoh>
            <price>1.00</price>
            <level>22</level>
        </part>
    </parts>
    <orders>    
        <order ono="1000" >
            <receivedDate>1967-08-17</receivedDate>
            <shippedDate>1967-08-13</shippedDate>
            <items>
                <item>
                    <partNumber>10508</partNumber>
                    <quantity>2</quantity>
                </item>
            </items>
        </order>
        <order ono="1001" >
            <receivedDate>1968-08-14</receivedDate>
            <shippedDate>1968-08-11</shippedDate>
            <items>
                <item>
                    <partNumber>10000</partNumber>
                    <quantity>2</quantity>
                </item>

            </items>
        </order>

        <order ono="1022">
            <receivedDate>1995-02-14</receivedDate>
            <shippedDate>1995-02-13</shippedDate>
            <items>
                <item>
                    <partNumber>10000</partNumber>
                    <quantity>1</quantity>
                </item>
                <item>
                    <partNumber>10508</partNumber>
                    <quantity>2</quantity>
                </item>
                <item>
                    <partNumber>10509</partNumber>
                    <quantity>3</quantity>
                </item>
            </items>
        </order>

    </orders>
</mo>

Requirement: I want to get the total price of products in order 1022 via XQuery. I've written below xquery to get the sum of prices of parts in order no 1022.

XQuery:

for
$o in /mo/orders/order,
$p in /mo/parts/part
where
data($o/@ono) = ("1022")
and
data($p/@pno) = $o/items/item/partNumber
return fn:sum($p/price)

But, this query returns the parts prices but not the total of prices. Its result is: 3 2 1

Anybody know how to get the sum of prices ?


Solution

  • You're summing up the individual values. Wrap fn:sum() all around the query calculating the individual prices per item.

    fn:sum(
      for $item in /mo/orders/order[@ono='1022']/items/item
      return /mo/parts/part[@pno = $item/partNumber]/price * $item/quantity
    )