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:

test

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?

try to restart SSMS after Increase XML data capacity to Unlimited in Options >> Result to Grid or create new query window and execute your query there — gofr1
– gofr1, Commented Oct 22, 2016 at 13:07

Vikrant · Accepted Answer · 2018-06-28 06:24:47Z

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

This Practice

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:

Vikrant · Accepted Answer · 2016-11-18 08:49:55Z

0

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.

edited Nov 18, 2016 at 8:49

Vikrant

5,05218 gold badges53 silver badges77 bronze badges

answered Oct 25, 2016 at 10:21

gopimanikandan

194 bronze badges

Collectives™ on Stack Overflow

create and store large XML output in SQL

2 Answers 2

EDIT:

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

EDIT:

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related