SQL Server 截断 XML 返回值 [英] SQL Server truncating XML return value

问题描述

我正在调试我们组织中现有的 sproc.它试图返回一个 2047 字节长的 XML 字符串(使用for XML auto").

I'm debugging an existing sproc in our organization. It's trying to return an XML string (using "for XML auto") that is 2047 bytes long.

当我在 SSMS 中运行 sproc 时，返回值出现在结果窗口中.如果我单击该值(显示为超链接)，则会打开一个新窗口并显示整个字符串.(顺便说一下，字符串是一个 XML 元素，所有数据都包含在属性中.这不是我的设计，但我正在使用.)

When I run the sproc in SSMS, the return value appears in the results window. If I click on the value (shown as a hyperlink), a new window opens and displays the whole string. (By the way, the string is a single XML element with all the data contained in attributes. Not my design, but that's what I'm working with.)

但是，如果我在结果窗格中查看结果(通过拖出列的宽度)，那么最后几个字节将被截断.我只看到 2034 个字符.

If, however, I view the result in the results pane (by dragging out the width of the column), then the last few bytes are truncated. I see only 2034 characters.

因为它只有 2034 个字符，所以它不是 SSMS 结果窗口的限制(默认为 2MB，现在设置为无限制).这是发送到调用此 sproc 的服务的完全相同的字符串(通过 ADO.NET 和 ExecuteScalar).

As it's only 2034 characters, it's not a limit of the SSMS results window (2MB by default, now set to Unlimited). This is the exact same string that gets sent to the service that is calling this sproc (via ADO.NET and ExecuteScalar).

为什么 sproc 会截断 XML?

Why is the sproc truncating the XML?

谢谢，杰

推荐答案

一位同事发现了这篇 Microsoft KB 文章，标题为 当您使用提供解决方案的 SqlDataReader 对象时，XML 数据行被截断为 2,033 个字符(改用 ExecuteXmlReader):

A co-worker found this Microsoft KB article entitled The XML data row is truncated at 2,033 characters when you use the SqlDataReader object which provides the solution (use ExecuteXmlReader instead):

这篇关于SQL Server 截断 XML 返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！