SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Finding the Attribute

I was playing with some Extended Events recently. If you haven’t tried, I’d encourage you to do so. However, working with XML is not my favorite. I know I can get the GUI in SSMS 16.x to show me events, but I sometimes want to query.

Here was my quick adventure in XML and XQUERY. I should know this stuff better, but I think I’m working with XML so rarely that I’m constantly re-learning things.

I had a document like this:

DECLARE @x XML = CONVERT (XML, 
'<event name="login" package="sqlserver" timestamp="2016-09-28T01:48:31.743Z">
  <data name="is_cached">
    <value>false</value>
  </data>
  <data name="is_recovered">
    <value>false</value>
  </data>
  <data name="is_dac">
    <value>false</value>
  </data>
  <data name="database_id">
    <value>1</value>
  </data>
  <data name="database_name">
    <value>master</value>
  </data>
  <action name="username" package="sqlserver">
    <value>PLATO\Steve</value>
  </action>
  <action name="session_nt_username" package="sqlserver">
    <value>PLATO\Steve</value>
  </action>
  <action name="session_id" package="sqlserver">
    <value>60</value>
  </action>’

There was more, but this is fine. I had a query someone else sent me that looked like this:

SELECT 
[message] = @x.value(
                     '(event/data[@name="database_name"]/value)[1]',
                     'nvarchar(250)'
                     )

That’s fairly simple, but what I really wanted was to get an attribute at the top. In the “event” node, I wanted the “name” attribute. I can go from the query above to that, right? I could have dug into XQUERY, but I’ve found it logical in the past, so I thought I could actually figure this out.

I know that the path was just event, and I needed to get the attribute from that. I tried this:

SELECT 
[message] = @x.value(
                     '(event[@name="name"]/value)[1]',
                     'nvarchar(250)'
                     )

That didn’t work. So I modified things to

SELECT 
[message] = @x.value(
                     '(event/name/value)[1]',
                     'nvarchar(250)'
                     )

No go.

Hmmmm. What do I need to do? I decided to Google a little and saw a note that the attribute is accessed with the @ symbol. OK, so I need to provide that as the path.

SELECT 
[message] = @x.value(
                     '(event/@name/value)[1]',
                     'nvarchar(250)'
                     )

Still no good, but then I removed the value.

SELECT      @x.value(
                '(event/@name)[1]', 
                'nvarchar(250)'
               )

That was it.

XPATH and XQUERY make sense once you get the rules, but they’re still annoying to work with. I’ll be trying to work with the GUI in SSMS as much as possible with XE.


Filed under: Blog Tagged: syndicated, T-SQL, xml

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...