1

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

Dio Phung
  • 5,944
  • 5
  • 37
  • 55

3 Answers3

2

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.

Community
  • 1
  • 1
Dio Phung
  • 5,944
  • 5
  • 37
  • 55
  • It looks like your data is actually XML. If so, you don't need to jump through so many hoops. Specifically, `select cast(xml_cache as xml) FROM [dbo].[NZF_topic] AS nt WHERE nt.id LIKE '%nzf_1609%'` should work. – Ben Thul Mar 10 '14 at 14:57
  • I tried, but since the XML contain empty space, I'm seeing this error: `Msg 9410, Level 16, State 1, Line 1 XML parsing: line 1, character 23, whitespace expected` My purpose is just to retrieve the raw content of the column to check whether the content is truncated or not. So this workaround is fine by now. – Dio Phung Mar 11 '14 at 03:20
0

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.

jean
  • 4,159
  • 4
  • 31
  • 52
  • I've already tried this suggestion to increase the textsize, well it didn't work as the non-XML output is limited at 65535 characters. My column can contain up to 80,000 characters. – Dio Phung Mar 10 '14 at 11:06
0

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

Razvan Socol
  • 5,426
  • 2
  • 20
  • 32