1

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.

Improve this question
1
  • Is the XML constructed from various different steps / multiple queries or a single query? Is this something that is beyond what the 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
0

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.

Improve this answer
5
  • I am not sure if I was clear but you seem to be going the developer route which is good. The basic question is create the XML in T-SQL stored procedure or a CLR stored procedure. The former being a huge mess and the latter being a nice clean abstraction of the XML file that is easy to maintain and debug? – Mike_1142 Apr 3 '15 at 2:18
  • @Mike_1142, in terms of ease of use I would always choose .NET's XML libraries over SQL's XML functions. However, the performance of a CLR stored procedure using .NET XML compared to SQL XML functions could be very significant. If the procedure is only being run once a day or something, that may not matter though. Without knowing more about what the stored procedure is doing to generate the XML, it's hard to be more specific. – nateirvin Apr 3 '15 at 14:01
  • I understand. I just took a look at the code again and it would be really hard for me to explain what it is doing because I do not really. Would you have any recommendations for information on design of these types of things. Modularity would be good but how to incorporate it into an overall good design. – Mike_1142 Apr 3 '15 at 17:32
  • @Mike_1142, if you're talking about the design of the stored procedure, I would recommend a pattern when the procedure generates/outputs normal tabular data, and then it's up to the C# code to build XML. SQL's decent at reading XML, but not so great for building it. – nateirvin Apr 3 '15 at 17:51
  • Yes that is what I meant and the answer was exactly what I needed. It is a large undertaking but would be very worthwhile if the design objectives were met. – Mike_1142 Apr 3 '15 at 23:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.