Início / dba / Perguntas / 341283
Accepted
Aleksey Vitsko
Asked: 2024-07-30 05:58:54 +0800 CST

Como converter uma única coluna com dados XML em múltiplas colunas, enquanto consulta o arquivo XEL de Eventos Estendidos

  • 772

Criamos uma sessão de Eventos Estendidos para capturar erros com gravidade 16 ou superior:

CREATE EVENT SESSION [Error_Reporting] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.username)
    WHERE ([severity]>(15)))
ADD TARGET package0.event_file(SET filename=N'https://storageaccount.blob.core.windows.net/logs/Errors.xel',max_file_size=(10240), max_rollover_files=1)
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

Agora podemos consultá-lo executando:

select cast(event_data as xml), timestamp_utc
from sys.fn_xe_file_target_read_file('https://storageaccount.blob.core.windows.net/logs/Errors_0_133667581914690000.xel', NULL, NULL, NULL)

A coluna event_dataé do tipo nvarchar, mas contém valores XML, que representam uma tabela.

insira a descrição da imagem aqui

Pergunta :

Como event_dataa coluna pode ser convertida (dividida) em várias colunas (como UserName, Error_number, Categoria, Destino, Mensagem e assim por diante), quando consultada no editor de texto no SSMS?

Exemplo de dados XML: https://pastecode.io/s/tjg7jo40

sql-server

1 respostas

  1. Best Answer

    Você deve ser capaz de usar algo como

    SELECT 
        error_number = event_data_xml.value('(event/data[@name = "error_number"]/value/text())[1]', 'int'),
        username = event_data_xml.value('(event/action[@name = "username" and @package = "sqlserver"]/value/text())[1]', 'sysname'),
        xft.timestamp_utc
FROM sys.fn_xe_file_target_read_file('https://storageaccount.blob.core.windows.net/logs/Errors_0_133667581914690000.xel', NULL, NULL, NULL) xft
CROSS APPLY (SELECT CAST(xft.event_data AS XML)) CA(event_data_xml)

    Portanto, você usa um predicado XPath para filtrar o nameatributo dos elementos data/ actionde interesse de cada coluna (e adicionalmente packageo atributo, se necessário).

    For destinatione categoryuse /text/text()em vez de /value/text()como os valores são apenas map_keys em sys.dm_xe_map_valuesvez de qualquer coisa significativa fora deste contexto.

    Certifique-se também de filtrar o tipo de elemento correto para cada coluna correspondente ao elemento pai relevante (o exemplo acima tem um para cada datae action)

    • 3

relate perguntas