Scenario:
You are working as SQL Server Developer or ETL developer or DBA and you need to load multiple xml files from a folder to SQL Server Table. There are multiple ways to achieve thisYou can use SQL Server Integration Services and create an SSIS Package to load the files from a folder
Or
You can use use TSQL to load the xml files, In this post we are going to use TSQL to load the XML files from a folder to SQL server Table.
How to Import XML files to SQL Server Table from a folder |
Step 1:
Let's create a table with a column of XML data types in which we want to load XML Files from a folder.
USE YourDBName
GO
CREATE TABLE dbo.XMLFilesTable
(
Id INT IDENTITY PRIMARY KEY,
FileName VARCHAR(100),
XMLData XML,
LoadedDateTime DATETIME
)
Step 2:Use below script, Change the folder path in which your XML files are present. The script should load all the xml files from given folder to table.
IF OBJECT_ID('tempdb..#FileList') IS NOT NULL
DROP TABLE #FileList
--Folder path where files are present
Declare @SourceFolder VARCHAR(100)
SET @SourceFolder='C:\Source\'
CREATE TABLE #FileList (
Id int identity(1,1),
FileName nvarchar(255),
Depth smallint,
FileFlag bit)
--Load the file names from a folder to a table
INSERT INTO #FileList (FileName,Depth,FileFlag)
EXEC xp_dirtree @SourceFolder, 10, 1
--Use Cursor to loop throught files
--Select * From #FileList
Declare @FileName VARCHAR(500)
DECLARE Cur CURSOR FOR
SELECT FileName from #FileList
where fileflag=1
OPEN Cur
FETCH Next FROM Cur INTO @FileName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @InsertSQL NVARCHAR(MAX)=NULL
--Prepare SQL Statement for insert
SET @InsertSQL=
'INSERT INTO dbo.XMLFilesTable(FileName, LoadedDateTime,XMLData)
SELECT '''+@FileName+''',getdate(),Convert(XML,BulkColumn ) As BulkColumn
FROM Openrowset( Bulk '''+@SourceFolder+@FileName+''', Single_Blob) as Image'
--Print and Execute SQL Insert Statement to load file
Print @InsertSQL
EXEC(@InsertSQL)
FETCH Next FROM Cur INTO @FileName
END
CLOSE Cur
DEALLOCATE Cur
Step 3: Query the table to take a look if all XML files loaded correctly to SQL Server table.
How to Load XML files a folder to SQL Server Table by TSQL - SQL Server Tutorial |
Wow,great information. I am sure the information on your blog will help others,Thanks.
ReplyDeletejavascript training in chennai
javascript course in chennai
Hibernate Training in Chennai
core java training in chennai
Spring Training in Chennai
QTP Training in Chennai
Manual Testing Training in Chennai
JMeter Training in Chennai
Saved my time! Nice one!
ReplyDeleteSo how does one read the contents of each file stored in this XML table?
ReplyDeleteOk, I managed with a cursor … Below is the script i modified to meet my requirements.
ReplyDeleteCREATE DATABASE OPENXMLTesting
GO
CREATE TABLE MasterData
(Branch varchar(4),
CardNumber varchar(20) ,
AccountID varchar(13))
Go
USE OPENXMLTesting
GO
CREATE TABLE XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY,
FileID Varchar(20),
XMLData XML,
LoadedDateTime DATETIME
)
IF OBJECT_ID('tempdb..#FileList') IS NOT NULL
DROP TABLE #FileList
--Folder path where files are present
Declare @SourceFolder VARCHAR(100)
SET @SourceFolder='E:\XMLFiles\'
CREATE TABLE #FileList (
Id int identity(1,1),
FileName nvarchar(255),
Depth smallint,
FileFlag bit)
--Load the file names from a folder to a table
INSERT INTO #FileList (FileName,Depth,FileFlag)
EXEC xp_dirtree @SourceFolder, 10, 1
--Use Cursor to loop throught files
--Select * From #FileList
Declare @FileName VARCHAR(500)
DECLARE Cur CURSOR FOR
SELECT FileName from #FileList
where fileflag=1
OPEN Cur
FETCH Next FROM Cur INTO @FileName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @InsertSQL NVARCHAR(MAX)=NULL
--Prepare SQL Statement for insert
SET @InsertSQL=
'INSERT INTO dbo.XMLwithOpenXML( fileID,XMLData,LoadedDateTime)
SELECT '''+@FileName+''',Convert(XML,BulkColumn ) As BulkColumn, GETDATE()
FROM Openrowset( Bulk '''+@SourceFolder+@FileName+''', Single_Blob) as Image'
--Print and Execute SQL Insert Statement to load file
Print @InsertSQL
EXEC(@InsertSQL)
FETCH Next FROM Cur INTO @FileName
END
CLOSE Cur
DEALLOCATE Cur
DECLARE @RespXml XML --VARCHAR(500)
DECLARE X_CURSOR CURSOR FOR
Select XMLData from XMLwithOpenXML
OPEN X_CURSOR
FETCH NEXT FROM X_CURSOR INTO @RespXml
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
INSERT INTO MasterData
SELECT Branch,CardNumber ,AccountID
FROM OPENXML(@hDoc, 'CardBatch/CardRecord/Account')
WITH
(
Branch [varchar](4) '../Branch',
CardNumber [nvarchar](16) '../CardNumber',
AccountID [nvarchar](16) 'AccountId'
)
FETCH NEXT FROM X_CURSOR INTO @RespXml
END
CLOSE X_CURSOR
DEALLOCATE X_CURSOR
EXEC sp_xml_removedocument @hDoc
GO
I get the same file processed over and over again using this. Any suggestions?
DeleteMy xmlwithopenxml table does have each of the 42 files in it that I'm trying to process but when I run the query it loads the last one in that list 42 times.
DeleteĐặt vé tại Aivivu, tham khảo
ReplyDeleteVe may bay di My
bay từ mỹ về việt nam
vé máy bay giá rẻ từ Canada về Việt Nam
mua ve may bay tu han quoc ve viet nam
Recording an interview with you is the perfect opportunity for you to clearly and informally state the goals of your company, and to get them out deep and wide to every employee within your organization. Salesforce interview questions
ReplyDelete