Posted by jonathaneggert on 11/10/06 22:50

Well thanks for such a detailed explanation of the answer.

Why can't this be done in SQL Server 2000? It is a sub-query which I
have used extensively in SQL Server 2000--why the problem with FOR XML?



Erland Sommarskog wrote:
> (jonathaneggert@hotmail.com) writes:
> > The following seems to work in SQL Server 2005, but I'm getting the
> > error 'Incorrect syntax near xml' when I run it in SQL Server 2000.
> >
> >
> > select a.accountid , (select street, city, state, zip from account b
> > where a.accountid =b.accountid for xml auto, elements) as xmldata
> > from account a
> >
> > The idea is to return 2 columns:
> > accountid
> > xmldata (address as xml)
> >
> > Assuming the fields are correct, any ideas on what the problem might be?
>
> The problem is simply that you try to achieve something which is not
> possible in SQL 2000.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Rolling up spans without breaks between them
• Prev in forum: Re: How do i find indexes of the columns in SQL server 2000???
• Thread view: Re: FOR XML not working in a subquery

[Reply to this message]