EDI to Normalized SQL Database RRS feed

  • Question

  • Hi all!


    I'm brand new to Biztalk and I'm looking for some best practices and how to's for the following scenario:


    I need to extract insurance claim data from HIPAA EDI files (specifically 837D's) and insert them into a normalized SQL 2005 database.  A claim will include such things as claim header data, claim detail data, vendor data, provider data, subcriber data, etc.  In my normalized database I have a claimheader table, claimdetail table, and vendor, provider, and subcriber tables,etc with foreign key relationships established. 


    I'm not sure of the best approach to get the data from the file to the database, preserving the foreign key relationships.  Do I need a seperate map and .xsd for each table?  What about (what is) an updategram?  If a record, say for a vendor, already exists in the "Vendor" how can I reference that Vendor's ID in my claimheader table and not try to insert the same vendor into the Vendor table over and over again for every claim that has the same vendor?


    Any help will be greatly appreciated.  Also, if you need more info, just let me know.


    Thanks for any time you can give me!!

    Thursday, March 6, 2008 2:19 PM

All replies

  • Hi,


    It may be a bit tricky to do this in an orchestration using the SQL adapter, you will have to call quite a few stored procedures, and also loop around the various nodes to get the data inserted.


    It ma be more efficient to map the HIPAA EDI file to a more managable XML structure, then pass the XML to a SQL stored procedure. SQL 2005 has some nice functionlity for working with XML, so you shoud be able to peform the table inserts in one stored procedure. This would alow you to do this in one transaction and would also be much better performance wise.


    You may find mapping the HIPAA document to XML challenging using the BizTalk mapper, i often find it easyer to use cusom XSLT called from a map to perform the translation.








    Friday, March 7, 2008 7:44 AM