CODE EXAMPLE FOR SQL

importing multiple xml files in azure sql database

CREATE PROCEDURE [BI].[XML_IMPORT_AND_SPLIT] AS
BEGIN
 DECLARE @name_of_file varchar(8000)
 DECLARE cursor_file_names CURSOR FAST_FORWARD
 FOR SELECT NAME_OF_FILE FROM BI.BLOB_FILE_NAME WHERE NAME_OF_FILE LIKE '%.xml'
 OPEN cursor_file_names
 FETCH NEXT FROM cursor_file_names into @name_of_file
 WHILE @@FETCH_STATUS = 0 
 BEGIN 
 EXEC
 (
 'INSERT INTO [BI].[XML_IMPORT](XML_DATA) 
 SELECT CAST(BulkColumn AS XML)
 FROM OPENROWSET
 (
 BULK ''MyStorage/MainFolder/' + @name_of_file + ''',
 DATA_SOURCE = ''EDS_AzureBlobStorage'', 
 SINGLE_BLOB
 ) as XML_IMPORT'
 )
 FETCH NEXT FROM cursor_file_names into @name_of_file
 END
 DEALLOCATE cursor_file_names