I am currently looking for an alternative to writing a large, complex XML file based on a known specification. Currently the file is written in t-sql as a stored procedure. The output is in a results column which is pretty useless when there are thousands of rows and each needs to be written to individual files. It is also a nightmare (for me) to debug. Break points frequently encompass 1000's of lines of code.
To overcome the XML to file problem I altered the stored procedure to insert the XML to a staging table and then use a query in SSIS to pull the data out of the table, a C# script to create the files and then an SSIS extension from codeplex to zip and send the file to an external SFTP server. With a little more automation this process is workable and meets the project criteria.
I have searched google and these forums to try and find a feasible alternative and I was unsuccessful. The ideal solution would not require additional commercial software and open source would be a bit of an issue as well. I am hoping CLR would be an alternative as writing the code in .NET and its xml libraries seem to be more abstract rather than hand coding text. I see no support for CLR or much at all about SQL to XML. XML to SQL is by far more prevalent.
I hope I have covered what I have tried and the efforts I put in to find an alternate solution on my own.
Let me know if anyone has some suggestions, even if they do not meet the ideal solution criteria.
First question post by the way.
FOR XML
clause can generate? And what XML goes into what exact files? All XML into a single file? Multiple XML documents into a single XML file? Multiple XML documents, each into their own individual file? I am trying to figure out where the complexity lies in this process since, on the surface, it is rather simple to generate XML from a query. And saving it, zipping it, and FTPing it are quite simple via SQLCLR (though SFTP, as opposed to FTPs, can be tricky). – Solomon Rutzky Apr 4 '15 at 17:17