Query Help On RawContent in EnsLib_HL7.Message table

Request for getting Audit/Reporting info.
The ask is for a count of inbound messages by distinct identifier.

My challenge is getting the unique identifer out of the RawContent column in the EnsLib_HL7 table.

Wondering if a temp table can be created to store the value then do a join?

In the below example I am wanting to use the 999998 value in the PID.

Name              RawContent
ADT_A01             MSH||||||||
                 PID|||9904319776^^^ACMEHOSPITAL^MRN~991051441^^^ACMEHIE^MPIID~999998^^^BLUECLUE^BEKEY|
                 |BACON^CHRIS^P^

I am able to get the counts of the events but stumped on doing for the distinct value in PID.
select Name, 
sum(case when Name='ADT_A01' then 1 else 0 end) as Total_ADT_A01,
sum(case when Name='ADT_A03' then 1 else 0 end) as Total_ADT_A03,
sum(case when Name='ADT_A04' then 1 else 0 end) as Total_ADT_A04
from EnsLib_HL7.Message
where Name in ('ADT_A01','ADT_A04','ADT_A03')
group by Name