If you're using a custom business process to handle the message, you could perform the truncate in an OnRequest() callback.

If you're using a BPL, you would add an action near the beginning of the process to perform the truncate. The request object will be a single batch object containing a collection of records; the next step would be to iterate over the collection and perform the inserts.

You could do essentially the same thing in a DTL; use an SQL rule at the beginning to truncate, then add a foreach to iterate over the batch and populate the DB.

The key is the fact that your BP is getting all of the records in a single object. So when that object arrives, it's time to truncate the table and repopulate it. You will have to skip the first record if it contains headers, of course.

With IRIS 2025.1.1 on Windows, I cannot reproduce your issue. Both sets of instructions execute normally. 

VS Code Version: 1.105.1 (user setup)
Commit: 7d842fb85a0275a4a8e4d7e040d2625abbf7f084
Date: 2025-10-14T22:33:36.618Z
Electron: 37.6.0
ElectronBuildId: 12502201
Chromium: 138.0.7204.251
Node.js: 22.19.0
V8: 13.8.258.32-electron.0
OS: Windows_NT x64 10.0.19045

ISC ObjectScript Extension 3.2.1-beta.1

ISC Server Manager 3.10.6-beta.3

ISC Language Server 2.8.0

A foreach (possibly multiple foreaches) is what you need. Getting the iterators right is the issue. You're iterating over the AISgrp in the first RGSgrp of the source message and placing them in target RGSGroups with the same iteration count of the source AISgrp. Here's an example based on the 2.3.1:SIU_S12 schema structure that should do what you want, and also handles repetitions of the AIL, AIP and AIG groups:

ORCgrp is a member of PIDgrpgrp and PIDgrpgrp is missing from your "if" statement. The condition should read:

..Contains(..ToUpper(source.{PIDgrpgrp(k1).ORCgrp(k2).OBXgrp(k3).OBX:ObservationValue()}),"DETECTED")

I don't know whether you're using the same doctype for the target, but if you are, the target path will need to be updated as well.

Here's an example of the generated query, updated with calls to HICG.GetMsg():
 

SELECT TOP 100 head.ID As ID, 
    {fn RIGHT(%EXTERNAL(head.TimeCreated),999 )} As TimeCreated,
    head.SessionId As Session, 
    head.Status As Status,
    CASE head.IsError WHEN 1 THEN 'Error' ELSE 'OK' END As Error,
    head.SourceConfigName As Source,
    head.TargetConfigName As Target,
    head.SourceConfigName, 
    head.TargetConfigName, 
    head.MessageBodyClassName As BodyClassname, 
    head.MessageBodyId As BodyId, 
    EnsLib_HL7.Message.%ID As Body_ID, 
    HICG.GetMsg(head.MessageBodyId,'PID:3') As Body_PID_PatientID,
    HICG.GetMsg(head.MessageBodyId,'PID:18') As Body_PID_PatientAccountNumber 
    FROM %IGNOREINDEX Ens.MessageHeader.SessionId Ens.MessageHeader head, 
        EnsLib_HL7.Message 
    WHERE (((head.SourceConfigName = 'From_Some_Router'
        AND head.TargetConfigName = 'To_Some_Operation')) 
        AND head.MessageBodyClassName='EnsLib.HL7.Message'
        AND head.MessageBodyId=EnsLib_HL7.Message.%ID) 
    ORDER BY head.ID Desc

I just replaced the null values with calls to HICG.GetMsg(). The 2nd argument is the schema-specific path to the values you're extracting, using the same syntax as found in EnsLib.HL7.Message's GetValueAt() method. You can download the class from the HL7 Spy website: https://hl7spy.ca/downloads/HICG_HL7.xml.

While @David.M's response will provide a query, it will likely not include the HL7 message-derived values. For example, a search that includes the HL7 field values you've listed will return null for their values when executed via the SQL menu or SQL shell.

That said, I created a tool (originally for use with HL7 Spy to extract HL7 messages from Ensemble/Health Connect) that has some features that may help get you what you want. See this thread for an example of its use.