Блог

Я настраиваю оповещение с использованием расширенного события, в котором я извлекаю информацию в формате XML, поэтому я застрял в поиске значений — имени объекта из этого XML.

 SELECT CAST(data AS XML) AS [result] 
FROM #temp  
WHERE data LIKE '%<text>Abort</text>%'
  

Используя этот запрос, я извлек те записи, которые получают тайм-аут в формате XML, и через этот xml нам нужно получить значение XYZ в качестве имени объекта, используя T-SQL <value>XYZ</value></data>

Вывод приведенного выше запроса select:

 <event name="rpc_completed" package="sqlserver" timestamp="2019-02-20T14:42:39.678Z"><data name="cpu_time"><value>15000</value></data><data name="duration"><value>29999325</value></data><data name="physical_reads"><value>0</value></data><data name="logical_reads"><value>363</value></data><data name="writes"><value>0</value></data><data name="result"><value>2</value><text>Abort</text></data><data name="row_count"><value>9</value></data><data name="connection_reset_option"><value>0</value><text>None</text></data><data name="object_name"><value>XYZ</value></data><data name="statement"><value>exec XYZ </value></data><data name="data_stream"><value /></data><data name="output_parameters"><value /></data><action name="transaction_id" package="sqlserver"><value>0</value></action><action name="session_id" package="sqlserver"><value>1381</value></action><action name="server_principal_name" package="sqlserver"><value>sq</value></action><action name="database_name" package="sqlserver"><value>PR</value></action><action name="database_id" package="sqlserver"><value>5</value></action><action name="client_pid" package="sqlserver"><value>32048</value></action><action name="client_hostname" package="sqlserver"><value>RuntimeHost</value></action><action name="client_app_name" package="sqlserver"><value>test</value></action><action name="event_sequence" package="package0"><value>133050</value></action></event>

NA
  

Вывод должен быть таким:

 Object Name
XYZ
  

Вы можете использовать nodes для фильтрации элементов внутри вашего xml по значению атрибута, а затем value для извлечения необходимых вам данных:

 ;with x as(
    SELECT CAST(data AS XML) AS [result] 
    FROM #temp  
    WHERE data LIKE '%<text>Abort</text>%'
)
select 
    t.s.value('.', 'nvarchar(max)') as object_name
from
    x
cross apply 
    [result].nodes('//data[@name = "object_name"]/value') t(s)
  

Результат:

Редактировать

Одним из подходов к получению database_name является добавление другой nodes фильтрации по action тегам. Чтобы получить timestamp вы можете просто добавить value в select предложение, указывающее правильное выражение xpath:

 ;with x as(
    SELECT CAST(data AS XML) AS [result] 
    FROM #temp  
    WHERE data LIKE '%<text>Abort</text>%'
)
select 
      t.s.value('.', 'nvarchar(max)') as [object_name]
    , u.s.value('.', 'nvarchar(max)') as [database_name]
    , [result].value('(/event/@timestamp)[1]', 'nvarchar(max)') as [timestamp]
from
    x
        cross apply 
    [result].nodes('//data[@name = "object_name"]/value') t(s)
        cross apply  
    [result].nodes('//action[@name = "database_name"]/value') u(s)
  

Результаты с database_name и timestamp :