Report on Usage of Paid Extension Connectors in Mirth Connect

report_on_paid_mirth_connectors.sql

with channel_xml as (
    select
        name,
        xmlparse(document channel) as channel_xml
    from channel c
)
   , destination_connector_xml as (
    SELECT
        name as channel_name,
        unnest(xpath('//destinationConnectors/connector/name/text()', channel_xml))::TEXT as connector_name,
        unnest(xpath('//destinationConnectors/connector', channel_xml)) as connector_xml
    FROM
        channel_xml
)
   , source_connector_xml as (
    SELECT
        name as channel_name,
        'Source' as connector_name,
        unnest(xpath('//sourceConnector', channel_xml)) as connector_xml
    FROM
        channel_xml
)
   , all_connector_xml AS (
    SELECT * FROM source_connector_xml
    UNION ALL
    SELECT * FROM destination_connector_xml
)
, rows as ( select
             channel_name, connector_name,
            unnest(xpath('//properties/@class', connector_xml))::TEXT as connector_type
            FROM destination_connector_xml
)
SELECT * FROM "rows"
WHERE connector_type ilike any (array['%fhir%', '%xds%', '%atsm%', '%atsm%', '%edi%', '%email%', '%serial%', '%xca%', '%xcpd%'])
ORDER BY channel_name ASC, connector_name ASC;