CODEWITHSUNDEEP
sql-serverxmltexttruncated
Dio Phung
Dio Phung

Reputation: 6282

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 nvarchar(max).

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:

The output is truncated

How do I retrieve the whole content of this column (without modifying the schema)?

Upvotes: 1

Views: 1434

Answers (3)

Razvan Socol
Razvan Socol

Reputation: 5694

This was a limitation of SQL Server Management Studio, but in SSMS 18.2 this limit has been increased. By default it truncates to 65535 characters (instead of 43679), but you can configure it in Tools / Options / Query Results / SQL Server / Results to Grid / Non-XML data to show up to 2097152 characters.

See https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017#new-in-this-release-ssms-182

Upvotes: 0

Dio Phung
Dio Phung

Reputation: 6282

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 (&lt; --> <, &gt; --> >) , we can get the proper output. Well it's not the perfect solution but hey, MS products ain't perfect either.

Upvotes: 2

jean
jean

Reputation: 4350

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

and

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.

Upvotes: 0

Related Questions