FOR XML PATH results in SSMS truncated to 2034 characters

Question

I'm using SQL Server 2008 R2.

I'm concatenating strings together using "for xml path('')".

I have set the Tools->Options->SQL Server->Results to Grid options to max.

I have set the Tools->Options->SQL Server->Results to Text options to max.

Executing the query in Grid mode and copying the one row/one colum results, I see the return value is limited to 2034 characters.

Executing the query in Text mode and copying the one row/one colum results, I see the return value is limited to 1124 characters.

How can I ensure the returned value isn't truncated?

Possible duplicate of [XML Output is Truncated in SQL](http://stackoverflow.com/questions/2742651/xml-output-is-truncated-in-sql) — Rowland Shaw, Feb 17 '17 at 10:04
@RowlandShaw keep it up, you almost have 30,000 internet points! — DonBecker, Feb 18 '17 at 00:30

score 6 · Accepted Answer · edited May 23 '17 at 12:34

6

Answer found here:

For some reason, converting the XML to a string returns a non-truncated string.

Not sure why it works, but it does.

edited May 23 '17 at 12:34

Community

answered Sep 17 '13 at 18:01

DonBecker

3

There is an issue with the .NET SQL Server Client that causes this (see http://support.microsoft.com/kb/310378). Microsoft suggest using SqlCommand.ExecuteXmlReader instead of SqlCommand.ExecuteReader to work around the issue. – Andrew Skirrow Dec 10 '14 at 13:47
I've provided an example powershell script if anyone's interested in [another topic](http://stackoverflow.com/a/27613908/2226686) – Rod Dec 23 '14 at 04:33

1 Answers1