如何在 SQL Server 2008 Management Studio 中查看 text 或 varchar(MAX) 列的完整内容? [英] How do I view the full content of a text or varchar(MAX) column in SQL Server 2008 Management Studio?

问题描述

在这个实时 SQL Server 2008 (build 10.0.1600) 数据库中，有一个 Events 表，其中包含一个名为 Details 的 text 列.(是的，我意识到这实际上应该是一个 varchar(MAX) 列，但是设置这个数据库的人并没有这样做.)

In this live SQL Server 2008 (build 10.0.1600) database, there's an Events table, which contains a text column named Details. (Yes, I realize this should actually be a varchar(MAX) column, but whoever set this database up did not do it that way.)

此列包含我试图通过 SQL Server Management Studio 访问的非常大的异常日志和关联的 JSON 数据，但是每当我将结果从网格复制到文本编辑器时，它都会将其截断为 43679 个字符.

This column contains very large logs of exceptions and associated JSON data that I'm trying to access through SQL Server Management Studio, but whenever I copy the results from the grid to a text editor, it truncates it at 43679 characters.

我在 Internet 上的各个位置都读到过，您可以在 Tools > 中设置为 XML 数据检索的最大字符数.选项 >查询结果SQL Server >Results To Grid 到 Unlimited，然后执行如下查询:

I've read on various locations on the Internet that you can set your Maximum Characters Retrieved for XML Data in Tools > Options > Query Results > SQL Server > Results To Grid to Unlimited, and then perform a query such as this:

select Convert(xml, Details) from Events
where EventID = 13920

(请注意，数据是列根本不是 XML.CONVERT将列转换为 XML 只是我从谷歌搜索中发现的一种解决方法，其他人曾经用来绕过 SSMS 的限制从 text 或 varchar(MAX) 列中检索数据.)

(Note that the data is column is not XML at all. CONVERTing the column to XML is merely a workaround I found from Googling that someone else has used to get around the limit SSMS has from retrieving data from a text or varchar(MAX) column.)

但是，在设置了上面的选项后，运行查询，点击结果中的链接，还是出现如​​下错误:

However, after setting the option above, running the query, and clicking on the link in the result, I still get the following error:

无法显示 XML.发生了以下错误:发生了意外的文件结尾.第 5 行，位置 220160.

Unable to show XML. The following error happened: Unexpected end of file has occurred. Line 5, position 220160.

一种解决方案是增加从服务器为 XML 数据检索的字符数.要更改此设置，请在工具"菜单上，单击选项".

One solution is to increase the number of characters retrieved from the server for XML data. To change this setting, on the Tools menu, click Options.

那么，对如何访问这些数据有任何想法吗?将列转换为 varchar(MAX) 会解决我的问题吗?

So, any idea on how to access this data? Would converting the column to varchar(MAX) fix my woes?

推荐答案

SSMS 只允许 XML 数据的无限数据.这不是默认值，需要在选项中设置.

SSMS only allows unlimited data for XML data. This is not the default and needs to be set in the options.

一种可能在非常有限的情况下起作用的技巧是简单地以如下特殊方式命名列，以便将其视为 XML 数据.

One trick which might work in quite limited circumstances is simply naming the column in a special manner as below so it gets treated as XML data.

DECLARE @S varchar(max) = 'A'

SET @S =  REPLICATE(@S,100000) + 'B' 

SELECT @S as [XML_F52E2B61-18A1-11d1-B105-00805F49916B]

在 SSMS(至少 2012 版到当前 18.3 版)中，这会显示如下结果

In SSMS (at least versions 2012 to current of 18.3) this displays the results as below

单击它会在 XML 查看器中打开完整结果.向右滚动显示B的最后一个字符被保留，

Clicking on it opens the full results in the XML viewer. Scrolling to the right shows the last character of B is preserved,

然而，这确实存在一些重大问题.向查询添加额外的列会破坏效果，额外的行都会与第一行连接.最后，如果字符串包含诸如 < 之类的字符，则打开 XML 查看器将失败并出现解析错误.

However this does have some significant problems. Adding extra columns to the query breaks the effect and extra rows all become concatenated with the first one. Finally if the string contains characters such as < opening the XML viewer fails with a parsing error.

避免 SQL Server 将 < 转换为 < 等问题或由于这些字符而失败的更强大的方法如下(在这里感谢 Adam Machanic).

A more robust way of doing this that avoids issues of SQL Server converting < to < etc or failing due to these characters is below (credit Adam Machanic here).

DECLARE @S varchar(max)

SELECT @S = ''

SELECT @S = @S + '
' + OBJECT_DEFINITION(OBJECT_ID) FROM SYS.PROCEDURES

SELECT @S AS [processing-instruction(x)] FOR XML PATH('')

这篇关于如何在 SQL Server 2008 Management Studio 中查看 text 或 varchar(MAX) 列的完整内容?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！