Search code examples
sql-serverssmssql-server-2014ssms-2014

How do I get a FOR XML AUTO result to be more than 256 characters from SSMS?

I've found various solutions to this issue but they don't seem to work. I have a query that returns a single result that has about 6 nested rows in it. The rough shape is: 

<ItemGroup> 
  <Item />
  <Item />
  <Item />
  <Item />
</ItemGroup>

with each Item having about 8 elements in it. I'm using FOR XML AUTO, TYPE to produce the XML. The query has a nested query in it to produce the inner rows. So far so good. However when I run the query my XML is always trimmed at 256 characters. The first suggestion to fix this is to change the XML data to Unlimited (or 5MB, they both should be adequate) for its max return size in Tools, Options, Query Results, SQL Server, Results to Grid, XML Data. Seems sensible. Didn't work.

If I output to grid I get an error about an unclosed string:

unclosed literal

My next attempt to fix was just to output the results to a file. I don't see any options for changing the amount of data (max) to return there, so I assume it's unlimited. However, dumping the result to a file resulted in the exact same problem: 1 row x 256 columns in the result text.

Can this be fixed? Is this a bug in SSMS 2014? The exact version is 12.0.2000.8. My next step will be to just send the result rows to a node.js script and manipulate the XML there instead.

Edit: I ran the query through node and the shaping I was doing with FOR XML AUTO, TYPE is clearly goofed up. The result doc was 35MB. But if I run the query without the shaping I get a 9 row result set. Still: I would not have been able to figure that out with SSMS, even when outputting directly to a file.

Solution

  • It's been a month and I didn't get much of a resolution to this. My real solution was to not use FOR XML AUTO, TYPE. Not because it's broken, but because it's too easy to goof up and result in long cycles of cryptic debugging. Better to shape the XML by hand (in Node in my case) unless you really really need to understand how to shape XML in SQL. Read the comments above if you are curious why I went that way.