SQL Server: reading XML files for extended events

Question

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?

Hi Carlo, if my answer helped you to solve your problem it would be very kind of you to vote up and tick the accept check. I visited your profile and found that you hardly ever accepted answers. This is important to show to other users that this question is resolved. This is how SO is working. All professionals giving answers here are hungry for reputation points. — Shnugo, Dec 31 '15 at 15:27
You solved my problem and deserve a point, sorry for the delay. I reviewed my previous answers to see if any valid solution was not recognized as such. Again, my fault here. I find that saying "hardly ever accepted answers" is a bit too harsh, but still I can improve :-) — carlo.borreo, Jan 06 '16 at 11:40

score 2 · Accepted Answer · answered Dec 28 '15 at 11:50

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)')

SQL Server: reading XML files for extended events

1 Answers1