SQL Server 2008 2mb Limit for XML?

Question

I am trying to infer a long XML result from SMSS.

When I right-click on the results and save the results as ..., I can only get a 2 MB file

I changed the settings in SMSS using Tools -> Options -> Query Results -> SQL Server -> Results to Grid so that the XML data is unlimited.

However, does it still seem to truncate my XML results?

So, how can I get around this problem and output the XML result to a file?

thank

Answer 1

I think I've seen this before, and this is a limitation of the results grid. Try to output the results directly to a file and see what happens.

Answer 2

bcp "SELECT * FROM database.dbo.table FOR XM L RAW" queryout c:\Archivo.xml -Syourserver -T -c -r -t