1

I'm trying to find the quickest way to output XML results to disk from within SQL Server. This needs to be automated and quick as there are thousands of records to process.

These are the options that I am aware of:

  • sp_OACreate & sp_OAMethod
  • xp_cmdshell & osql.exe
  • SSIS package
  • Assembly DLL (SQLBulkCopy Class from .NET)
  • BCP utility

Currently using the first option (sp_OACreate & sp_OAMethod) to create a new file for each result. In my environment, the client statistics show that it takes 374 milliseconds to execute and generate one file (with close to nothing in it).

I came across one article that compares using sp_OACreate & sp_OAMethod with other approaches and it is way slower. However, it does not compare performance for BCP, SSIS, DLL or other approaches. https://www.sqlservercentral.com/articles/writing-to-a-file-using-the-sp_oacreate-stored-procedure-and-osql-the

A list of benchmarks for each option would be useful but I can't seem to find that information online. My last option is to implement and test each option to see which is fastest but I'm hoping someone has some information.

LeSteelBox
  • 415
  • 3
  • 15
  • Does the XML exist, stored in SQL Server or are you creating XML as a result of querying data stored in a relational manner? What is the datatype of the data you want to output? If you are storing blobs of data or if the data is stored in structured tables the storage mechanisms of the data can differ (I believe). Further, I have not worked with either stored XML data or queried XML data much, but aside from XML data stored as blobs or varcharmax or text type situations, we're just talkig about moving pages of data form SQL to file. BCP or SSIS should perform close enough to one another. – jamie May 24 '19 at 18:07
  • 1
    if speed/performance is a concern then it is important to know a bit more detail. At a high level, BCP or SSIS should be your top choices (chose which ever you or other staff are most adept with... BCP will likely involve some OS scripting as well. Also, if you are automating, I'd avoid use of xp_cmdshell - this introduces security risks. xp_cmdshell should be disabled on the server. – jamie May 24 '19 at 18:12
  • Querying data from tables; no blobs or xml data. Single batch processing is set up, i.e. collect the number of records that need to be processed (along with their PKs) then pass one record to a stored procedure that creates one XML file then it moves onto the next record. This part is working fine. – LeSteelBox May 24 '19 at 21:25

0 Answers0