SQL 2005中XML数据类型的大小限制。

Is there a size limit on the XML data type in SQL 2005?

SQL 2005中的XML数据类型是否有大小限制?

When I try to return anything more than 44kb size of the XML string from my stored proc, it just returns an empty string. I am using FOR XML PATH to return hierarchical data sets in XML format from my stored procs.

当我尝试从存储的proc中返回超过44kb大小的XML字符串时，它只返回一个空字符串。我正在使用XML路径从存储的proc返回XML格式的分层数据集。

3 个解决方案

#1

It's 2GB for xml datatype.

xml数据类型为2GB。

Are you only getting the 44k on SSMS or in your client code? SSMS limits LOB type data under Tools..Options..Query Results

你只在SSMS上获得44k还是在你的客户端代码中?SSMS在Tools..Options下限制LOB类型数据。查询结果

Edit, after comment:

编辑后评论:

SSMS has probably changed the value back if you go into options again.

如果您再次进入选项，SSMS可能已经更改了值。

If you view the results in grid mode, then SSMS displays the xml as a link that opens in a new window with all data (I checked with FOR XML AUTO with a 16000 row table)

如果您以网格模式查看结果，那么SSMS将xml显示为一个链接，该链接在一个具有所有数据的新窗口中打开(我用16000行表检查了xml AUTO)

#2

According to the documentation, it stores the XML data as a BLOB. So the limit should be 2gb for SQL 2005, and effectively unlimited in SQL 2008.

根据文档，它将XML数据存储为BLOB。因此，SQL 2005的限制应该是2gb，而SQL 2008的限制实际上是无限的。

#3

If you are seeing your results in SQL Server Management Studio, there is an option to set how much data it can display, and it defaults to only 2MB.

如果您正在SQL Server Management Studio中查看结果，那么可以设置它可以显示多少数据，并且默认值只有2MB。

To change it, follow this path:
Tools > Options > Query Results > SQL Server > Results to Grid > Maximum Characters Retrieved

要更改它，请遵循以下路径:工具>选项>查询结果> SQL Server >结果到网格>最大字符检索

#1