USE [fias]
GO
/****** Object:  StoredProcedure [dbo].[spXMLBulkLoad]    Script Date: 21.06.2016 10:36:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spXMLBulkLoad] (
	 @@File		SysName
	,@@Schema	SysName
	,@@DataBase	SysName	= '<DefaultDataBase>'
) AS BEGIN -- EXEC dbo.spXMLBulkLoad 'Z:\Path\Data.xml','Z:\Path\Schema.xsd'
	-- EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE WITH OVERRIDE;
	DECLARE	 @ErrCode	Int
		,@OLEXMLBulk	Int
		,@ErrMethod	SysName
		,@ErrSource	SysName
		,@ErrDescript	NVarChar(4000)
	SET @ErrSource = 'c:\fias\XMLBulkError.xml'	--  файл ошибки (OPENROWSET требует константу)
	EXEC @ErrCode = master.sys.sp_OACreate 'SQLXMLBulkLoad.SQLXMLBulkload' ,@OLEXMLBulk OUT
	IF (@ErrCode = 0) BEGIN
		SET	@@DataBase	= 'Provider=SQLOLEDB;Data Source=.;DataBase=' + @@DataBase + ';Integrated Security=SSPI'
		EXEC @ErrCode = master.sys.sp_OASetProperty	@OLEXMLBulk ,'ConnectionString'	,@@DataBase		IF (@ErrCode != 0) BEGIN SET @ErrMethod = 'ConnectionString'	GOTO Error END
		EXEC @ErrCode = master.sys.sp_OASetProperty	@OLEXMLBulk ,'ErrorLogFile'	,@ErrSource		IF (@ErrCode != 0) BEGIN SET @ErrMethod = 'ErrorLogFile'	GOTO Error END
		EXEC @ErrCode = master.sys.sp_OASetProperty	@OLEXMLBulk ,'SchemaGen'	,1		IF (@ErrCode != 0) BEGIN SET @ErrMethod = 'SchemaGen'	GOTO Error END		
		EXEC @ErrCode = master.sys.sp_OASetProperty	@OLEXMLBulk ,'SGDropTables'	,1		IF (@ErrCode != 0) BEGIN SET @ErrMethod = 'SGDropTables'	GOTO Error END				
		EXEC @ErrCode = master.sys.sp_OASetProperty	@OLEXMLBulk ,'CheckConstraints'	,1			IF (@ErrCode != 0) BEGIN SET @ErrMethod = 'CheckConstraints'	GOTO Error END
		EXEC @ErrCode = master.sys.sp_OAMethod		@OLEXMLBulk ,'Execute', NULL	,@@Schema, @@File
		IF (@ErrCode != 0) BEGIN
			SET @ErrMethod = 'Execute'
			DECLARE	 @Exist	Int
				,@Error	XML
			EXEC	master.dbo.xp_FileExist	@ErrSource, @Exist OUT
			IF (@Exist = 1) BEGIN
				SELECT	 @Error		= E.Error
							-- Обход глюка
							+ CASE	WHEN Right(E.Error,1) != '>'	THEN 'lt>'
								ELSE ''				END
				FROM	OPENROWSET(BULK 'c:\fias\XMLBulkError.xml',SINGLE_NCLOB)E(Error) -- Из @ErrSource файла
				SELECT	@ErrDescript	= IsNull(@ErrDescript	,'') + E.Error.value('Description[1]','SysName') + ' '
				FROM	@Error.nodes('/Result/Error')E(Error)
				SELECT	@ErrDescript	= IsNull(@ErrDescript	,'') + E.Error.value('Description[1]','SysName') + ' '
				FROM	@Error.nodes('/Error/Record')E(Error)
			END ELSE
				GOTO Error
		END
		GOTO Destroy
	Error:	EXEC @ErrCode = master.sys.sp_OAGetErrorInfo	@OLEXMLBulk ,@ErrSource OUT ,@ErrDescript OUT
	Destroy:EXEC @ErrCode = master.sys.sp_OADestroy	@OLEXMLBulk
	END ELSE
		SELECT	 @ErrMethod	= 'SQLXMLBulkLoad.SQLXMLBulkload'
			,@ErrSource	= 'sp_OACreate'
			,@ErrDescript	= 'Ошибка создания OLE объекта'
	-- Вывод ошибок
	IF (@ErrMethod IS NOT NULL) BEGIN
		RAISERROR('Ошибка при выполнении метода "%s" в "%s": %s',18,1,@ErrMethod,@ErrSource,@ErrDescript)
		RETURN	@@Error
	END
END