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

  • How to remove duplicate entries in SQL using selected columns only?
  • SQL Sum duration by hour and day
  • PHP Delete Link Unlinks Files from Folders but Doesn't Remove Row from MSSQL Table
  • Executing set of SQL queries using batch file?
  • Table column split to two columns in sql?
  • SQL Server 2005 Reporting Services - Configuring the Unattended Account - Invalid domain\alias
  • 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)?

  • Call stored procedure for each XML element without cursors
  • How to create a local database in visual studio?
  • Update large number of rows - SQL Server 2005
  • Restore Azure SQL Database on a schedule
  • recursive query with union in sql server
  • Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?
  • 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 (&lt; –> <, &gt; –> >) , 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

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.