Storing the XML file into the table

 To storing the XML file into the SQL Server we need to take the XML data type. XML is one of the options for Data exchange. We are getting the data in the XML file we need to load that file's data into the table.

See the below file    

Creating a table to store the files data.

CREATE TABLE Xml_File_Details ( FileID int identity(1,1) primary key, SearchKeyword NVARCHAR(50), XMLData XML );

These files we are loading.  

Below is the script to insert the records in this table.

Insert into Xml_File_Details (SearchKeyword,XMLData) SELECT  'Computer' AS SearchKeyword ,XMLDATAT FROM ( SELECT CONVERT(XML, XMLCol, 0)FROM OPENROWSET (BULK 'H:\SSIS1\Source\XML_Files\Computer.Xml' ,SINGLE_BLOB) AS XMLSource (XMLCol) ) AS XMLFileToImport (XMLDATAT) union all  SELECT  'Fantasy' AS SearchKeyword ,XMLDATAT FROM( SELECT CONVERT(XML, XMLCol, 0)FROM OPENROWSET (BULK 'H:\SSIS1\Source\XML_Files\Fantasy.Xml' ,SINGLE_BLOB) AS XMLSource (XMLCol) ) AS XMLFileToImport (XMLDATAT) union all SELECT  'Horror' AS SearchKeyword ,XMLDATAT FROM( SELECT CONVERT(XML, XMLCol, 0)FROM OPENROWSET (BULK 'H:\SSIS1\Source\XML_Files\Horror.Xml' ,SINGLE_BLOB) AS XMLSource (XMLCol) ) AS XMLFileToImport (XMLDATAT) union all SELECT  'Romance' AS SearchKeyword ,XMLDATAT FROM( SELECT CONVERT(XML, XMLCol, 0)FROM OPENROWSET (BULK 'H:\SSIS1\Source\XML_Files\Romance.Xml' ,SINGLE_BLOB) AS XMLSource (XMLCol) ) AS XMLFileToImport (XMLDATAT) union all SELECT  'Science_Fiction' AS SearchKeyword ,XMLDATAT FROM ( SELECT CONVERT(XML, XMLCol, 0)FROM OPENROWSET (BULK 'H:\SSIS1\Source\XML_Files\Science_Fiction.Xml' ,SINGLE_BLOB) AS XMLSource (XMLCol) ) AS XMLFileToImport (XMLDATAT);

Records inserted successfully.   

See the records in the tables.