We discussed FOR XML in a number of previous posts. SQL Server 2005 added a few enhancements to the FOR XML clause. One of the enhancements added in SQL Server 2005 is the PATH clause which helps to do a great deal of customization/control over the structure XML result being generated. Another keyword added to FOR XML in SQL Server 2005 is the TYPE directive that produces the result as XML data type.

I wrote a series of articles on FOR XML at www.sqlservercentral.com.  You can find articles on AUTO and RAW here, PATH here and EXPLICIT here. In addition, I have added links to several FOR XML posts I wrote previously.

In this article, I have explained how to access the results of a FOR XML query from ADO.NET. SQL Server can stream the output of a FOR XML query. It means that SQL Server need not wait till the query execution completes, to start sending you the data. Instead, it will start sending you the data as a stream. As soon as a chunk of data is available, it is sent to you and SQL Server will continue to execute the query to fetch the rest of the data.

See that the query is still executing, and you started getting part of the XML result. The downside of this is that, if something goes wrong, say the query timed out, you will end up with an incomplete XML document.

SQL Server 2005 introduced TYPE directive that converts the results of a FOR XML query to a well-formed XML. When you use the TYPE directive, SQL Server will not stream the results. Instead, it will read all the needed data, create the result as XML data type which performs the necessary validations to make sure that the XML document is well formed. SQL Server will start sending you the data only after performing all these. This could add some overhead at the server side as well as some delay in getting the results at the client side.

Summary: Use the TYPE directive only if you really need it. By avoiding the TYPE directive you can get some performance benefits in most of the cases. 

FOR XML Tutorials