Partial text searches inside an XML DataColumn

• Ashwin M N

  Hall of Fame

  Points: 3668

  More actions

  June 6, 2011 at 5:14 am

  #240883

  I have created a DDL trigger to insert data definition changes into a table Logs and EVENTDATA() is Event column of datatype XML.

  The application developer has reported that particular procedure has altered and wants to know when and who did it?

  How can I implement like operator on XML datatype.In simple if the Event column if it was of datatype varchar my Select

  statement would be

  select * from Logs where Event like '%@procedure_name%'

  How can this be implemented for a XML datatype.

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  June 6, 2011 at 1:10 pm

  #1334629

  You can use the LIKE operator on the results of an XQuery value() function.

  Modified Example B from http://msdn.microsoft.com/en-us/library/ms178030.aspx

  USE AdventureWorks

  GO

  SELECT CatalogDescription.value('

  declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";

  (/PD:ProductDescription/@ProductModelID)[1]','INT') AS Result

  FROM Production.ProductModel

  WHERE CatalogDescription.value('

  declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";

  (/PD:ProductDescription/@ProductModelID)[1]', 'VARCHAR(100)') LIKE '3%'

  ORDER BY Result DESC

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  June 7, 2011 at 2:36 am

  #1334834

  You can use the XML 'contains' operator

  DECLARE @event XML

  SET @event = '

  <EVENT_INSTANCE>

  <EventType>ALTER_PROCEDURE</EventType>

  <PostTime>2011-06-06T00:00:00.000</PostTime>

  <ServerName>SERVERNAME</ServerName>

  <LoginName>DOMAIN\User</LoginName>

  <UserName>dbo</UserName>

  <DatabaseName>DBName</DatabaseName>

  <SchemaName>dbo</SchemaName>

  <ObjectName>procedure_name</ObjectName>

  <ObjectType>PROCEDURE</ObjectType>

  </EVENT_INSTANCE>

  '

  SELECT @event.value('contains( (/EVENT_INSTANCE/ObjectName)[1] ,"procedure_name" )' ,'bit')

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537