Menu
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.
336
The xml data type is a built-in data type in SQL Server and is somewhat similar to other built-in types such as int and varchar. As with other built-in types, you can use the xml data type as a column type when you create a table as a variable type, a parameter type, a function-return type, or in CAST and CONVERT.
In SQL Server, you usually store XML data in a column configured with the xml data type. The data type supports several methods that let you query and modify individual elements, attributes, and their values directly within the XML instance, rather than having to work with that instance as a whole.
The stored representation of xml data type instances can't exceed 2 GB.
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)
121
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
