SQL Server 2008 2mb Limit for XML??

Question

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

When I right click on the results and 'save results as...', I can only get a 2mb file?

I have changed the settings in SMSS via Tools -> Options -> Query Results -> SQL Server -> Results to Grid, for XML data to be unlimited.

However, it still seems to be truncating my XML results?

So, how can I bypass this problem and output my XML result to a file?

Thanks

Answer 1

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

Answer 2

may this option can help you

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