x

Error creating XML in SQL

I wonder if anyone can advise.

I need to create an XML file to test importing data into an SSIS package from XML, I can't change the format of this so changing the format is not an option. I'm creating the XML File fine with small numbers of rows, but when I try to do a large number of rows I'm getting the message below when I click on the results to view in XML format.alt text

I've tried doing what it recommends and increased the size in the Query Results options to Unlimited under XML Data: but that has not resolved the issue.

Any ideas?

Many thanks
XML.jpg (42.5 kB)
more ▼

asked Apr 17, 2012 at 10:56 AM in Default

Mrs_Fatherjack gravatar image

Mrs_Fatherjack
4.7k 60 62 67

can you include any code you are using to build the XML? How many rows are involved when it errors? Have you tried changing the output to Text (Ctrl + T) rather than Grid ( should be Ctrl + D but currently isnt working for me) or File
Apr 17, 2012 at 11:05 AM Fatherjack ♦♦
The code works fine for smaller samples, when the file gets beyond approx. 13.5 mg it starts erroring. Will try saving it as file or text.
Apr 17, 2012 at 11:17 AM Mrs_Fatherjack
I do not think output to TEXT will work as it has more limitations for xml than in GRID mode.
Apr 17, 2012 at 11:50 AM Sacred Jewel
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

A workaround for this is to change the script that produces the XML to a stored procedure. Then create a text file that looks like:

:XML ON
exec XMLTest

Where XMLTest is the name of the stored procedure.

Then open a command prompt and enter:

c:\sqlcmd -d [DatabaseName] -i xmltest.txt -o xmltest.xml

And this creates a very large XML file.

more ▼

answered Apr 17, 2012 at 01:13 PM

Mrs_Fatherjack gravatar image

Mrs_Fatherjack
4.7k 60 62 67

(comments are locked)
10|1200 characters needed characters left
I would rather try to save the results by right clicking the xml result link -> Save Results as -> Select All files -> Name the file as Name.xml. This way the proper format would be retained. Moreover, I think the "query options" would not do the job, as it is some kind of SSMS is not able to handle huge XML in memory, so giving out of memory exception.
more ▼

answered Apr 17, 2012 at 12:38 PM

Sacred Jewel gravatar image

Sacred Jewel
1.7k 2 4 5

How do you save it as XML from there?
Apr 17, 2012 at 12:45 PM Mrs_Fatherjack
As I said earlier that you have to select the option "All files" from file type while saving the GRID results. Somehow the upload image option is telling me the file type is invalid. So not able to show it graphically :(
Apr 17, 2012 at 01:24 PM Sacred Jewel
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x151

asked: Apr 17, 2012 at 10:56 AM

Seen: 1202 times

Last Updated: Apr 17, 2012 at 01:24 PM