Geek Answers Handbook

Size limit for XML data type in SQL 2005

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

When I try to return something larger than the XML string size from my stored proc larger than 44 KB, it simply returns an empty string. I use FOR XML PATH to return hierarchical XML data sets from my stored procedures.

+7
sql xml sql-server sql-server-2005
source share
3 answers

This is 2GB for the xml data type.

Do you get only 44 thousand SSMS or client code? SSMS restricts LOB data in the Tools..Options..Query Results section

Edit, after comment:

SSMS probably changed the value back if you re-enable the options.

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

+13
source share

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

+1
source share

If you see the results in SQL Server Management Studio, it is possible to set how much data it can display, and by default it is only 2 MB.

To change it, follow these steps:
Tools> Options> Query Results> SQL Server> Grid Results> Maximum Characters Received

+1
source share

More articles:


All Articles