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

  • Source Type 200 in SQL Server Import and Export Wizard?
  • SSIS package fails in sql server agent, but not in visual studio. ODBC Connection
  • Grouping by an alias
  • T/F: Using IF statements in a procedure produces multiple plans
  • Interpret deadlock and fix it
  • When increasing the size of VARCHAR column on a large table could there be any problems?
  • SELECT [xml_cache], * FROM [dbo].[NZF_topic] AS nt
    WHERE 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)?

  • Pivot or transpose a table in SQL Server without GROUPING BY
  • Design principles for designing database architecture of financial transaction system?
  • Insert values into SQL Server table using SqlDataReader
  • Clustered indexes on non-identity columns to speed up bulk inserts?
  • JTDS driver not working for Sql Server 2008R2 and Denali Native SSPI library not loaded. Check the java.library.path system property
  • SQL Server search for “ὡ” (U+1F61) matches all?
  • 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 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


    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.