I have been working on the task of loading hierarchal XML data files for processing and have over come several obstacles. I thought I would share the solution and see if there are better ways.
This solution is targeted to SQL Server 2008 and as such avoids using the OPENXML method. Instead it uses OPENROWSET with the BULK option. Second, the XML is hierarchal and is not conducive to the Bulk loading process. Since many processes could be using the
functionality at the same time, there was a desire to avoid using tables to store the XML temporarily. To let each process control its own file name, it was necessary to allow for variable file names. But, the OPENROWSET requires a string constant. Therefore,
dynamic SQL is used in association with a local variable to provide file name flexibility. Putting all the pieces together resulted in the code below.
Given the requirements, are there other ways to accomplish this task?
DECLARE @XMLdata xml;
DECLARE @XMLFileName nvarchar(MAX);
DECLARE @SQL nvarchar(MAX);
SET @SQL = 'SELECT @xmldataOUT = BULKCOLUMN FROM OPENROWSET(BULK ''' + @XMLFileName + ''', SINGLE_BLOB) as X;';
EXECUTE sp_executesql @SQL, N'@xmldataOUT xml OUTPUT', @xmldataOUT=@XMLdata OUTPUT;