I inherited some SQLs and I dont really know how to morph them. This query fetches all the columns of the database where the data is stored in one cell as an XML. So first we serialized it and then search for the string.

What I need now, is to change this to a COUNT...I just want to know across this timespan how many time this occurs.

What god can help me?

SELECT ORIGINATING_APPLICATION_NAME,ORIGINATING_MESSAGE_ID,SERVICE_NAME,TRANSACTION_ID,USERNAME,PLAN_CODE,ORIGINATION_TIMESTAMP,

EVENT_PERSISTENT_TIMESTAMP,ENTRY_POINT_ADDRESS,SERVICE_VERSION,SERVICE_RETURN_CODE,ORIGINATING_USER_ID,LOG_EVENT_ID,SUPPLEMENTAL_EVENT_DATA,

COMPONENT_IP_ADDRESS,COMPONENT_NAME,SERVICE_OPERATION,SERVICE_TYPE from  (

SELECT  APP.*,

XMLSERIALIZE(SUPPLEMENTAL_EVENT_DATA AS VARCHAR(30000)) AS XMLCHAR

FROM TBL.ANALYTIC_EVENT_APP APP

where (EVENT_PERSISTENT_TIMESTAMP between '2022-01-01-00.00.00.000000' and '2022-01-31-23.59.59.000000')

    and plan_code in ('XYZ')

) WHERE XMLCHAR LIKE '%<SearchOptions>First Name, Last Name,%'