Integration Engine Solutions to Connect Anything to Anything

Log out?

Select Data from Database and Format to HL7 Message

zip-iconTraining Files

This route ‘F – Select Patient From Database’ receives a file from a Directory/File Listener or from any of these protocols TCP/IP, HTTP or API.  The message or file payload consists of  basic patient information such as Medical Record Number, Episode ID or Account Number, Last Name, Middle Name, First Name, Date of Birth, Gender, and SSN. Each field is separated by a pipe ‘|’.  The MRN and Account Number are taken from the file and SQLXML is used to select the patient from the database.  If the MRN/Account is a match, all the required fields are pulled from each database table, and an XML file is created.  The XML file is passed to the next route, ‘F – Format XML To HL7 Message’, to map the XML file fields to an HL7 message.  If the format is successful, the HL7 message is sent to the downstream system or application. 

Select Patient from the Database Route: 

Selecting HL7 patient from database route example


Map XML to HL7 Message Route:

XML to HL7 Mapping Route Example



 Example inbound file payload: 


 MRN = 1005589 

Account Number = 991234 

Patient Last Name = Jones

Patient Middle Name =

Patient First Name = William 

Patient Birth Date = 19610615 

Gender = 

SSN = 123456789 


The H2 database was used for this route, and the SQLXML Format was created and configured to select the patient information from these tables: PATIENT, PATIENT_VISIT, and INSURANCE. Use the document ‘Preparing the Database to help create and configure the database tables and the H2 database connection. 

 SQLXML:  Select the Patient from the PATIENT Table 

Data Mapping HL7 patient table in PilotFish software


SQLXML: Select the Patient Visit from the PATIENT_VISIT Table  

Data Mapping from delimited file to XML with HL7 message


SQLXML: Select the Patient Insurance from the INSURANCE Table 

Delimited file to XML in PilotFish data mapper with HL7 message

To test this route, we will be using a Directory/File Listener and poll for a file called ‘PayloadFile.txt’ from the ‘C:\PilotFish eiConsole Working Directories\data\In’. The output HL7 message will be sent or transported to a File/Directory ‘C:\PilotFish eiConsole Working Directories\data\Out’ with a file named ‘HL7MessageOut.txt’.  Since there are two Routes to complete this process, they will need to be run or test this process in the local eiPlatform emulator.  In addition, make sure that the H2 database tables are configured and connected to do the SQLXML select in order for it to work.

This is a unique website which will require a more modern browser to work! Please upgrade today!