Query XML for Dummies

  • 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 4 years, 6 months ago by  homebrew01.
    • This topic was modified 4 years, 6 months ago by  homebrew01.
    • This topic was modified 4 years, 6 months ago by  homebrew01.
  • 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 4 years, 6 months ago by  homebrew01.
    • This reply was modified 4 years, 6 months ago by  homebrew01.
    • This reply was modified 4 years, 6 months ago by  homebrew01.
  • 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">
  • 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 4 years, 6 months ago by  homebrew01.
    • This reply was modified 4 years, 6 months ago by  homebrew01.

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

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