Hi,

First of all a promise.

I promise to help 5 people if anyone has an answer to my problem ...

I have generated some XML to fill a strongly typed dataset which works fine
except for one thing - if a value is calculated (a SQL Server aggregate) the
dataset cannot receive the value - it is always Zero.

Say that again ...

I am writing a discussion forum for a Sports Club and need to display the
number of Active Discussions in each category. In a stored procedure I
generate some XML used to fill a strongly typed dataset on the client. Here
is an extract of the XML from the stored procedure as viewed in Query
Analyser:

<EventCategories xmlns="x-schema:#Schema7" EventCategoryID="13"
EventCategory="Health And Fitness">
<DiscussionCategories DiscussionCategoryID="5" DiscussionCategory="Getting
Fitter" ActiveDiscussions="1"/>
<DiscussionCategories DiscussionCategoryID="6" DiscussionCategory="Losing
Weight" ActiveDiscussions="0"/>
</EventCategories>

Take a look at the number of ActiveDiscussions in "Getting Fitter" - It is 1

When I fill a strongly typed dataset on the client the number of
ActiveDiscussions is always 0

Here is an extract from VS.NET command window (in break)

?dsdc.DiscussionCategories.Rows(2)("DiscussionCate gory")
"Getting Fitter" {String}
String: "Getting Fitter"
?dsdc.DiscussionCategories.Rows(2)("ActiveDiscussi ons")
0 {Integer}
[Integer]: 0 {Integer}

The number of Active Discussions - Zero! What? How? Why?

OK so maybe you are thinking that the problem lies with the schema for the
strongly typed dataset but no! I'll put the schema at the end of the post,
but to test the schema, rather than calculate the number of discussions in
the stored procedure I simply put the DiscussionCategoryID rather than the
calculated number of Active Discussions. This produces the following XML as
viewed in Query Analyser -

<EventCategories xmlns="x-schema:#Schema8" EventCategoryID="13"
EventCategory="Health And Fitness">
<DiscussionCategories DiscussionCategoryID="5" DiscussionCategory="Getting
Fitter" ActiveDiscussions="5"/>
<DiscussionCategories DiscussionCategoryID="6" DiscussionCategory="Losing
Weight" ActiveDiscussions="6"/>
</EventCategories>

The number of Active Discussions (actually the DiscussionCategoryID which is
not calculated) for "Getting Fitter" = 5

This works fine! Taking a look at the dataset in debug mode of VS.NET -

?dsdc.DiscussionCategories.Rows(2)("DiscussionCate gory")
"Getting Fitter" {String}
String: "Getting Fitter"
?dsdc.DiscussionCategories.Rows(2)("ActiveDiscussi ons")
5 {Integer}
[Integer]: 5 {Integer}

FANTASTIC! The figure in the dataset is that in the XML.

SO WHY DOESN'T THE ORIGINAL XML CORRECTLY FILL THE DATASET?

Answer - because the value was calculated - it is a SQL Server Aggregate.
But how does the dataset know it was calculated? This information doesn't
appear in the XML which is the only way the data is transferred from SQL
Server to the client ... So it must be Black Magic?

I did a little (or actually a lot) of searching for an answer to this
problem and came across the following at
http://www.sqlxml.org/faqs.aspx?faq=10

"You can not use aggregate functions in your query when using the FOR XML
AUTO. I got this text excerpt from Ken Henderson's - The Guru's Guide to SQL
Server Architecture and Internals - Chapter 18 - "NOTE: Currently, AUTO mode
does not support GROUP BY or aggregate functions. The heuristics it uses to
determine element names are incompatible".
So in order to make this work, you would need to use the "EXPLICIT" mode.
Hope that helps someone else as it was driving me crazy!
Additionally, FOR XML itself is incompatible with COMPUTE, so you can't use
it in FOR XML queries of any kind."

OK, but I was using EXPLICIT in the first place. (Stored Procedure below)
Furthermore to isolate the final XML produced from the way in which the
figure was calculated, I put the calculation in a User Defined Function and
then filled an in-memory datatable before finally producing the XML using
EXPLICIT.
__________________________________________________ __________________________
_______________

USER DEFINED FUNCTION:

CREATE function fn_DiscussionsInCategory(
@DiscussionCategoryID int,
@FromDate varchar(11))
returns int
AS
BEGIN

declare @ActiveDiscussions int

select @ActiveDiscussions=count(DiscussionID)
from Discussions
where DiscussionCategoryID = @DiscussionCategoryID
and LastPost>convert(smalldatetime,@FromDate)

return @ActiveDiscussions
return @DiscussionCategoryID

END
__________________________________________________ __________________________
_______________

THE STORED PROCEDURE

CREATE procedure pr_DiscussionCategoriesXML
@FromDate varchar(11)
AS

SET NOCOUNT ON

--Declare an in-memory datatable from which the XML will be generated
declare @DC Table(
Tag int,
Parent int,
EventCategoryID int,
EventCategory varchar(50),
DiscussionCategoryID int,
DiscussionCategory varchar(50),
ActiveDiscussions int)

--Insert parent elements
insert into @DC(Tag,Parent,EventCategoryID,EventCategory)
select 1,
NULL,
EventCategoryID,
EventCategory
from EventCategories
where Active='Y'
and EventCategoryID>0

--Insert Child elements
insert into
@DC(Tag,Parent,EventCategoryID,EventCategory,Discu ssionCategoryID,Discussion
Category)
select 2,
1,
EC.EventCategoryID,
EC.EventCategory,
DC.DiscussionCategoryID,
DC.DiscussionCategory
from EventCategories EC
inner join DiscussionEventCategories DEvC on
EC.EventCategoryID=DEvC.EventCategoryID
inner join DiscussionCategories DC on
DEvC.DiscussionCategoryID=DC.DiscussionCategoryID

--Use the User-Defined Function to calculate the number of Active
Discussions
update @DC set
ActiveDiscussions=dbo.fn_DiscussionsInCategory(Dis cussionCategoryID,@FromDat
e) where Tag=2 --DiscussionCategoryID--

select Tag,
Parent,
EventCategoryID [EventCategories!1!EventCategoryID],
EventCategory [EventCategories!1!EventCategory],
DiscussionCategoryID [DiscussionCategories!2!DiscussionCategoryID],
DiscussionCategory [DiscussionCategories!2!DiscussionCategory],
ActiveDiscussions [DiscussionCategories!2!ActiveDiscussions]
from @DC
order by
[EventCategories!1!EventCategory],
[DiscussionCategories!2!DiscussionCategoryID],
Tag,
Parent
for XML EXPLICIT, XMLDATA

__________________________________________________ __________________________
_______________

Recap:
The User Defined Function works
The stored procedure works
The XML produced is correct
The strongly typed dataset cannot receive the number of Active Discussions
when this is calculated in any way. Even when the calculation is done in a
separate User Defined Function and the results put in a temporary table
before producing the XML using EXPLICIT. So how does the dataset know the
figure was calculated ???????????????

It's a mystery to me - Does anyone have an answer to this? If I had any hair
I would have pulled it all out by now.

Any help greatly appreciated.

Ken
Sheffield, England

__________________________________________________ __________________________
_______________

An extract of the schema for the strongly typed dataset:

<xs:sequence>
<xs:element name="DiscussionCategories"
minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:attribute
name="DiscussionCategoryID" form="unqualified" type="xs:int" />
<xs:attribute name="DiscussionCategory"
form="unqualified" type="xs:string" />
<xs:attribute name="ActiveDiscussions"
form="unqualified" type="xs:int" />
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="EventCategoryID"
form="unqualified" type="xs:int" />
<xs:attribute name="EventCategory"
form="unqualified" type="xs:string" />