Select Data from Database and Format to HL7 Message

Training 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: 

Map XML to HL7 Message Route:

 Example inbound file payload: 

1005589|991234|JONES|A|WILLIAM|19610615|M|123456789 

 MRN = 1005589 

Account Number = 991234 

Patient Last Name = Jones

Patient Middle Name =A 

Patient First Name = William 

Patient Birth Date = 19610615 

Gender = M 

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 

SQLXML: Select the Patient Visit from the PATIENT_VISIT Table  

SQLXML: Select the Patient Insurance from the INSURANCE Table 

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.