I'm capturing extended events in SQL server. Trying to analyse the trace – converting the event data to xml an error comes up:
Msg 9420, Level 16, State 1, Line 28
XML parsing: line 1, character 731, illegal xml character
Investigating the issue, I find that there is a character of ascii number 3 in the sql_text action value. This value is passed by a client in form of dynamic SQL. This is of little concern here. The problem is that XE cannot handle weird characters in sql_text.
This is how I get to the data causing the trouble:
declare @data nvarchar(max)
declare @id int
SET NOCOUNT ON
if object_id('tempdb.dbo.#xerrors') is null --drop table #xerrors
begin
select CAST(null as XML) as xml_event_data, *
into #xerrors
from sys.fn_xe_file_target_read_file
(
'...\Log\error_*.xet',
'...\Log\error_*.xem',
null,
null
)
alter table #xerrors
add id int identity(1,1) primary key not null
end
while exists(select 1 from #xerrors where (@id is null or ID > @id) and xml_event_data is null)
begin
select top 1 @id = id, @data = event_data from #xerrors where (@id is null or ID > @id) and xml_event_data is null order by id
print @id
update #xerrors set xml_event_data = CONVERT(xml, event_data) where id = @id
end
select ascii(SUBSTRING(event_data, 731, 1)), event_data from #xerrors where id = 11253
And my session setup is:
-- TRIES TO ELIMINATE PREVIOUS SESSIONS
if exists(select 1 from sys.dm_xe_sessions xes where xes.name = 'error_capture')
DROP EVENT SESSION error_capture ON SERVER
GO
-- CREATES THE SESSION
CREATE EVENT SESSION error_capture ON SERVER
ADD EVENT sqlserver.error_reported
(
ACTION (
sqlserver.tsql_stack,
sqlserver.sql_text,
sqlserver.client_hostname,
sqlserver.client_pid,
sqlserver.client_app_name,
sqlserver.is_system,
sqlserver.nt_username,
sqlserver.username,
sqlserver.transaction_id,
sqlserver.session_id,
sqlserver.database_id,
sqlserver.database_context
)
--actions
WHERE Severity > 10
)
ADD TARGET package0.asynchronous_file_target
-- CONFIGURES THE FILE TARGET
(set filename = '...\Log\error.xet' , metadatafile = '...\Log\error.xem')
GO
-- STARTS THE SESSION
ALTER EVENT SESSION error_capture ON SERVER STATE = START
Do you consider this a bug in the XE framework or am I doing something wrong? I believe all events should be valid XML…
The file paths have been intentionaly shortened
Best Answer
Since there are no takers yet, I offer a guesswork answer: Yes, I think it's a bug.
Workaround: replace the illegal character once you know which one it is before converting the data to XML like this: