size limit for XML datatype in SQL 2005

Question

Is there a size limit on the XML data type in SQL 2005?

When I try to return anything more than 44kb size of the XML string from my stored proc, it just returns an empty string. I am using FOR XML PATH to return hierarchical data sets in XML format from my stored procs.

Answer 1

It's 2GB for xml datatype.

Are you only getting the 44k on SSMS or in your client code? SSMS limits LOB type data under Tools..Options..Query Results

Edit, after comment:

SSMS has probably changed the value back if you go into options again.

If you view the results in grid mode, then SSMS displays the xml as a link that opens in a new window with all data (I checked with FOR XML AUTO with a 16000 row table)

Answer 2

According to the documentation, it stores the XML data as a BLOB. So the limit should be 2gb for SQL 2005, and effectively unlimited in SQL 2008.