主页 / dba / 问题 / 341283
Accepted
Aleksey Vitsko
Asked: 2024-07-30 05:58:54 +0800 CST

查询扩展事件 XEL 文件时，如何将包含 XML 数据的单列转换为多列

  • 772

我们创建了一个扩展事件会话来捕获严重级别为 16 或更高的错误：

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

现在，我们可以通过运行以下命令来查询它：

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)

列event_data是 nvarchar 类型但包含 XML 值，代表一个表。

在此处输入图片描述

问题：

在 SSMS 中的文本编辑器中查询时，如何将event_data列转换（分解）为多个列（例如用户名、错误号、类别、目标、消息等）？

XML 数据示例：https://pastecode.io/s/tjg7jo40

sql-server

1 个回答

  1. Best Answer

    你应该能够使用类似

    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)

    因此，您可以使用 XPath 谓词来过滤每列感兴趣的元素的name属性（如果需要，还可以过滤附加属性）。dataactionpackage

    对于destination和的category使用，/text/text()而不是/value/text()因为值仅仅是map_keys，sys.dm_xe_map_values而不是该上下文之外的任何有意义的东西。

    还要确保对与相关父元素相对应的每一列的正确元素类型进行过滤（上面的示例中分别有一个data和action）

    • 3

相关问题