[MSSQL] 2. Xevent파일 테이블 변환 쿼리

drop table if exists dbo.Event_Data
go

select 
	n.value('(@timestamp)[1]', 'datetime2') AS [utc_timestamp],
	n.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(2000)') as sql_text,
	ISNULL(n.value('(action[@name="batch_text"]/value)[1]', 'nvarchar(100)'),'') as batch_text,
	n.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(100)') as client_app_name,
	n.value('(action[@name="client_hostname"]/value)[1]', 'nvarchar(100)') as client_hostname,
    n.value('(@name)[1]', 'varchar(50)') as event_name,
    n.value('(@package)[1]', 'varchar(50)') AS package_name,
    n.value('(data[@name="duration"]/value)[1]', 'int') as duration,
    n.value('(data[@name="cpu_time"]/value)[1]', 'int') as cpu_time,
	n.value('(action[@name="database_id"]/value)[1]', 'int') as database_id,
	n.value('(action[@name="session_id"]/value)[1]', 'int') as session_id,
	n.value('(data[@name="spills"]/value)[1]', 'int') as spills,
    n.value('(data[@name="physical_reads"]/value)[1]', 'int') as physical_reads,
    n.value('(data[@name="logical_reads"]/value)[1]', 'int') as logical_reads,
    n.value('(data[@name="writes"]/value)[1]', 'int') as writes,
    n.value('(data[@name="row_count"]/value)[1]', 'int') as row_count,
    n.value('(action[@name="database_name"]/value)[1]', 'nvarchar(128)') as [database_name],
    n.value('(action[@name="is_system"]/value)[1]', 'nvarchar(20)') as is_system,
    n.value('(action[@name="nt_username"]/value)[1]', 'nvarchar(128)') as nt_username,
    n.value('(action[@name="server_principal_name"]/value)[1]', 'varchar(20)') as server_principal_name,
    ISNULL(n.value('(action[@name="result"]/value)[1]', 'varchar(20)'),'OK') as result,
    n.value('(action[@name="query_hash"]/value)[1]', 'int') as query_hash
INTO dbo.Event_Data
from (select cast(event_data as XML) as event_data
from sys.fn_xe_file_target_read_file('C:\Users\btar\Downloads\report_user_query_0_133195523261700000.xel', null, null, null)) ed
cross apply ed.event_data.nodes('event') as q(n)
go

 

sys.fn_xe_file_target_read_file에 Xevent 파일 경로를 설정하여 테이블 형태로 변환할 수 있습니다.