Query Mirth Connect event table by event name and pick out channel name and channel ID from the attributes. Postgres 13 and newer

mirth_connect_events_by_channel_name.sql

WITH attribute_rows AS (select e.name,
                               e.date_created,
                               xml_element.*
                        from "event" e,
                             xmltable('//linked-hash-map/entry'
                                      passing (e."attributes"::xml)
                                      columns "key" text PATH 'string[1]',
                                          "value" text PATH 'string[2]'
                                 ) as xml_element
                        where e.name like '%Deploy%'
                           or e.name like '%Redeploy%')
SELECT name as event_name,
       date_created,
       unnest(REGEXP_MATCHES(value, 'Channel\[id=(.*),name.*' )) as channel_id,
       unnest(REGEXP_MATCHES(value, 'Channel\[id=.*,name=(.*)\]' )) as channel_name
FROM attribute_rows
WHERE "key" = 'channel'   ;