MS SQL Server 2008R2:如何检索大文本列的内容?
我有一个表，该表的列名为 xml_cache ，其中包含最多80,000个字符.该列声明为nvarchar(max).
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).
使用SQL Management Studio检索此列的内容时遇到问题
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%'
当我运行此SQL时，输出网格包含截断的数据，恰好在第43680个字符处.
Wwhen I ran this SQL, the output grid contain truncated data, exactly at the 43680-th characters.
查看输出网格:屏幕截图-大尺寸:
如何检索此列的全部内容(不修改架构)?
How do I retrieve the whole content of this column (without modifying the schema)?
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%'
然后使用一些简单的搜索&替换(<-> <，>-> >)，我们可以获得正确的输出.嗯，这不是完美的解决方案，但是，MS产品也不完美.
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.