MS SQL Server 2008R2 : How to retrieve the content of a large text column?
I had a table with a column named xml_cache, containing large number of characters up to 80,000. The column is declared as
I had problem retrieving the content of this column using SQL Management Studio
SELECT [xml_cache], * FROM [dbo].[NZF_topic] AS nt WHERE nt.id LIKE '%nzf_1609%'
Wwhen I ran this SQL, the output grid contain truncated data, exactly at the 43680-th characters.
See the output grid: screenshot – large size:
How do I retrieve the whole content of this column (without modifying the schema)?
2 Solutions collect form web for “MS SQL Server 2008R2 : How to retrieve the content of a large text column?”
After I post the question, then I saw this related question. The work around is to wrap the column inside
<xml><![CDATA[ long content ]]</xml> :
SELECT convert(xml,'<xml><![CDATA[' + cast(xml_cache as varchar(max)) + ']]></xml>'), * FROM [dbo].[NZF_topic] AS nt WHERE nt.id LIKE '%nzf_1609%'
Then with use some simple search & replace (
>) , we can get the proper output. Well it’s not the perfect solution but hey, MS products ain’t perfect either.
First there area limitation at the Query Analyzer tool.
Click right mouse button over the query
You ill find two fields:
Execution -> General -> SET TEXTSIZE
Results -> Grid – > Max characters retrieved
Anyway maybe you cannot get that large text using query analyzer. It’s happen due to query analyzer is a development tool and don’t make sense retrieving a big text no human can read.