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.
Best Answer
Your current setup seems to be on the right track. I would cut SSIS out of the equation - the stored procedure INSERTs into the staging table, and then a C# app pulls the data out, creates the XML files, and SFTPs the files off to the destination. I realize that's more a 'developer' solution than an 'DBA' solution, but flat files and SFTP are not things well-suited to SQL environments.