24

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 query

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

Muhammad Akhtar
  • 51,913
  • 37
  • 138
  • 191

9 Answers9

65

Try the following:

Declare @xmldata xml
set @xmldata = (select ... From test for xml...)
select @xmldata as returnXml
Dan Is Fiddling By Firelight
  • 5,981
  • 17
  • 77
  • 128
user3791994
  • 651
  • 5
  • 2
  • 2
    This allowed me to avoid using the XmlReader. – Edyn Jun 04 '15 at 01:47
  • 2
    This answer worked great for me and should be marked as the answer. – Lews Therin Jan 12 '16 at 15:03
  • 1
    Works perfect! Thanks. – Itay.B Jan 19 '16 at 10:07
  • 4
    Great tip! Note: works **only** when SQL Server 2014 SSMS is set for 'query results to grid' (ctrl+d). Does not work 'to file' or 'to text'. So I copy/pasted from the grid cell into my editor. – John Apr 07 '16 at 19:06
  • See [this stack overflow post](http://stackoverflow.com/questions/3230424/cant-set-output-of-with-xmlnamespaces-for-xml-path-to-a-variable) if you need to prepend WITH XMLNAMESPACES as the set = ( ... ) syntax does not work with WITH in this case. – John Apr 07 '16 at 19:25
  • This works for Sql Server 2016 FOR JSON PATH as well – Don Rolling Mar 10 '17 at 15:29
5

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.

JohnH
  • 91
  • 1
  • 5
  • Answer that helped me, but as mentioned by Tim Clauss https://stackoverflow.com/a/66141674/10010199 one has to completely restart SQL Server for the change to take effect – St3ve Jan 27 '22 at 15:56
5

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.

Axarydax
  • 16,353
  • 21
  • 92
  • 151
5

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

Vin
  • 942
  • 8
  • 13
4

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.

Tim Clauss
  • 71
  • 2
1

I know this question was asked like 10 Years ago, but still if someone is looking for a workaround which doesn't require any changes in the DB side and is a bit similar to the Accepted answer but not with SqlDataReader.

You could make use of a DataTable and fill it using the SqlDataAdapter. Where your XML data will be divided into multiple rows.

For Example

string xmloutput = "";
var cmd = new SqlCommand("<some sql query that returns data in xml format>", con);
var da = new SqlDataAdapter(cmd);
var dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
    foreach (DataRow dr in dt.Rows)
    {
        xmloutput  += dr[0].ToString();
    }

Note:

  • the above code assumes that the only one column is returned from DB with XML data in it.

And later on, make use of the variable xmloutput.

vikscool
  • 1,293
  • 1
  • 10
  • 24
1

If your XML result set is still being truncated even after changing SSMS Options, use the SQLCMD utility with :XML ON option prior to running your XML statement. Direct your output to a file using the -o switch. SQLCMD is a command line utility that is very simple to use. See http://msdn.microsoft.com/en-us/library/ms162773.aspx.

  • Might only work with MS SQL 2008 or earlier. MS SQL 2012 will cut it off: https://connect.microsoft.com/SQLServer/feedback/details/786004/sqlcmd-with-xml-on-break-lines-on-large-output – Rod Dec 22 '14 at 23:13
0

The XML output you get in Query output window is not really intended for saving valid XML documents. You can get valid XML out of SQL Server when you capture it as a stream using ADO or SQLXML managed classes in .NET

However you can limit the query output sizes by using In SQL Server Management Studio go to Tools >> Options >> Query Results >> SQL Server >> Results to Text >> Maximum number of characters displayed in each column

Dinesh
  • 2,026
  • 7
  • 38
  • 60
0

You can use SQL Management Studio for 2008 against a SQL Server 2005 database engine. If you do that, the defaults should be large enough, but in Tools -> Options -> Query Results -> SQL Server there is a Results to Grid and Results To Text node.

Results to Grid allows you to control the maximum size for XML data explicitly, and the default maximum size is 2MB.

If you don't have a license for SSMS 2008 you should be able to use the express edition for free here.

Emil Lerch
  • 4,416
  • 5
  • 33
  • 43