Load Multiple XML file data into the table

In the previous post we see how to load the XML file data into the table. In this post see how to load the Multiple XML files using sql script.

We have a source folder where we have the XML files and we need to load these file data into the table.

  

Below is the table.

CREATE TABLE Xml_File_Details(                 FileID int IDENTITY(1,1) NOT NULL,                 SearchKeyword nvarchar(50) NULL,                 XMLData  xml NULL, )

Using the below Sql script we can load the files from the source folder and loading it into the database.

DECLARE @SQL VARCHAR(8000),                                 @File_Count int,                                 @Number int,                                 @File_Nm varchar(100),                                 @File_Path varchar(150); DECLARE @DirList TABLE (List VARCHAR(250)) ; DECLARE @FileList TABLE (id int identity(1,1), File_Nm VARCHAR(150), File_Path VARCHAR(150)) ;  Set @Number=1;  INSERT INTO @DirList EXEC master.dbo.xp_cmdshell 'Dir H:\SSIS1\Source\XML_Files\*.Xml'; INSERT INTO @FileList(File_Nm,File_Path) SELECT trim(replace(REVERSE(LEFT(REVERSE(List),CHARINDEX(' ', REVERSE(List)))),'.xml','')),'H:\SSIS1\Source\XML_Files\'+Trim(REVERSE(LEFT(REVERSE(List),CHARINDEX(' ',REVERSE(List))))) FROM @DirList WHERE List LIKE '%.Xml%';  set @File_Count=(select Count(*) from @FileList);  while (@Number<=@File_Count) Begin select @File_Nm=File_Nm, @File_Path =File_Path from @FileList where id=@Number  SET @SQL = ' Insert into Xml_File_Details (SearchKeyword,XMLData) SELECT  '''+@File_Nm+''' AS SearchKeyword ,XMLDATAT FROM ( SELECT CONVERT(XML, XMLCol, 0)FROM OPENROWSET (BULK '''+@File_Path+''' ,SINGLE_BLOB) AS XMLSource (XMLCol) ) AS XMLFileToImport (XMLDATAT) ' EXEC (@SQL);  set @Number=@Number+1; end;

See the records in the table 

Running this script 

Records are inserted successfully.

See the result in the table.  

Get the expected result.