Uploading XML Files to SQL Server

Uploading XML Files to SQL Server

by jbrooks 18. November 2010 13:14

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
 

Tags:

Development | SQL