Error in For XML Path

[Madiya122]

I have following quary example.

SELECT
   t1.TeamID,
   MemberList = substring((SELECT ( ', ' + FirstName )
                           FROM TeamInfo t2
                           WHERE t1.TeamID = t2.TeamID
                           ORDER BY 
                              TeamID,
                              FirstName
                           FOR XML PATH( '' )
                          ), 3, 1000 )FROM TeamInfo t1
GROUP BY TeamID

Its giving error "Incorrect syntax near the keyword 'FOR'."
Pl help.

[pritaeas]

Have you tried FOR XML AUTO ? Wondering why you don't specify a path?

If you just try:

SELECT FirstName
   FROM TeamInfo
   ORDER BY 
      TeamID,
      FirstName
   FOR XML AUTO

Do you get results?

[Madiya122]

Yes it works but with incorrect results as below.

    <TeamInfo FirstName="Bob"></TeamInfo><TeamInfo FirstName="Jim"></TeamInfo><TeamInfo FirstName="Mary"></TeamInfo><TeamInfo FirstName="Ellen"></TeamInfo><TeamInfo FirstName="Ralph"></TeamInfo><TeamInfo FirstName="Sue"></TeamInfo><TeamInfo FirstName="Bill"></TeamInfo><TeamInfo FirstName="Linda"></TeamInfo>

Expected results is 
TeamID     MemberList
1   Bob, Jim, Mary
2   Ellen, Ralph, Sue
3   Bill, Linda

[pritaeas]

What version of MSSQL are you using?

[Madiya122]

SQL2000

[pritaeas]

Am sure you read from the article where you found this example that it is designed for SQL 2005 and up. Earlier versions don't support the FOR XML.

[Madiya122]

Well, yes. But I missed.
Thanks for your help.