IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DBImportReadXMLfile]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_DBImportReadXMLfile]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DBImportReadXMLfile]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_DBImportReadXMLfile] AS' 
END
GO
ALTER PROCEDURE [dbo].[sp_DBImportReadXMLfile]
		 @FileName					varchar(255) = null
		,@xFile						xml output
		,@errorNumber				int				output
		,@errorMessage				nvarchar(250)	output 
AS
BEGIN

	if(@FileName is null)
	begin 
		set @errorMessage	= 1560
		set @errorNumber	= 'An invalid parameter or option was specified for procedure'
	end
	else
	begin
		set @errorNumber = 0 
		set @errorMessage = space(0)
		declare @sql nvarchar(max)
		set @sql = (N'set @xFile = (SELECT * FROM OPENROWSET(BULK ''' + @FileName + ''',   SINGLE_BLOB) AS x)')

		declare @ParmDefinition nvarchar(500);
		set @ParmDefinition = N'@xFile xml OUTPUT';

		exec sp_executesql @sql, @ParmDefinition, @xFile output 
	end
end






GO