CODEWITHSUNDEEP
sql-serverxmlsql-server-2014extended-events
carlo.borreo
carlo.borreo

Reputation: 1355

SQL Server: reading XML files for extended events

I have set SQL server to report errors in form of XML files, through extended events. Now, I want to read and display them. I am doing something wrong, and some data does not display as expected. I simplified as much as possible, here is what I do:

    declare @xmlColumn xml = '<event name="error_reported" package="sqlserver" timestamp="2015-08-25T14:22:24.576Z">
      <data name="error_number">
        <value>207</value>
      </data>
      <action name="username" package="sqlserver">
        <value>John</value>
      </action>
    </event>'

    select @xmlColumn.value('(/event/@timestamp)[1]','datetime2'),
           @xmlColumn.value('(/event/data[@name="error_number"]/value)[1]', 'int'),
           @xmlColumn.value('(/event/action[@name="username"]/text)[1]', 'varchar(99)')

The oputput is:

2015-08-25 14:22:24.5760000 207 NULL

I expected:

2015-08-25 14:22:24.5760000 207 John

What am I doing wrong?

Upvotes: 0

Views: 695

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

You must read the node "value" below "action":

select @xmlColumn.value('(/event/@timestamp)[1]','datetime2'),
       @xmlColumn.value('(/event/data[@name="error_number"]/value)[1]', 'int'),
       @xmlColumn.value('(/event/action[@name="username"]/value)[1]', 'varchar(99)')

Upvotes: 2

Related Questions