Kelsey Thornton (1/3/2011)
WayneS (1/1/2011)
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!It's a nice article. Can you explain why do you use the OpenRowset function to load the data into a staging table?
Hi Kelsey,
Very good question. (I especially like the quote from my signature... :-)) In retrospect, I think I should have included this in the article. Thanks for bringing it up!
To answer your question - it's to load the entire XML file into one record into a table, where we can then work with it. The code in reference is:
OPENROWSET(BULK N'C:\SQL\cd_catalog.xml', SINGLE_BLOB)
Notice that we're using the "BULK" and "SINGLE_BLOB" options of OPENROWSET. From the OpenRowset BOL reference in the article, here is what these two options do:
BULK
Uses the BULK rowset provider for OPENROWSET to read data from a file.
SINGLE_BLOB
Returns the contents of data_file as a single-row, single-column rowset of type varbinary(max).
I'm sure that there are other ways to accomplish this (for instance, utilize the sp_OA procedures to start up Scripting.FileSystemObject, open the file, read it line by line, append what you just read into a variable, and when finished reading the file, close it and write the results to a table - and clean up all the objects that you had to create along the way). Currently, I believe that using OpenRowset with the BULK and SINGLE_BLOB options is going to be the easiest, and probably the fastest way.
A discussion of all the various ways to load an external file into a table would lead to an article of it's own - beyond the scope of this "SQL Spackle"! Therefore, I just used the quick/easy way to get the job done.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes