BEGIN TRANSACTION
DECLARE @xmlDoc XML

SET @xmlDoc =(
SELECT * FROM OPENROWSET (
	BULK 'D:\Studier\MCTS\ML326C - XML i Relationsdatabaser\Projektfiler\faktura_10.xml', SINGLE_BLOB
	) AS xmlData
	)

	INSERT INTO Faktura.XMLFaktura(faktura)
	SELECT @xmlDoc
	
	IF @@ERROR <> 0 
	BEGIN
		ROLLBACK TRANSACTION
		PRINT 'FAIL'
		RETURN
	END
	
	
	
	/*Faktura_ID ska sättas in ifall allt gick bra ifrån XMLFaktura till faktura_id*/
	INSERT INTO Faktura.Faktura(faktura_id, lev_orgnr, kopare, anst_id, fakturanr, datum, totalsum,totalmoms)
	SELECT
	SCOPE_IDENTITY(), 
	@xmlDoc.value('(/FAKTURA/FÖRETAG/ORGNR)[1]', 'char(15)'),
	@xmlDoc.value('(/FAKTURA/KÖPARE/FÖRETAGSNAMN)[1]', 'char(10)'),
	--@xmlDoc.value('(/FAKTURA/KÖPARE/BESTÄLLARE)[1]', 'char(41)') as namnXML,
	(SELECT top 1 anst_id FROM HR.Anställd
		WHERE LTRIM(RTRIM(fornamn)) + ' ' + LTRIM(RTRIM(efternamn)) = @xmlDoc.value('(/FAKTURA/KÖPARE/BESTÄLLARE)[1]', 'CHAR(41)')),
	@xmlDoc.value('(/FAKTURA/FAKTURA_DETALJER/FAKTURA_NR)[1]', 'int'),
	@xmlDoc.value('(/FAKTURA/FAKTURA_DETALJER/DATUM)[1]', 'smalldatetime'),
	@xmlDoc.value('(/FAKTURA/FAKTURA_DETALJER/TOTAL_SUMMA)[1]', 'char(20)'),
	@xmlDoc.value('(/FAKTURA/FAKTURA_DETALJER/TOTAL_MOMS)[1]', 'char(20)')
	
	IF @@ERROR <> 0 
	BEGIN
		ROLLBACK TRANSACTION
		PRINT 'FAIL2'
		RETURN
	END
	
	
		COMMIT TRANSACTION
		PRINT 'Faktura importerad!'

(1 row(s) affected)
Msg 245, Level 16, State 1, Line 29
Conversion failed when converting the nvarchar value 'abc' to data type int.

(1 row(s) affected)
Msg 248, Level 16, State 1, Line 27
The conversion of the nvarchar value '123123123123123123' overflowed an int column.
The statement has been terminated.
FAIL2

(1 row(s) affected)
Msg 245, Level 16, State 1, Line 27
Conversion failed when converting the nvarchar value 'abc' to data type int.

query 1
---------------------------
declare @x int

set @x=346664654376576868689797809000

select 'test'



this will abort only statement and you will get test message also printed

query 2
----------------

declare @x int

set @x='fergrtght5hyjuyjkutkuilyililioluil'

select 'test'

this will abort batch altogether and you will just get error message alone