SQL Query XML for Extended Events system table for

Question

I am querying the system tables for Extended Events in SQL to get some data about the extended event itself, and I want to get the database that is set in the Extended Events when its created.

if I run this query from system tables here:

SELECT SESE.predicate_xml
FROM sys.server_event_sessions SEV
LEFT OUTER JOIN sys.server_event_session_fields SESF ON SEV.event_session_id = SESF.event_session_id
LEFT OUTER JOIN sys.server_event_session_events SESE ON SEV.event_session_id = SESE.event_session_id

I can get an XML string for predicate_XML that gives me XML similar to the one:

<and>
  <and>
    <or>
      <leaf>
        <comparator name="equal_uint64" package="package0" />
        <event name="sp_statement_starting" package="sqlserver" field="object_type" />
        <value>8272</value>
      </leaf>
      <leaf>
        <comparator name="equal_uint64" package="package0" />
        <event name="sp_statement_starting" package="sqlserver" field="object_type" />
        <value>20038</value>
      </leaf>
    </or>
    <leaf>
      <comparator name="equal_i_sql_unicode_string" package="sqlserver" />
      <global name="database_name" package="sqlserver" />
      <value>DatabasenameHere</value>
    </leaf>
  </and>
  <leaf>
    <comparator name="equal_boolean" package="package0" />
    <global name="is_system" package="sqlserver" />
    <value>false</value>
  </leaf>
</and>

There is an XML for database_name, and I want to get the value for that.

This is a very simple Extended event so there is a very small XML but this can get large, and can have multiple databases along with the and/or in the XML could be more complex so I can not do a simple query to just find the top/first one. For this specific XML this query works, but it wont work on others/all:

DECLARE @XML AS XML = '<and><and><or><leaf><comparator name="equal_uint64" package="package0"></comparator><event name="sp_statement_starting" package="sqlserver" field="object_type"></event><value>8272</value></leaf><leaf><comparator name="equal_uint64" package="package0"></comparator><event name="sp_statement_starting" package="sqlserver" field="object_type"></event><value>20038</value></leaf></or><leaf><comparator name="equal_i_sql_unicode_string" package="sqlserver"></comparator><global name="database_name" package="sqlserver"></global><value><![CDATA[SubmissionEngine]]></value></leaf></and><leaf><comparator name="equal_boolean" package="package0"></comparator><global name="is_system" package="sqlserver"></global><value>false</value></leaf></and>'
SELECT @XML
SELECT T.c.value('.', 'varchar(100)') AS result  
--,T.c.value('.') AS ObjectType
FROM   @XML.nodes('/and/and/leaf/value') T(c)  
GO  

Please advise on what/how I can query this type of XML to get the database_name from the XML (and if there are multiple, get those as well)

Answer 1

again I'm not sure, if I get this correctly, but you might try a more generic search:

Attention: I've added another node with a database name to demonstrate, that the search is neither depending on the position nor the naming.

DECLARE @xml XML=
N'<and>
  <and>
    <or>
      <leaf>
        <comparator name="equal_uint64" package="package0" />
        <event name="sp_statement_starting" package="sqlserver" field="object_type" />
        <value>8272</value>
      </leaf>
      <leaf>
        <comparator name="equal_uint64" package="package0" />
        <event name="sp_statement_starting" package="sqlserver" field="object_type" />
        <value>20038</value>
      </leaf>
    </or>
    <leaf>
      <comparator name="equal_i_sql_unicode_string" package="sqlserver" />
      <global name="database_name" package="sqlserver" />
      <value>DatabasenameHere</value>
    </leaf>
  </and>
  <leaf>
    <comparator name="equal_boolean" package="package0" />
    <global name="is_system" package="sqlserver" />
    <value>false</value>
  </leaf>
  <test>
    <anyelement name="database_name"/>
    <value>OneMoreDB</value>
  </test>
</and>';

--The query

SELECT vals.value('text()[1]','nvarchar(max)')
FROM @xml.nodes('//*[*[@name="database_name"]]/value') A(vals);

You can read this as:

• Search anywhere (the deep search with //)
• find any element
  • where there is any element on the next level with an attribute name with the value "database_name"
• and take the <value>below the element