GROUP BY and aggregate functions not supported with FOR XML AUTO

microsoft.public.sqlserver.programming

22 Sep 2005 10:36 PM

MittyKom

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.

22 Sep 2005 10:42 PM

Jerry Spivey

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

Show quote

"MittyKom" <Mitty***@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.

22 Sep 2005 10:43 PM

David Portas

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
--