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
Thanks for your efforts in sharing this post with us. This was really awesome. kindly keep continuing the great work.
ReplyDeleteSpoken English Classes in Chennai Anna Nagar
Spoken English Class in Porur
Spoken English Class in Vadapalani
Spoken English Class in Thiruvanmiyur
Spoken English Class in Chennai
Best English Speaking Classes in Mumbai
English Speaking Course in Mumbai
IELTS Training in Chennai
IELTS Coaching in Chennai
IELTS Mumbai
I gathered lots of information from your blog and it helped me a lot. Keep posting more.
ReplyDeleteSalesforce Training in Chennai
salesforce training institute in chennai
Salesforce Course
ccna Training in Chennai
Ethical Hacking course in Chennai
PHP Training in Chennai
Web Designing Training in Chennai
Salesforce Training in Anna Nagar
Salesforce Training in Vadapalani
Salesforce Training in Thiruvanmiyur
The article is so informative. This is more helpful for our
ReplyDeletebest software testing training in chennai
best software testing training institute in chennai with placement
software testing training
courses
software testing training and placement
software testing training online
software testing class
software testing classes in chennai
best software testing courses in chennai
automation testing courses in chennai
Thanks for sharing.
This is the first & best article to make me satisfied by presenting good content. I feel so happy and delighted.By Learn Digital Marketing Course Training in Chennai it will help to get Digital Marketing Training with Placement Institute in Chennai. If you Learn Social Media Marketing Training with Placement Institute in Chennai, you will get job soon.
ReplyDeleteLearn Best Digital Marketing Course Training in Chennai in professional institute to get reputed job.
I like the helpful info you provide in your articles. I’ll bookmark your weblog and check again here regularly. I am quite sure I will learn much new stuff right here! Good luck for the next!
ReplyDeleteWeb Designing Course in Chennai | Web Designing Training in Chennai
Mobile Application Development Courses in chennai
Data Science Training in Chennai | Data Science courses in Chennai
web designing classes in chennai | web designing training institute in chennai
Great Article. Thank you for sharing! Really an awesome post for every one.
ReplyDeleteIEEE Final Year projects Project Centers in Chennai are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation. For experts, it's an alternate ball game through and through. Smaller than expected IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble. Final Year Project Domains for IT It gives you tips and rules that is progressively critical to consider while choosing any final year project point.
Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Spring Framework Corporate TRaining the authors explore the idea of using Java in Big Data platforms.
Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai
Such a wonderful article and I feel that it is best to write more on this topic. Thank you so much because i learn a lot of ideas about it. Keep posting...
ReplyDeleteDigital Marketing Course In Kolkata
Web Design Course In Kolkata
SEO Course In Kolkata
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.
DeleteIt is an excellent blog. Your post is very good and unique. I am eagerly waiting for your new post. Thanks for sharing and keep sharing more good blogs.
ReplyDeleteimpact of social media
applications of artificial intelligence in real world
advantages of php
rpa roles and responsibilities
salesforce developer interview questions and answers
salesforce interview questions and answers pdf
Đặ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