XQuery Question - Conditional sum()

I have an XML doc like this:

<root>
<orderid>123-456</orderid>
<item>
<description>abc</description>
<itemid>999</itemid>
<itemprice>
<component>
<type>A</type>
<amount>1.00</amount>
</component>
<component>
<type>B</type>
<amount>2.00</amount>
</component>
</itemprice>
</item>
<item>
<description>def</description>
<itemid>888</itemid>
<itemprice>
<component>
<type>B</type>
<amount>3.00</amount>
</component>
<component>
<type>C</type>
<amount>4.00</amount>
</component>
</itemprice>
</item>
<item>
<description>ghi</description>
<itemid>777</itemid>
<itemprice>
<component>
<type>A</type>
<amount>5.00</amount>
</component>
<component>
<type>C</type>
<amount>6.00</amount>
</component>
</itemprice>
</item>
</root>

How do I return the sum on component price for type A in this order,
which in this example should be $6.00?

I try to use the following code but it just returns the sum of every
price, not limited to a certain type:

SELECT xmldata.query('
for $p in //root
where $p/orderid = "123-456"
and $p/item/itemprice/component/type = "A"
return $p/item/itemprice/cComponent/amount
')
FROM test

Also, how do I return it as a single row?

Thanks.

Try this:

DECLARE @xml XML

SET @xml = '<root>
<orderid>123-456</orderid>
<item>
<description>abc</description>
<itemid>999</itemid>
<itemprice>
<component>
<type>A</type>
<amount>1.00</amount>
</component>
<component>
<type>B</type>
<amount>2.00</amount>
</component>
</itemprice>
</item>
<item>
<description>def</description>
<itemid>888</itemid>
<itemprice>
<component>
<type>B</type>
<amount>3.00</amount>
</component>
<component>
<type>C</type>
<amount>4.00</amount>
</component>
</itemprice>
</item>
<item>
<description>ghi</description>
<itemid>777</itemid>
<itemprice>
<component>
<type>A</type>
<amount>5.00</amount>
</component>
<component>
<type>C</type>
<amount>6.00</amount>
</component>
</itemprice>
</item>
</root>'

SELECT @xml.query('sum(root/item/itemprice/component[type="A"]/amount)')


"notlkk" wrote:

> I have an XML doc like this:
>
> <root>
> <orderid>123-456</orderid>
> <item>
> <description>abc</description>
> <itemid>999</itemid>
> <itemprice>
> <component>
> <type>A</type>
> <amount>1.00</amount>
> </component>
> <component>
> <type>B</type>
> <amount>2.00</amount>
> </component>
> </itemprice>
> </item>
> <item>
> <description>def</description>
> <itemid>888</itemid>
> <itemprice>
> <component>
> <type>B</type>
> <amount>3.00</amount>
> </component>
> <component>
> <type>C</type>
> <amount>4.00</amount>
> </component>
> </itemprice>
> </item>
> <item>
> <description>ghi</description>
> <itemid>777</itemid>
> <itemprice>
> <component>
> <type>A</type>
> <amount>5.00</amount>
> </component>
> <component>
> <type>C</type>
> <amount>6.00</amount>
> </component>
> </itemprice>
> </item>
> </root>
>
> How do I return the sum on component price for type A in this order,
> which in this example should be $6.00?
>
> I try to use the following code but it just returns the sum of every
> price, not limited to a certain type:
>
> SELECT xmldata.query('
> for $p in //root
> where $p/orderid = "123-456"
> and $p/item/itemprice/component/type = "A"
> return $p/item/itemprice/cComponent/amount
> ')
> FROM test
>
> Also, how do I return it as a single row?
>
> Thanks.
>
>
>