GROUP BY and aggregate functions not supported with FOR XML AUTO

Hi All
I am trying to ouput the results from my query in the form of XML. The query
is like this:
SELECT a, b, COUNT(S.c ) AS x
FROM s
GROUP BY a,b
ORDER BY a,b
FOR XML AUTO, ELEMENTS
If run this, i get an error like this:
Server: Msg 6821, Level 16, State 1, Line 1
GROUP BY and aggregate functions are currently not supported with FOR XML
AUTO.
Is there any way i can do this? Thank you all in advance.MittyKom,
Try:
SELECT * FROM (SELECT TOP 100 PERCENT a, b, COUNT(c) AS x
FROM s
GROUP BY a,b
ORDER BY a,b ) AS Y
FOR XML AUTO, ELEMENTS
HTH
Jerry
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:2D6F411D-7CA3-4EEB-A05F-1FBF03FFE7E3@.microsoft.com...
> Hi All
> I am trying to ouput the results from my query in the form of XML. The
> query
> is like this:
> SELECT a, b, COUNT(S.c ) AS x
> FROM s
> GROUP BY a,b
> ORDER BY a,b
> FOR XML AUTO, ELEMENTS
>
> If run this, i get an error like this:
> Server: Msg 6821, Level 16, State 1, Line 1
> GROUP BY and aggregate functions are currently not supported with FOR XML
> AUTO.
> Is there any way i can do this? Thank you all in advance.|||SELECT a, b, x
FROM
(SELECT a, b, COUNT(S.c) AS x
FROM S
GROUP BY a, b) AS T
ORDER BY a, b
FOR XML AUTO, ELEMENTS ;
David Portas
SQL Server MVP
--