I had a large number of XML files that each contained hourly prices for a lot of different locations. I needed to get all of the prices for a single location. I did this by loading all of the files into a table and then querying the table.
First, I needed to change the rights on my database to allow me to run the xp_cmdshell store procedure.
USE MASTER
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO
USE MyTestDB
GO
Then I needed to make the temporary table that would hold my file names and load the file names from the directory.
CREATE TABLE #DIR (fileName varchar(100))
INSERT INTO #DIR
EXEC master..xp_CmdShell 'DIR C:\RTHourly\*.xml /B'
A second table is needed hold the content of my XML files, it is one row for each file. Note the XML data type for the last column called priceData.
create table xmls
(id int identity,
fileName varchar(250),
priceData xml)
Now we are ready to loop thru each file name found in the #DIR table and load it’s contents into the xmls table.
DECLARE @fileName AS varchar(250)
DECLARE @fullPathName AS varchar(250)
DECLARE file_cursor CURSOR FOR
SELECT fileName
FROM #DIR
WHERE fileName like '%.xml'
OPEN file_cursor
FETCH NEXT FROM file_cursor INTO @fileName;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @fileName
set @fullPathName = 'C:\RTHourly\'+ @fileName
-- this must be dynamic sql b/c file name has to be a literal string for OPENROWSET
exec (
'INSERT INTO xmls(fileName, priceData)
SELECT '''+@fileName+''' AS fileName, A.*
FROM OPENROWSET( BULK '''+@fullPathName+''',SINGLE_CLOB)
AS A')
FETCH NEXT FROM file_cursor INTO @fileName;
END
CLOSE file_cursor;
DEALLOCATE file_cursor;
At this point all of the files are loaded so I just need to get the data out for my location “AZ”.
select priceData.value('(//PriceGroup/Prices/Price[@location = "AZ"]/Detail/@utcTimepoint)[1]',
'varchar(150)') as time,
priceData.value('(//PriceGroup/Prices/Price[@location = "AZ"]/Detail/@lmp)[1]',
'real') as price,
fileName
from xmls
order by 1
Now let’s turn off the security hole that we created when we started this little project.
USE MASTER
GO
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO