Skip to content
PilotFish Logo

Route HL7, EDI to Database & Web Services

HL7 and EDI Integration Demo: SQL Database and JSON Web Service

See how the PilotFish eiConsole IDE configures HL7, X12 EDI and FHIR JSON integrations using a graphical seven-stage assembly line.

Follow three incoming data feeds as they are transformed into XML, mapped to patient data and routed to a SQL database and web service. The demo covers XSLT data mapping, EDI SNIP validation, routing, inline testing and deployment to eiPlatform.

scheduleVideo Key Moments

30-second summary

  • Receive HL7 (MLLP), EDI (S/FTP) and FHIR JSON (HTTP/S) in one route
  • Canonicalize inbound data to XML for consistent transformations
  • Drag & drop mapping with Friendly Names or edit the generated W3C XSLT directly
  • Write to SQL via SQL-XML inserts and deliver JSON to a web service
  • Apply EDI SNIP validation (Types 1–5 built-in, 6–7 via configuration)
  • Route by XPath rules, add transaction monitors and error route triggers
  • Configure database connections using environment properties and test connectivity before deployment.
  • Test integrations directly in eiConsole, including saved sample files, intermediate payloads and results for both target systems.
  • Deploy interface configuration files to eiPlatform using the corresponding project, interface and route directory structure.

Inside the HL7 & EDI → DB & Web Services Pipeline

This demonstration follows three incoming data feeds: HL7 from a hospital, X12 EDI received over FTP and FHIR JSON received through a RESTful web service. PilotFish processes the data and routes patient information to a SQL database and a reporting web service.

Source System Documentation

Identify the source system with a name, icon and optional tags. This documentation helps developers understand the interface configuration and locate routes within a project.

Listener – Input Connectivity

Configure how incoming data is received using one of 44 available Listeners, including HL7, HTTP, FTP/SFTP, database, Kafka and messaging protocols.

The demo shows how to configure connectivity for an HL7 feed, receive EDI files through FTP and accept FHIR JSON patient resources through a RESTful web service.

Choose from 146 available Processors to perform tasks such as decompression, decryption and data preparation before transformation.

EDI SNIP Validation Processor

Use the EDI SNIP Validation Processor to validate incoming X12 EDI transactions. SNIP Types 1–5 are available through built-in configuration options, including syntax, implementation guide requirements, semantic rules and external code sets.

SNIP Types 6 and 7 can be implemented through custom configuration.

Learn more about HIPAA X12 EDI SNIP validation.

Source Transform – Normalize and Map

Select the incoming format, including HL7, X12 EDI, JSON, CSV or Excel, and transform the data into XML for mapping.

The three-pane Data Mapper displays source fields, mapping logic and target fields. Drag and drop elements to map patient information, including first name, last name and date of birth.

The demo also shows how to apply XSLT functions for date formatting and other transformations. Developers can work visually or edit the underlying XSLT directly, with changes reflected in both views.

Use the built-in mapping test to process sample data and inspect the transformation results.

Explore PilotFish HL7 interface engine capabilities.

Routing and Transaction Monitoring

Configure routing rules to determine where processed data is sent. In this demonstration, the All Targets routing option sends the same patient information to both the database and reporting web service.

Other routing options include load balancing and XPath-based conditional routing.

Transaction monitoring options include email alerts, batch failure monitoring, error route triggers and SNMP traps.

Target Transform: SQL and JSON Output

Create two mappings in this demo:

  1. To SQL XML (insert into the patient table, etc.)
  2. To a JSON object to send to a web service

Transport: Outbound Connectivity

Configure how processed data is delivered to each destination.

The demonstration uses a database transport to write patient information to an H2 SQL database and an HTTP POST transport to send JSON to the reporting web service.

The demo also shows how environment properties manage database connection settings, including the database URL, driver, username and password. Separate environment settings can be maintained for different deployment environments.

Built-in connection testing confirms that the database is accessible and that the reporting service responds successfully.

Target System Documentation

Identify each destination with a name and icon. In this demo, the two targets are a patient data store and a quality reporting web service.

Like the Source System stage, the Target System stage provides visual documentation without modifying the data.

Inline Testing and Debugging

Use eiConsole’s inline testing to validate an interface before connecting it to a live source system.

The demonstration loads a sample HL7 file and starts testing at the Source Transform stage. The test processes the data through the routing logic and both target destinations.

The results show:

  • HL7 data transformed into patient XML
  • A database insert generated and written to the SQL database
  • Patient XML transformed into JSON
  • JSON delivered to the reporting web service through HTTP POST

Developers can inspect the output at each stage, save tests for reuse and configure test starting points, ending points and trace points.

A green check mark indicates successful execution, while a red X identifies an unsuccessful stage that requires troubleshooting.

Deployment with eiPlatform

Once an interface has been configured and tested in eiConsole, its configuration files can be deployed to the eiPlatform runtime engine.

The demo shows how eiConsole organizes configuration files into project, interface, route and format directories. These files follow the same directory structure in eiPlatform, allowing updated configurations to be copied to the corresponding runtime directories.

The eiConsole provides the development and configuration environment, while eiPlatform executes the deployed integrations. eiDashboard provides additional interface monitoring and reporting

Why PilotFish?

  • Visual interface configuration: Build and maintain integrations using a graphical seven-stage assembly line.
  • Flexible data mapping: Transform HL7, X12 EDI, FHIR JSON and other formats using visual mapping tools and standards-based XSLT.
  • Reusable configurations: Organize interfaces, routes and mappings for easier maintenance and deployment.
  • Built-in testing: Validate data transformations, routing and target delivery before production deployment.
  • Monitoring and troubleshooting: Use transaction monitoring, alerts and inline testing to identify and resolve integration issues.

[Competitive Advantages & Platform Comparisons]      [View Healthcare Integration Case Studies]

FAQ

What EDI SNIP levels are supported? expand_more

SNIP Types 1–5 are available out of the box. Types 6–7 are supported via custom configuration.

Which Listeners, Transports and Processors are available? expand_more

40+ Listeners, 35+ Transports and 100+ Processors spanning modern protocols, queues, cloud and databases.

How does PilotFish normalize different inbound formats like HL7, EDI, and FHIR JSON? expand_more

Declare the inbound format in Source Transform and the engine canonicalizes payloads to XML. Use the three-pane Data Mapper with Friendly Names for drag & drop mapping or edit the generated W3C XSLT directly.

Can I route messages conditionally and get notified on issues? expand_more

Yes. Use XPath-based routing rules to send transactions to specific targets. Add transaction monitors and error route triggers to alert teams or kick off recovery workflows.

How are PilotFish interfaces deployed and monitored? expand_more

Interfaces are configured and tested in the eiConsole IDE, then deployed to the eiPlatform runtime engine.

The demo shows how configuration files are organized into project, interface and route directories and copied to the corresponding eiPlatform directories. eiDashboard provides additional monitoring and reporting capabilities for deployed interfaces.

Check out our FAQ pages for more
Full Transcript expand_more

Platform Overview and Product Suite

PilotFish, the interoperability platform for healthcare. The PilotFish interoperability platform is a suite of products that enables the integration of Anything to Anything, Anywhere. The product suite consists of the eiPlatform, the runtime server where you deploy your interfaces; the eiConsole, where you configure your interfaces; and the eiDashboard for interface monitoring and reporting. In this demo, we will focus on the eiConsole.

 

Projects, Interfaces and Routes

When you first launch the eiConsole, you are presented with the route file management screen. This is a repository for your project folders, each containing a set of routes. PilotFish integrations are made up of interfaces and routes. An interface is a logical grouping of routes. These project folders hold routes from client work. HIE integration where health information exchange data is acquired and normalized. Lab orders for laboratory orders and results.

Medical equipment for on-premises equipment and smart carts that must be integrated with pharmacy and EMR billing, and data analytics dealing with clinical and administrative data for reporting. There are other routes. We’re going to discuss the 07. When we open a project folder, we might have multiple routes. For this demo, we will focus on the second route. Multiple feeds to database and API for HL7, EDI, and FHIR JSON.

 

The Seven-Stage Assembly Line

When you open the route, you are presented with the main route grid. It consists of seven stages. This is where you configure the interface. Think of it as an assembly line. You work from left to right, moving through each of these seven stages to create an end-to-end route. Each stage has a graphical configuration panel. Everything on the left-hand side has to do with the source system where the data comes from, and everything on the right has to do with the target system where that data is sent.

Before we dive into each stage, here is a quick overview of the assembly line. Every PilotFish route follows the same seven-stage process. The first stage is the source system where you identify the source. The next stage you configure connectivity in the listener stage. Here you can also manipulate the data with built-in processors. The third stage is the source transform where the data gets transformed to XML where a specialized transformation is required.

The mapping stays attached to the stage that uses it. The fourth stage is the route stage where you configure where you want the data to go. You can also set up transaction monitoring in this stage. The fifth

stage, target transform, is where you can do a second transform if needed to whatever format the target system can accept. The sixth stage, transport, is where you configure transmission to the target system.

The last stage, target system, is where you identify the target where you want the data to go.

 

Example: HL7, EDI and FHIR Data Feeds

Let’s look at this interface in detail. We have HL7 coming in from a hospital over MLLP. We are also dealing with EDI from a provider practice picked up by FTP. We have FHIR JSON that we are receiving over a web service. We take these three feeds and transform them into something we can send on. On the first target line, we send it to a SQL database to store it. Then we send a copy of that information to a web service in JSON.

Stage 1: Source System

This first stage is the source system. It is for documentation so that when you open the route, you can see graphically what is happening. Open the source panel and highlight the system name. We can give it a name and select an icon that represents it. Click choose source icon. Scroll through the icons. There are many icons to choose from. You can change the color to further differentiate them. You can add a tag name and a tag value if you need to search for it later.

The source system has no other function.

 

Stage 2: Listener and Processors

The first functional stage is the listener. Here we define the connectivity protocol that receives the source data. Click the ellipsis next to listener type. That opens the list. As I scroll the drop-down, some of the listeners here are a database, email, FTP and SFTP, HTTP, Kafka, Rabbit, MQ, and REST. There are 44 listeners: the ones I mentioned, plus some additional ones. This source uses HL7 over LLP or MLP. Below the choice, you fill in the configuration. This listener uses its port.

This listener has no processor yet. A processor cleans up or manipulates the data before the transform, such as decompression or decryption. I’ll open the processor tab and show how you add one. Click add processor, then click the ellipsis next to processor type. That opens the processor list. Some of the processors here are AL3 compression, AL3 decompression, and API XL. There are 146 processors, the ones I mentioned, plus some additional ones. Under compression, decompression, zip, unzips a file.

I’ll cancel so this listener stays empty.

 

EDI SNIP Validation and FHIR Input

For the second source, I am working with EDI. The listener is FTP. The required fields are filled in. The host is localhost. The port is 21. A processor is applied here. This is the EDI snip validation processor. Types 1 through 5 are available as checkboxes. I’ll click through them as I mention them. Types 1 through three are built-in rules from the X12 implementation guides. Type four is semantic rules. Type five is external codes.

Types six and seven are a custom configuration defined with the PilotFish services team. The EDI source transform is on this same row. The third source is FHIR JSON received over a RESTful web service. The service name is FHIR and it accepts a post of a patient resource.

 

Stage 3: Source Transform

Moving to the source transform. The transformation module defines the inbound format and PilotFish turns that format into XML so the mapping can work with it. Click the ellipsis next to the transformation module. That opens the list. Some of the formats here are CSV, EDI, HL7, JSON, and Excel. There are 10 transformation modules, the ones I mentioned, plus some additional ones. This step stays on HL7. Here is how you select the version.

The version is 2.3, and the parser is lenient if the version is left open. On the right-hand side is where I’m actually going to do my data transformation in our data mapper. I’m going to walk through this now. Now go down here and click edit.

 

Configuring the Data Mapper

Once we open our data mapper, you can see it has three panes. On the left-hand side, this is what I’m mapping from. In this case, I am mapping from HL7 since I’m expecting that inbound. In the middle pane, this is our actual mapping tree. This is all achieved through drag and drop from the left source and right target onto the center. On the right-hand side, this is what I’m mapping to.

In this case, I’m just going to pull out some basic patient demographic information that I want to keep in store for later out of each message. Friendly names is a PilotFish add-on, a value-added feature that takes the codes and shows you the name. I’ll turn it on and leave it on. This icon shows the full X path with predicates. I’ll leave it off so the center shows the full name. I’ll open the patient identification segment, the patient name and the family name.

Family name maps to last name and given name maps to first name. At the top of the mapping panel is a palette of XSLT structure and functions. You can drag and drop them just like when you drag and drop from the left and the right. I’ll open XPath functions. I’m going to show you a couple of functions. String and date and time. I’ll open string and walk through a few: concat, contains, substring and starts with. I’ll open the date and time functions, current date, current date time, and current time.

 

Date Formatting, XSLT and Mapping Tests

Now, we’re going to show you how the drag and drop works. I’ll delete the date of birth. I’ll open the date of birth on the left. I drag the date of birth from the right and from the left onto the center. Then I drop the date and time formatter on it. The input pattern is the compact date and the output pattern is the date with dashes. I’m going to click the XSLT tab. XSLT is a W3C compliant standard language for transforming XML. You can change it in either place.

When you change it in XSLT, it shows up in the mapper. And when you change it in the mapper, it shows up in XSLT. If you know XSLT, you can change it directly there instead of using the mapper. And back to the mapper. Okay. Now click the testing tab. Now you click the green button at the top that feeds a sample file and shows the output below so you can make sure the fields are in the correct places. Now we’ll exit the data mapper and return to the main route grid.

 

Stage 4: Routing and Transaction Monitoring

The route stage is where you configure where the data goes. You can also set up transaction monitoring in this stage. The routing rules tab sets the routing module. The other tabs on this stage are general, debug, trace, and transaction monitoring. The routing module selected is all targets. It copies the input to all configured targets. The data store and the quality reporting service targets both receive the same patient.

All targets, load balancer, routing module, and XPath/attribute are the three available modules shown. The transaction monitoring tab allows you to trap an error so the route stops. Add transaction monitor opens the monitor options. Click the ellipsis next to transaction monitor type. That opens the list of options. The monitors here are batch failure, email alert, error route trigger and SNMP trap.

 

Stage 5: Target Transform

The first target transform is XML to database on the data store row. The transformation module is no transformation. Click the ellipsis next to transformation module. That opens the list. Some of the formats here are CSV, EDI, HL7, JSON, and Excel. There are 10 transformation modules, the ones I mentioned, plus some additional ones. You pick the module that converts the data into the format the downstream system needs. This step stays on no transformation. The XSL file is XML to database mapping.

Clicking the edit button opens the mapping. This is similar to the source transform mapping process.

 

Stage 6: Transport and Environment Properties

The transport on this row is populate database. It writes the patient row into the H2 database. Click the ellipsis next to transport type. That opens the list. Some of the transports here are a database, email, FTP, HTTP, Kafka, and REST. There are 36 transports, the ones I mentioned, plus some additional ones. You pick the transport that sends the data the way the downstream system receives it. This step stays on database SQL. These connection fields use environment variables.

In the file menu, click edit and select environment properties from the drop-down. This is what changes between environments. You have a different environment settings file for each environment. Database user, password, driver and URL. Test connection checks that this database can be reached. The connection was established.

 

Stage 7: Target System and Reporting Service

The target system is data store. Like the source system, it is documentation. It names the receiver and shows an icon. It does not change the data. Open the icon list. The icon documents this target. I’ll cancel. So this target keeps its current icon. The transport is RESTful service 1. It stays on HTTP post and sends the data with a post. The target URL is the reporting service. Test connection checks that service. It comes back with a 200 response code. So the test is successful.

The target system is the quality reporting service. It is documentation too. It names the service that receives the data. Listener is how data gets in. Transport is how data gets out.

 

Inline Testing

Next, we will move on to the inline testing component. In the file menu, click mode to open the testing component. In this mode, you can run tests, save them for future use, and view results, including success and failure indications. The inline testing allows you to test your data integration routes, and identify issues. The test mode looks just like the editing mode, except anywhere that you see an arrow is where you could potentially run your test.

When you click on a stage, the testing configuration panel opens up. You can check the boxes to start at any stage, skip a stage, add a trace point, or end your test. In this test, we are going to start after the listener. If we were going to test the listener, another system would have to actually send the data over FTP or HL7 LLP or the RESTful listener.

For this demo, we start the process at the source transform that allows testing without having to hook up the source system and check the box for start test here and click the execute test button. The source is from file. The HL7 file for this route is test.hl7. Let’s execute the test. The sample HL7 runs from the source transform through the route to both targets. HL7 to XML. This stage output is the patient XML. The route sent that patient to both targets. XML to database.

This stage output is the database insert. Populate database wrote the row. XML to JSON. This stage output is the JSON for the reporting service. The RESTful service posted that JSON. This stage output is the result of the call. A green check mark means a stage successfully completed. A red X means it was unsuccessful.

 

Deployment to eiPlatform

Once you have completed your test and fixed any errors, you are ready to deploy. We’re now going to discuss the deployment process to the eiPlatform. The eiConsole is where you develop it. The eiPlatform is where you deploy it. Each project on this screen is a set of configuration files. Behind the scenes, eiConsole writes them into the working directory. That directory is the EIP root. To deploy, you copy the configuration files that changed. This is the EIP route.

The working directory has the same structure in the eiConsole as it does in the platform. The interfaces are in this folder. The routes are in this folder and the formats are in this folder. To deploy, you copy the configuration files that changed from the eiConsole to the platform. The EIP route has the same structure in both. And that’s it. We’re done.

If you’re curious about the software features, free trial, or even a demo – we’re ready to answer any and all questions. Please call us at 813 864 8662 or click the button.

Contact Us

X12, chartered by the American National Standards Institute for more than 35 years, develops and maintains EDI standards and XML schemas.