Xml Output Is Truncated in SQL

XML Output is Truncated in SQL

I've had the same problem with management studio and xml result sets. I've solved this by getting the output programatically with c#, with SqlDataReader.

FOR XML PATH results in SSMS truncated to 2034 characters

Answer found here:

https://stackoverflow.com/a/5658758/609058

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

Not sure why it works, but it does.

How to prevent sqlcmd truncation with for xml path query result

I ran into the same issue today, I used the -y0 option. My output looks correct now. Thought I would post my findings so it might help others running into the same thing.

sqlcmd -Sxxxxxxx -E -dmaster -h-1 -y0 -Q" my query that produces long results in here;" > "D\:out.txt"

-h-1 removes the column headers too.

How to stop truncating of data when outputting XML from ReportServer using data returned from a stored procedure with Powershell (large XML file)

Use -MaxCharLength parameter of Invoke-SQLCMD command. By default it 4000.

See Invoke-SqlCmd doesn't return long string?

sqlplus report - XML data being truncated

This might help:

The default width of datatype columns
is the width of the column in the
database. The column width of a LONG,
CLOB, NCLOB or XMLType defaults to the
value of SET LONGCHUNKSIZE or SET
LONG, whichever is the smaller, so I
set BOTH LONG and LONGCHUNKSIZE
to 32K and it worked beautifully.

SQL Server truncation and 8192 limitation

You can export the data to a flat file which will not be truncated. To do this:

1. Right click the Database
2. Click Tasks -> Export Data
3. Select your Data Source (defaults should be fine)
4. Choose "Flat File Destination" for the Destination type.
5. Pick a file name for the output.
6. On the "Specify Table Copy or Query", choose "Write a query to specify the data to transfer"
7. Paste in your query

Remaining steps should be self explanatory. This will output the file to text and you can open it in your favorite text editor.

Some Python requests are being truncated when saving to SQL

64K is kind of a magic number, especially with XML, but also some providers and other software will issue short SET TEXTSIZE commands (e.g. 32K or 64K) on your behalf. You can override this with:

SET TEXTSIZE -1;

But, typically, when people say their text has been truncated at 64K, it's not actually the case, they're just believing what a UI is showing them. To be certain, don't measure the output of the UI, check the source!

SELECT DATALENGTH(large_column) FROM dbo.table WHERE <this row>;

I suspect you are storing XML data in a non-XML column (or converting it to a string on output), and you are copying the results from the grid output in SSMS, and then measuring the length of that string. Which would make you think the data stored is invalid, but it's not. You are just a victim of the max output by default for grids:

You can increase that number, but I'll admit I haven't played with it that much. For XML specifically (and sometimes even not for XML), it is much better to display the output as a proper XML column. This makes it a clickable cell in the grid and, when you click, it opens a document that is not truncated the way text output is (well, unless you exceed the default max of 2 MB, which you also increase to 5MB or, if you're really brave, unlimited).

For some other potential workarounds, see:

• How do you view ALL text from an ntext or nvarchar(max) in SSMS?
• how to get the full resultset from SSMS

---

Related Topics