FOR XML not working in a subquery

[jonathaneggert]

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?

[Erland Sommarskog]

(jo************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[jonathaneggert]

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:

(jo************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

(jo************@hotmail.com) writes:

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?

In SQL 2000, FOR XML can only be used in the outermost SELECT, to
produce a one-row, one-column result set. It cannot be used in subqueries,
derived tables. A good reason for this is that in SQL 2000, there is
not really any xml data type. Yet the result set returned by a FOR
XML clause is not really any of the SQL Server data types - it's XML.
It works thanks to some special hooks in the client APIs that can see
that here comes a one-row, one-column result set, which is an XML
document. There is no plumbing to permit FOR XML be composed with other
sorts of data.

This is all different in SQL 2005, where XML is a first-class citizen.

See also Books Online, the topic
XML and Internet Support ->
Retrieving and Writing XML Data ->
Retrieving XML Documents Using FOR XML ->
Guidelines for Using the FOR XML Clause

this topic lists a number of limitations with FOR XML.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx