aggregate calculations on xml ?

i've been breaking my head presenting aggregate summary calculations on
higher level nodes (. using sql server 2005 for xml path method. i'd
need output something like this:
<row>
<customer id="1" ocnt="2" icnt="7">
<order id = "o1" icnt="3">
<item id="i1" cnt=1/>
<item id="i2" cnt=2/>
</order>
<order id = "o1" icnt="4">
<item id="i1" cnt=1/>
<item id="i2" cnt=1/>
<item id="i3" cnt=2/>
</order>
<customer>
</row>
how to calculate values icnt and ocnt?
i know one way would be using translations. but i want to do it
directly from single query direct into xml. how to do it? is there a
way to reference xml elements from for xml path query and aggregate
that way? there must be an easy way. thanks in advance, Ed.Hello ubator@.gmail.com,

> how to calculate values icnt and ocnt?
Hows this?
declare @.x xml
set @.x = '<row>
<customer id="1" ocnt="2" icnt="7">
<order id = "o1" icnt="3">
<item id="i1" cnt="1"/>
<item id="i2" cnt="2"/>
</order>
<order id = "o1" icnt="4">
<item id="i1" cnt="1"/>
<item id="i2" cnt="1"/>
<item id="i3" cnt="2"/>
</order>
</customer>
</row>'
select @.x.value('count(/row/customer/order)','int') as ocnt
select @.x.value('sum(/row/customer/order/item/@.cnt)','int') as icnt
go
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Ubator,
You could try something like this :-)
USE Northwind;
WITH x AS (
SELECT
c.CustomerID
,(SELECT o.OrderID AS "@.id" FROM dbo.Orders AS o
WHERE o.CustomerID = c.CustomerID
FOR XML PATH('order'), TYPE
) AS orders
FROM dbo.Customers AS c
)
SELECT
x.CustomerID AS "@.id"
,x.orders.value('count(/order)', 'INT') AS ordercount
,x.orders AS "node()"
FROM x
FOR XML PATH('customer')
GO
HTH
/ Tobias|||Thanks, Tobias, this would be it. I thought there is a chance to submit
it in a single query with no need of procedure but i guess it's not
possible.
I've found also this link that could be useful:
http://www.codecomments.com/archive...6-1-746830.html|||> Thanks, Tobias, this would be it. I thought there is a chance to submit
> it in a single query with no need of procedure but i guess it's not
> possible.
How do you mean?
This is a single query...
/ Tobias