Aggregate on XML Data Type

Is there a way to aggreate data on the XML data type? I have found some
examples using XQuery, but they all invlove using the Let statement in FLWOR
which is not supported in SQL Server 2005. Are there any examples out there
that shows how to aggregate data on the XML data type?
Thanks
From the BOL:
SELECT ProductModelID,
Name,
Instructions.value('declare namespace
AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
count(/AWMI:root/AWMI:Location)', 'int' ) as WorkCtrCount
FROM Production.ProductModel
WHERE ProductModelID=7
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:DA76A4FD-57CB-455B-81B9-FD9EDD0A69E7@.microsoft.com...
Is there a way to aggreate data on the XML data type? I have found some
examples using XQuery, but they all invlove using the Let statement in FLWOR
which is not supported in SQL Server 2005. Are there any examples out there
that shows how to aggregate data on the XML data type?
Thanks
|||Robert,
It is possible that let is not needed in the examples you are referring to.
Can you supply one of these examples for further examination?
Thanks,
Galex Yen
"Robert" wrote:

> Is there a way to aggreate data on the XML data type? I have found some
> examples using XQuery, but they all invlove using the Let statement in FLWOR
> which is not supported in SQL Server 2005. Are there any examples out there
> that shows how to aggregate data on the XML data type?
> Thanks

Aggregate on XML Data Type

Is there a way to aggreate data on the XML data type? I have found some
examples using XQuery, but they all invlove using the Let statement in FLWOR
which is not supported in SQL Server 2005. Are there any examples out there
that shows how to aggregate data on the XML data type?
ThanksFrom the BOL:
SELECT ProductModelID,
Name,
Instructions.value('declare namespace
AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Product
ModelManuInstructions";
count(/AWMI:root/AWMI:Location)', 'int' ) as WorkCtrCount
FROM Production.ProductModel
WHERE ProductModelID=7
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:DA76A4FD-57CB-455B-81B9-FD9EDD0A69E7@.microsoft.com...
Is there a way to aggreate data on the XML data type? I have found some
examples using XQuery, but they all invlove using the Let statement in FLWOR
which is not supported in SQL Server 2005. Are there any examples out there
that shows how to aggregate data on the XML data type?
Thanks|||Robert,
It is possible that let is not needed in the examples you are referring to.
Can you supply one of these examples for further examination?
Thanks,
Galex Yen
"Robert" wrote:

> Is there a way to aggreate data on the XML data type? I have found some
> examples using XQuery, but they all invlove using the Let statement in FLW
OR
> which is not supported in SQL Server 2005. Are there any examples out ther
e
> that shows how to aggregate data on the XML data type?
> Thanks

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

again a problem with xml datasource extension

it seems to be that the xml datasource ignore empty fields. why?

example:

<?xml version="1.0" standalone="yes"?>
<Head>
<ID>121234</ID>
<Title>Hi</Title>
<Text></Text>
<Amount>3443.90</Amount>
</Head>

Only ID, Title and Amount appearing in the resultset.

any suggestions?

Instead of using autodetection you can specify explicitely what you want in the query

<Query>
...
<ElementPath> Head{ID, Title, Text, Amount} </ElementPath>
</Query>

|||

Dear Sasha, may be you know something about this error:

The reports based on XML is perfectly displayed in development Environment.

But than uploaded to Server, the following occurs:

· An error has occurred during report processing.

o An attempt has been made to use a data extension 'XML' that is not registered for this report server.

Thank you very much.

|||

See this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=376677&SiteID=1

-- Robert

again a problem with xml datasource extension

it seems to be that the xml datasource ignore empty fields. why?

example:

<?xml version="1.0" standalone="yes"?>
<Head>
<ID>121234</ID>
<Title>Hi</Title>
<Text></Text>
<Amount>3443.90</Amount>
</Head>

Only ID, Title and Amount appearing in the resultset.

any suggestions?

Instead of using autodetection you can specify explicitely what you want in the query

<Query>
...
<ElementPath> Head{ID, Title, Text, Amount} </ElementPath>
</Query>

|||

Dear Sasha, may be you know something about this error:

The reports based on XML is perfectly displayed in development Environment.

But than uploaded to Server, the following occurs:

· An error has occurred during report processing.

o An attempt has been made to use a data extension 'XML' that is not registered for this report server.

Thank you very much.

|||

See this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=376677&SiteID=1

-- Robert