Illegal XML character out of the extended event

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,

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:

select xml_event_data = CONVERT(xml, replace(event_data,CHAR(3),'')), *
from sys.fn_xe_file_target_read_file
            (
                '...\Log\error_*.xet', 
                '...\Log\error_*.xem', 
                null, 
                null
            )

StackExchange Database Administrators Q#1406, answer score: 3