SQL Server truncating XML return value

Question

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.

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.)

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.

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).

Why is the sproc truncating the XML?

Thanks, Jay

Martin Smith · Accepted Answer · 2017-09-11 19:01:36Z

2

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):

edited Sep 11, 2017 at 19:01

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

answered Nov 4, 2013 at 19:17

birdus

7,60417 gold badges66 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wade Hatler Over a year ago

It's so long ago I can't really remember the scenario, but here's what I do now. I am now using the Dapper ORM, and when I need to return an XML column, I just cast it to a string, since I have to pick it apart anyway. I do something like Field = convert(varchar(max), (select .... from ... for xml path('recname'), root('xml')). That comes back as a string, and I simply deserialize it with XmlSerializer...Deserialize(). It seems to work OK. I'm using dapper for column mapping, so I have to do some tricks to decode the XML at the right time, but it all works well enough.

Collectives™ on Stack Overflow

SQL Server truncating XML return value

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related