create and store large XML output in SQL

Question

I have a query that gives approximately four hundred thousand records [400000], up to 18mb.
I parsed the output to XML output, using:

for xml path('url'), Root('urlset')

Now, query result doesn't show complete XML. when I try to view in XML SQL window, not able to export in an XML file.

ERROR:

I have done below practices as suggested in other posts:

• Run This Script to enable xp_cmdshell
• Increase XML data capacity to Unlimited in Options >> Result to Grid

Still the same error! How to resolve this?

Answer 1

After a while, I tried what @gofr1 referred in his comment.

Above practice doesn't become effective on opening new Query window/ Refreshing the Database connection.

It works ONLY AFTER Restarting SSMS.

update NOTE: With this solution, you can see Results in Grid, but if you try to export it to XML file. you will get the same error.

Exception of type 'System.OutOfMemoryException' was thrown.

UPDATE:

so, here I decided to pass the XML output to an application and let the C# generate XML file in the desired folder.

As Google sitemap XML limit is : 50,000. So, one should create the number of sitemaps each containing max 50k records.

Note: Google allows 1000 sitemap files for each domain.

EDIT:

Google has increased the maximum limit from 1,000 sitemaps to 50,000 Sitemaps, a sitemap file size can be maximum up to 50mb. This is a huge increase in capacity.

References:

1. seroundtable/archives/021559
2. searchengineland/google-bing-increase-file-size-limit-sitemaps-files
3. searchenginejournal/google-bing-increase-sitemap-file-size-limit-50

Answer 2

This is because SSMS not able to show a large amount of data in the result window. You can write a utility and dump the data into the file for better readability.