Posted by Erland Sommarskog on 11/10/06 23:21

(jonathaneggert@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, 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: Rolling up spans without breaks between them
• Thread view: Re: FOR XML not working in a subquery

[Reply to this message]