1

I'm executing the following query:

SELECT LocalCCYCode, EquivCCYCode, AsOfDate, DayClosing 
FROM v_FxRate
ORDER BY LocalCCYCode, EquivCCYCode, AsOfDate
FOR XML RAW,  ROOT ('FxRates'), ELEMENTS XSINIL;

SSMS shows the result set contains 12,000+ rows. However, when I copy (and prettify) to VS Code it only shows around 70 rows.

According to this, I can change the settings to increase the number of characters returned.

I tried both methods (in the accepted answer) but neither worked. I also restarted SSMS.

What am I missing?

Dale K
  • 25,246
  • 15
  • 42
  • 71
MyDisplayName
  • 223
  • 5
  • 12
  • Do you get the closing tag for `FxRates` or does it cut off half way through giving you malformed XML? If you get the closing tag it isn't being truncated – Martin Smith May 15 '20 at 19:53
  • 1
    This *sounds* like you're copying out of SSMS and into another application. SSMS will only provide a certain about of MBs of xml data, depending on the setting you input. If you're returning a large set, you would may well be better off using an ETL process to consume and write the XML to a file. At the end of the day, SSMS is an IDE, not a true presentation layer. – Thom A May 15 '20 at 20:04
  • @Larnu SSMS has the option to allow unlimited MB of XML per the answer linked from the question. If that is set it shouldn't be truncating. – Martin Smith May 15 '20 at 20:11
  • It doesn't provide the closing tag. Definitely truncating. I'm manually copying the result into VS Code. I'm trying to learn how to use the FOR XML functionality and also create a large file to see processing/memory limitations when I work with it in C#. – MyDisplayName May 15 '20 at 20:16
  • Did you definitely set the default options for ssms globally? Not just options for the current query window you had open? Go back and check the options are set like this https://stackoverflow.com/a/2760023/73226 – Martin Smith May 15 '20 at 20:19
  • Also make sure the query has definitely stopped executing before you copy it as you can get an incomplete document that way too. It will start streaming the XML to the client as soon as the first rows are available and be visible in SSMS but you need to wait for the query to finish for it to be complete – Martin Smith May 15 '20 at 20:25
  • I ended up doing a Save Results As. – MyDisplayName May 15 '20 at 21:04

0 Answers0