XML Output is Truncated in SQL
Solution 1
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.
Solution 2
Try the following:
Declare @xmldata xml
set @xmldata = (select ... From test for xml...)
select @xmldata as returnXml
Solution 3
I had the same issue. I changes my Query result setting to Unlimited, but it didnt work. There is a work around. Its not very neat, but if you do want to continue to use SQL Server Management studio and get the results. Tweak the query to convert the xml stored using:
convert(xml,'<xml><![CDATA[' + cast(MyColumnWithXml as varchar(max)) + ']]></xml>')
The complete text is returned. Please note you will have to do some formatting at you end
Solution 4
This worked for me (SSMS 2012): Tools > Options > Query Results > SQL Server > Results to Grid: Maximum Characters Retried: XML data: Unlimited. Then I saved the results of the grid to a file.
Solution 5
Just thought I'd add a quick addition to the answer above. Changing the settings in SSMS 2017 Tools > Options > Query Results > SQL Server > Results to Grid works. However you must then close SSMS entirely and re-open it for the setting to take effect.
Muhammad Akhtar
Senior Software Engineer MCTS - Microsoft Certified Technology Specialist toakhtar - at - hotmail - .com Cell # : 00923014728930 View my MCP Certifications http://stackoverflow.com/tags/asp.net/topusers http://stackoverflow.com/search?tab=votes&q=user%3a97010%20%5basp.net%5d http://www.linkedin.com/profile/view?id=48514505&trk=tab_pro
Updated on July 12, 2022Comments
-
Muhammad Akhtar almost 2 years
I need to return my result set in XML and this works fine, but if the number of records are increased, my xml output is
truncated
here is my queryselect t.id,t.name,t.address from test FOR XML AUTO, ROOT('Response'), ELEMENTS
However I have set some option to increase the output result set like..
Tools --> Options --> Query Results --> SQL Server --> Results to Text --> Maximum number of characters displayed in each column Tools --> Options --> Results --> Maximum characters per column
but still I am unable to get my desired result.
please suggest my solution
EDIT: However when I click on the XML, it open in another query window and all xml shown here, but it not return all xml instead of truncated xml.
Thanks....