May 10, 2011 at 7:52 am
Hey,
I have this piece of code:
INSERT INTO dbo.TempTable
SELECT * FROM OPENROWSET (BULK 'C:\Temp\Test.xml', SINGLE_BLOB) AS data
It basically inserts all the information of an xml file into a table. However I have more than one xml file and more are added/updated each day so I need to know how to pass the filename as an input parameter. The location is always the same (C:\Temp\ in this case).
May 10, 2011 at 8:58 am
Do you ultimately want to load one XML file at a time from the directory or loop through and load them all at the same time?
If it's just the one file at a time you can use something simple like this (just turn it into a procedure): DECLARE @SQL nvarchar(500)
DECLARE @FileName nvarchar(25)
SET @FileName = 'NewTest.xml'
SET @SQL = '
INSERT INTO dbo.TempTable
SELECT * FROM OPENROWSET (BULK ''C:\Temp\' + RTRIM(@FileName) + ''', SINGLE_BLOB) AS data'
EXECUTE sp_executeSQL @SQL
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
May 12, 2011 at 12:56 am
I want to loop trough and load them all at the same time.
May 12, 2011 at 9:58 am
In that case try this:
(DISCLAIMER :: there's probably much better ways to accomplish this but I prefer this because it's easier to write and modify)
Alos, you'll need to have xp_cmdshell enabled and database mail enabled in order for this to work
SET NOCOUNT ON
/* Declare your variables */
DECLARE @SQL nvarchar(250), @Err int, @Msg varchar(250), @Idx int, @Env nvarchar(65)
DECLARE @EmailBody nvarchar(max), @EmailSubject nvarchar(140), @FileName nvarchar(100)
SET @Env = @@SERVERNAME
/* Read local folder for XML files, store in a temp table for processing */
CREATE TABLE #Files (Idx int IDENTITY(1,1), FName varchar(100))
/* Insert folder file contents into the lookup table */
SET @SQL = 'dir /B C:\xmltest'
INSERT #Files
EXEC MASTER.dbo.xp_cmdshell @SQL
DELETE FROM #Files WHERE FName IS NULL
BEGIN TRY
/* Loops through the files found in the folder and process as needed */
WHILE EXISTS (SELECT TOP 1 Idx FROM #Files)
BEGIN
SELECT TOP 1 @Idx = Idx, @FileName = FName FROM #Files
SET @SQL = '
INSERT INTO dbo.TempTable
SELECT * FROM OPENROWSET (BULK ''C:\Temp\' + RTRIM(@FileName) + ''', SINGLE_BLOB) AS data'
EXECUTE sp_executeSQL @SQL
DELETE FROM #Files WHERE Idx = @Idx
END
END TRY
BEGIN CATCH
SELECT @Err = @@ERROR, @Msg = ERROR_MESSAGE()
BEGIN
SET @EmailSubject = '' + @Env + ' :: ERROR Loading XML File : '+ CONVERT(VARCHAR(19),GETDATE(),121)
SET @EmailBody =
'********************************************************************** ' + CHAR(13)
+ 'ERROR - ' + @Msg + CHAR(13)
+ '**********************************************************************' + CHAR(13)
/* Send Email regarding error */
EXECUTE msdb.dbo.sp_send_dbmail @recipients = 'mydoggiejessie@gmail.com'
,@subject=@EmailSubject
,@body = @emailBody
,@body_format = 'TEXT'
,@sensitivity = 'Confidential'
,@importance='High'
END
END CATCH
DROP TABLE #Files
SET NOCOUNT OFF
GO
Here's the output (using the PRINT()):
INSERT INTO dbo.TempTable
SELECT * FROM OPENROWSET (BULK 'C:\Temp\file1.xml', SINGLE_BLOB) AS data
INSERT INTO dbo.TempTable
SELECT * FROM OPENROWSET (BULK 'C:\Temp\file2.xml', SINGLE_BLOB) AS data
INSERT INTO dbo.TempTable
SELECT * FROM OPENROWSET (BULK 'C:\Temp\file3.xml', SINGLE_BLOB) AS data
INSERT INTO dbo.TempTable
SELECT * FROM OPENROWSET (BULK 'C:\Temp\file4.xml', SINGLE_BLOB) AS data
INSERT INTO dbo.TempTable
SELECT * FROM OPENROWSET (BULK 'C:\Temp\file5.xml', SINGLE_BLOB) AS data
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
May 12, 2011 at 5:12 pm
use ssis and its foreach loop.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply