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
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.
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