MS SQL Server 2008R2:如何检索大文本列的内容? [英] MS SQL Server 2008R2 : How to retrieve the content of a large text column?

问题描述

我有一个表，该表的列名为 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.

这篇关于MS SQL Server 2008R2:如何检索大文本列的内容?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！