Catch bad XML characters

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
/*
	Date : 17/01/2018
	Notes: Catches a bad xml character error and displays messages to help with debugging
*/

DECLARE @x VARCHAR(MAX)
SET @x = 'dave' + CHAR(160) + 'smith'

BEGIN TRY
	SELECT CAST(@x AS XML) AS xmlOutput
END TRY
BEGIN CATCH

	DECLARE  @errorMessage VARCHAR(MAX)
			,@errPos INT

	SELECT @errorMessage = ERROR_MESSAGE()
	
	IF PATINDEX('%illegal xml character%', @errorMessage) > 0
	BEGIN  
		SELECT @errPos = SUBSTRING(@errorMessage, 
									PATINDEX('%character %', @errorMessage) + 10,
									PATINDEX('%, illegal%', @errorMessage) - PATINDEX('%character %', @errorMessage) - 10 )

		PRINT 'Error caused by illegal character in XML:'
		PRINT 'Character:' + SUBSTRING(@x,@errPos,1)
		PRINT 'Ascii Code:'	+ CAST(ASCII(SUBSTRING(@x,@errPos,1)) AS VARCHAR(MAX))
		PRINT 'Surrounding text:' + SUBSTRING(@x,@errPos-100,200)
		PRINT ''
	END
    
END CATCH