FOR XML streamed?

We are trying to write an XSLT report generator that is pipelined throughout all stages. The first step in the process is to get XML out of our database. We are using 2005 April CTP. The white paper by Michael Rys in the MSDN library seems to indicate that the FOR XML streaming behavior of SQL 2000 is still present in 2005. However, it seems to hint that if you use the TYPE directive to return your XML in an actual native XML field, then the XML is no longer streamed. The entire document may first be created and then sent back all at once as a scaler XML field.

Here are the 2 FOR XML clauses we are expirimenting with:

"For XML Path('PurchaseOrder'), Type, Root('PurchaseOrderReport')" - sends back an XML field instance.

"For XML Path('PurchaseOrder'), Root('PurchaseOrderReport')" - sends back an nvarchar(max) instance.

Can anyone answer if using the TYPE directive eliminates the streaming behavior and therefore defeats our attempt to pipeline?

Thanks!

The TYPE directive will create an XML datatype instance and thus will "Blob" on the server before sending the result back to give you the guarantee of a well-formed XML datatype instance.

So: yes, it eliminates the streaming. You can still pipeline after that on the client side though for the rest of your pipeline.

Best regards
Michael|||Thanks for the reply Michael!

Aside from the guarentee of well formed XML, are there other advantages/disadvantages to using the TYPE directive that we should concider when trying to decide between the typed or non-typed route?|||The TYPE directive constructs an XML data type instance, so you can use it for

(a) nested FOR XML
(b) assignment to XML columns, variables and parameters
(c) querying the XML data type result using XQuery

at the server. If your intent is to get the XML to the client in a streamable manner, and (a)-(c) don't apply, then you are better off without the TYPE directive. This also saves you the cost of data conversion to XML data type.

Hope this helps.

Thank you,

Shankar
Program Manager, Microsoft SQL Server