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.

Solution 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)

Solution 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.

Solution 3

If you are seeing your results in SQL Server Management Studio, there is an option to set how much data it can display, and it defaults to only 2MB.

To change it, follow this path:
Tools > Options > Query Results > SQL Server > Results to Grid > Maximum Characters Retrieved

Comments

[Vikram]

i am getting 44k on SSMS and i tried changing XML data maximum characters to unlimited, but still it does not returns beyond certain limit. though, it works fine on the server.

[ZygD]

According to msdn.microsoft.com/en-us/library/ms187339.aspx it says 2GB for xml datatype on SQL 2008 as well as SQL 2005

[ZygD]

Updated answer: try Grid mode

[Cheeto]

My mistake, I was referencing some bad information outside of MSDN.

[Vikram]

I was already using the grid mode with xml as a link but still it was coming blank. Anyways, it works absolutely perfect on a dev server but not on my machine and the diff is I have express edition, server has enterprise edition. Maybe, it has something to do with that, not sure though.