question

halciber avatar image
halciber asked

Handling XML output to a file

Hello, Several years ago, I build a system for a customer which produced XML output from a query. The calling VB .NET program calls BCP to run the stored procedure, and outputs the data to an XML file. This worked really well for many years, but the customer had a problem. The problem was that the file output is essentially a long single line of data. The pseudo code of the stored procedure looks like this: - Make temporary tables - Do a couple of SELECTS to fill temp tables - Do a SELECT ... FOR XML PATH... In a recent upgrade, the customer requested that I changed the output so there are line feeds at the end of certain tags. This makes reading the data easier, and it has some other benefits to the customer. I couldn't find anything in the BCP options to handle this, so I made the following change to the query: - Make temporary tables - Do a couple of SELECTS to fill temp tables - Do a SELECT INTO XML type - SET a STRING variable = XML type - Go through STRING variable an insert at the end of certain tags. This works fine for queries returning under 30K records of about 1500 characters each. However, SQL Server crashed when attempting to handle a query of 60K records. The major difference in queries is transferring the XML data into the STRING variable. When looking up the limits of the STRING, I found that the upper limit is 2^31 characters, which seemed like more than enough space. Has anyone had to output large XML files with line feeds? Is there a better method than using BCP? Or would I be better off importing the output into my program, and using the .NET XML tools to output this data to a file? Any thoughts or advice would be welcome. Best Regards, halciber
xmlbcp
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
This site runs by you voting. For each helpful answer below, click on the thumbs up next to those answers. If any one answer lead to a solution, indicate this by clicking on the thumbs up next to that answer.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
Wouldn't it be better to leave the XML alone? It is a format of data to be read by an application not a human. If they want to make it human-readable then they should use an XML prettifier/beautifer in their editor of choice.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

halciber avatar image halciber commented ·
Hi Kev, I had suggested this, but they have a few reasons that go beyond just having a human readable form of the data. For me, I confess that I'd like to solve this problem. I thought my solution was clever, until the server crashed. Thanks, halciber
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
OK. No worries. I always look for the simplest answer first.....
0 Likes 0 ·
Phil Factor avatar image
Phil Factor answered
I may have misunderstood the problem but would this article help? [Converting String Data to XML and XML to String Data][1] [1]: https://www.simple-talk.com/content/article.aspx?article=1433
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

halciber avatar image
halciber answered
Hi Phil, Thank you for responding. Ironically, I did use Robert Sheldon's article to handle the conversion from XML to the string(s). The issue is adding line feeds to the ends of tags. To give you a better idea of what I'm doing here is an example: DECLARE @xml varchar(MAX) -- -- do some things to fill in the temp tables -- SET @xml = ( SELECT ... /* query to put temp table data into xml output*/ FOR XML... ) DECLARE @out AS varchar(MAX) -- retrieve the contents of a tag from the -- @StartPosition to the @EndPosition, (which are calculated elsewhere) -- then insert a line feed SET @out = SUBSTRING(@xml, @StartPosition, @EndPosition) + char(10) -- recalculate @StartPosition & @EndPosition, then repeat the line above Note the "+char(10)" is what makes this a little different than just the straight conversion described in the article. I believe the problem I'm having is with the larger record sets returned. I hope this helps make my problem clearer. Thanks, Mike Goldweber
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.