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

8 comments:

  1. So how does one read the contents of each file stored in this XML table?

    ReplyDelete
  2. Ok, I managed with a cursor … Below is the script i modified to meet my requirements.

    CREATE 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


    ReplyDelete
    Replies
    1. I get the same file processed over and over again using this. Any suggestions?

      Delete
    2. My 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
  3. 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