HL7 to sql database

[Mike Strout]

I am looking for the best way to get a handful of messages into a sql database for more robust querying. A typical case looks like this…

Application owner needs a list of all facilities that have shown up in PID.3.3 in all the ORUs from the last month.

After a little research, it seems that if I can get the messages into XML most RDBMs have tools to suck in an XML file and turn it into a series of tables. Then the only challenge is to get a SMAT resend file into XML format. Of course this is complicated by the wide variety of HL7 variants we have in our environment.

I am wondering if anyone has ever created a script that parses the Cloverleaf HL7 definition files in the Formats directory of a site and automatically creates XSDs based on them? It doesn’t seem like it would be that hard to do. It would be a short step then to automate the updating and compiling of the XML packages on the server.

With accurate xsds, it would be easy to generate great XML files and I am am a few simple steps away from the power of SQL queries.

All this said, is there an easier way to answer my application user’s question?

Mike

Topic

[Jim Kosloskey]

Mike,

Most RDBMS can also import delimited files.

Using this particular request as an example, you could define a VRL with the pertinent information (give them the column layout). In my opinion for this example XML is way too much work.

Then create a Xlate that takes the HL/7 and creates the VRL.

Then extract the SMAT data for the last month using either a Tcl proc or the SMAT tool.

Then run the Xlate testing tool pointing to the extracted file and outputing to a file.

Then make the file available to them.

If you needed this to occur periodically, then you could automate all of the above within Cloverleaf processes and threads (would not use the Xlate testing tool though – would use routing). In this automation you could make use of ODBC (assuming you are on Windows or have the Cloverleaf Data Integrator Add-in) to connect directly to a DB and either invoke a Stored Procedure or do the Sql work yourself – using the VRL as the message.

There are Schemas (xsd) files out there for the HL/7 Version 2.x message set  but I have never used them so I cannot attest to their accuracy.

Setting up such schemas from scratch is probably a good bit of work.

But even with a starter set there would be rework needed as nobody really follows the HL/7 standard closely anyway.

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[Author]

Replies