How to Import or load Multiple XML files to SQL Server Table - SQL Server Tutorial

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 this

You 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

6 comments:

  1. 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.

    Learn Best Digital Marketing Course Training in Chennai in professional institute to get reputed job.

    ReplyDelete
  2. 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!
    Web 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

    ReplyDelete