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