Syntax to QRY XML Data

• homebrew01

  SSC Guru

  Points: 55548

  More actions

  June 17, 2020 at 9:32 pm

  #3764019

  What is the syntax to Query this XML data to find a statement = "coalesce" or any other text string , within a date range ??

  I created a  table EE_Table with Extended Events data in it. It has 1 column, defined as XML, called Event_Data.

  Something like this?

  select *
    from EE_Table
    where event_data.value('(/event/data/value)', 'nvarchar(max)') like '%coalesce%'
  
  error:
  'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

  Here is a sample row.

  <event name="sp_statement_completed" package="sqlserver" timestamp="2020-06-17T14:11:03.025Z">
    <data name="source_database_id">
      <value>5</value>
    </data>
    <data name="object_id">
      <value>1056279814</value>
    </data>
    <data name="object_type">
      <value>20038</value>
      <text>FUNCTION</text>
    </data>
    <data name="duration">
      <value>1</value>
    </data>
    <data name="cpu_time">
      <value>0</value>
    </data>
    <data name="physical_reads">
      <value>0</value>
    </data>
    <data name="logical_reads">
      <value>0</value>
    </data>
    <data name="writes">
      <value>0</value>
    </data>
    <data name="spills">
      <value>0</value>
    </data>
    <data name="row_count">
      <value>0</value>
    </data>
    <data name="last_row_count">
      <value>0</value>
    </data>
    <data name="nest_level">
      <value>3</value>
    </data>
    <data name="line_number">
      <value>17</value>
    </data>
    <data name="offset">
      <value>970</value>
    </data>
    <data name="offset_end">
      <value>1156</value>
    </data>
    <data name="object_name">
      <value />
    </data>
    <data name="statement">
      <value>if coalesce( NULLIF ( @sEntcode, '' ), N'#' ) = N'#' OR @sEntcode = N'##' OR @sRentity is null</value>
    </data>
  </event>

  • This topic was modified 4 years, 5 months ago by  homebrew01.
  • This topic was modified 4 years, 5 months ago by  homebrew01.

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  June 18, 2020 at 2:58 am

  #3764072

  Don't you have to use Contains for that?

  https://docs.microsoft.com/en-us/sql/xquery/functions-on-string-values-contains?view=sql-server-ver15

  --method of the xml data type. Inside the exit method,  
  --the XQuery contains()function is used to  
  --determine whether the <Summary> text contains the word  
  --Aerodynamic.   
    
  USE AdventureWorks  
  GO  
  WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS pd)  
  SELECT ProductModelID, CatalogDescription.query('  
        <Prod>  
           { /pd:ProductDescription/@ProductModelID }  
           { /pd:ProductDescription/pd:Summary }  
        </Prod>  
   ') as Result  
  FROM Production.ProductModel  
  where CatalogDescription.exist('  
     /pd:ProductDescription/pd:Summary//text()  
      [contains(., "Aerodynamic")]') = 1