TSQL -> Load XML files to SQLServer table

Suppose that you have number of XML files and you need to import them into SQLServer (2012) table. You can do this by running following TSQL script. After importing files, the content of each file will be stored in separate table record, in the field defined as XML data type. Before you start, list of xml files need to be created. It can be done via command line (let say we have our files in folder "XML_files" on drive D:)
cd D:\XML_files
dir *.xml /b > list.txt

list.txt file will be used in TSQL script as a "files to load list".


-- -----------------------------------------------------
-- [1] read list of xml file names into SQLServer table
-- -----------------------------------------------------

IF EXISTS ( SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[xml_files_list]') AND type in (N'U'))
DROP TABLE dbo.xml_files_list;

CREATE TABLE dbo.xml_files_list
(xmlfile_name Varchar(150) NULL);

-- load list of files
BULK INSERT dbo.xml_files_list
FROM 'D:\XML_files\list.txt'
WITH
(
ROWTERMINATOR = '\n',
FIRE_TRIGGERS
);

-- --------------------------------------------
-- [2] load data from xml files into SQL table
-- --------------------------------------------

IF EXISTS ( SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[xml_data]') AND type in (N'U'))
DROP TABLE dbo.xml_data;

CREATE TABLE dbo.xml_data
(
  id INT Identity(1,1) NOT NULL,
  xmlFileName Varchar(150) NULL,
  xmldata XML NULL
);

-- create errors table
IF EXISTS ( SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[xml_data_defects]') AND type in (N'U'))
DROP TABLE dbo.xml_data_defects;

CREATE TABLE dbo.xml_data_defects
(
  xmlFileName Varchar(150) NULL,
  Err_Message Varchar(150) NULL
);

DECLARE @cmd Varchar(2000), @x_file Varchar(150);

-- define cursor
DECLARE x_files_cursor CURSOR FOR
   SELECT xmlfile_name
   FROM dbo.xml_files_list;

OPEN x_files_cursor;   -- open cursor
FETCH NEXT FROM x_files_cursor INTO @x_file;

WHILE (@@FETCH_STATUS <> -1)
BEGIN

   -- execute bulk insert of every xml file

   BEGIN TRY
     SET @cmd = 'INSERT INTO xml_data (XMLData, xmlFileName) ' +
     'SELECT CONVERT(XML, BulkColumn) AS BulkColumn, ' +
     '''' + RTRIM( LTRIM(@x_file)) + ''' ' +
     'FROM OPENROWSET (BULK ' +
     '''D:\XML_files\' + RTRIM( LTRIM(@x_file)) + ''',' +
     ' SINGLE_BLOB) AS x';
     EXEC (@cmd);
   END TRY

   BEGIN CATCH

     INSERT INTO dbo.xml_data_defects (xmlFileName, Err_Message)
     VALUES (@x_file, 'Corrupted file');

   END CATCH

   -- go to the next record
   FETCH NEXT FROM x_files_cursor INTO @x_file;
END

-- close cursor
CLOSE x_files_cursor;
DEALLOCATE x_files_cursor;





sqlexamples.info