Updating a database for each OBX in HL7

The other day I needed to loop over each OBX segment in my message, and call an INSERT statement (or Stored Procedure) in my database. I know there are many ways to do this, but this was so simple that I thought it was worth sharing.

Obviously, in Integration Host (HL7 Soup), the trick is to build up a variable inside a ForEach transformer loop.  With each iteration, append the variable with another INSERT statement.  Add a semicolon at the end of your line to separate this query from the previous.

Note how I am setting the Query variable with its value first.  This builds up the text with each iteration.

Now you just head over to your Database Query activity and insert (right-click option) your variable as the Query Text.

How super simple was that! All done in a couple of minutes.

The only thing to watch out for is that this would be open to a SQL Injection, so only do it for controlled/sanitised data that doesn't allow Mr Johnny Drop Tables to crash your system.

It's all very much like this tutorial if you want help getting going.