Query XML for Dummies

Question

Post reply

Query XML for Dummies

homebrew01

SSC Guru

Points: 55549

June 21, 2020 at 2:41 am

#3764569

I am trying to learn Extended Events & XML.

I created and E.E. that captured my running SP_WHO2. But how do I query the E.E. file to find it ?

I ran this, just to find my record, but what's the "Correct" way to query my file to search for Stored Procedure calls, or table activity INSERT, UPDATE, DELETE etc... ?

With trace files, I could query the textdata column, but this EE format has me confused.

SELECT cast(event_data as XML) as event_data
FROM sys.fn_xe_file_target_read_file('D:\SQL Server 2016 SP2\TraceFiles\SQL_Activity_EE_0_132371803292440000.xel', null, null, null)
where event_data like '%who2%'


<event name="sql_batch_completed" package="sqlserver" timestamp="2020-06-21T02:32:47.608Z">
  <data name="cpu_time">
    <value>47000</value>
  </data>
  <data name="duration">
    <value>170991</value>
  </data>
  <data name="physical_reads">
    <value>2</value>
  </data>
  <data name="logical_reads">
    <value>400</value>
  </data>
  <data name="writes">
    <value>11</value>
  </data>
  <data name="spills">
    <value>0</value>
  </data>
  <data name="row_count">
    <value>174</value>
  </data>
  <data name="result">
    <value>0</value>
    <text>OK</text>
  </data>
  <data name="batch_text">
    <value>
execute sp_who2</value>
  </data>
  <action name="username" package="sqlserver">
    <value>MyDomain\MyLogin</value>
  </action>
  <action name="sql_text" package="sqlserver">
    <value>
execute sp_who2</value>
  </action>
  <action name="session_nt_username" package="sqlserver">
    <value>MyDomain\MyLogin</value>
  </action>
  <action name="nt_username" package="sqlserver">
    <value>MyDomain\MyLogin</value>
  </action>
  <action name="database_name" package="sqlserver">
    <value>master</value>
  </action>
  <action name="client_hostname" package="sqlserver">
    <value>MyServerName</value>
  </action>
  <action name="client_app_name" package="sqlserver">
    <value>Microsoft SQL Server Management Studio - Query</value>
  </action>
  <action name="collect_system_time" package="package0">
    <value>2020-06-21T02:32:47.619Z</value>
  </action>
  <action name="callstack" package="package0">
    <value>sqldk.dll+0x0000000000114B71
sqllang.dll+0x0000000000733F4C
sqllang.dll+0x0000000000734027
sqllang.dll+0x0000000000733B39
sqllang.dll+0x0000000000734130
sqllang.dll+0x0000000000734224
sqllang.dll+0x0000000000735131
sqllang.dll+0x0000000000734559
sqllang.dll+0x0000000000734F8F
sqllang.dll+0x0000000000734529
sqllang.dll+0x0000000000734321
sqllang.dll+0x000000000070DD3C
sqllang.dll+0x000000000043BD2D
sqllang.dll+0x0000000000027DAF
sqllang.dll+0x0000000000019DEA
sqllang.dll+0x000000000001FF52
sqllang.dll+0x0000000000020063
sqldk.dll+0x0000000000005B4D
sqldk.dll+0x0000000000005935
sqldk.dll+0x000000000000558D
sqldk.dll+0x000000000002BEB8
sqldk.dll+0x000000000002BF50
sqldk.dll+0x000000000002BB07
sqldk.dll+0x000000000002C1D8</value>
  </action>
</event>

This topic was modified 5 years ago by homebrew01.
This topic was modified 5 years ago by homebrew01.
This topic was modified 5 years ago by homebrew01.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55549 More actions · Answer 1

Tried this, but 0 results, although I know there are records.

select top 1 * FROM [EE_Table_20200617]
WHERE Event_data.value('(/event/data/value)[1]',  'nvarchar(max)') LIKE '%EXECUTE%'

Thanks in Advance.

This reply was modified 5 years ago by homebrew01.
This reply was modified 5 years ago by homebrew01.
This reply was modified 5 years ago by homebrew01.

homebrew01 SSC Guru Points: 55549 More actions · Answer 2

Also, how do I get the EE date/time to show as local server time, not UTC ?

I want to query within a date range. The beginning of my XML has UTC time. I want to select a 3 hour range, like 2020-06-17 08:00:00 - 2020-06-17 11:00:00

<event name="sql_batch_completed" package="sqlserver" timestamp="2020-06-21T02:32:47.608Z">

homebrew01 SSC Guru Points: 55549 More actions · Answer 3

In case anyone else can benefit, I was given this as 1 possible solution.

;with src
as
(
select N.value('value[1]', 'varchar(max)') as SQLstatement
  from Extend_Events_Table as T
 cross apply T.Event_data.nodes('/event/data') as I(N)
)
select * 
  From src 
 Where SQLstatement LIKE '%SearchString%'

This reply was modified 5 years ago by homebrew01.
This reply was modified 5 years ago by homebrew01.

Query XML for Dummies

Cookies on SQLServerCentral