Storing information from EDI messages into multiple database tables

In the past we have seen examples of how to "shred" XML documents into database tables; DataDirect XQuery provides the best of the two worlds: a highly efficient and scalable XML query language and support for inserting/updating/deleting data available in a RDBMS directly from the XQuery language. If you have read some of the past posts discussing the ability to process EDI messages with DataDirect XQuery, it should be easy to understand that it's not unusual to hit problems which involve storing information enclosed in incoming EDI messages into a database - be it for reporting, logging, archiving, further processing or other reasons. Recently I've been working on some usecase involving NCPDP Script. NCPDP Script is an EDI standard designed to support electronic handling of medication prescriptions; it describes a relatively small set of transactions, like "new prescription", "refill request", "cancel prescription" and so on. In particular, consider this simple "NEWRX" (new prescription) message, which could be something your pharmacy electronically receives from your doctor ordering a new prescription for you:[cc lang="text" theme="xquery"]UNA:+. *' UIB+UNOA:0++1234A5B6-78CD-90E1-2345-678901+++1234567890123:D+1234567:P+20070807:120550 UIH+SCRIPT:008:001:NEWRX+1234567+DDXQ12345678.A1234567890123 PVD+PC+123456789012:SPI*AB1234567:DH+++Apple:Bob++Green Fruit Medical Services+1234 Tree Pkwy.:Bedford:MA:01730+1234567890:WP PVD+P2+1234567:D3+++++Ripe Aid+A St:Wakefield:MA:01880+1234567890:WP*1234567890:FX PTT++20060724+Orange:Bill+M+123456789:SY+::MA:01880 DRU+P:fruittoil 12.5 mg oral tablet+Y6:30:38+:1 tab PO 2 times a day+85:20070807:102+0+R:7 UIT+1234A5B6-78CD-90E1-2345-678901+5 UIZ++1[/cc] If you visually scan the message above, you may notice two PVD segments that describe respectively the "provider" issuing the prescription (your doctor) and the pharmacy that is supposed to fill the prescription. You can probably also recognize a PTT segment which describes the patient, who you are. As you would probably expect, DataDirect Data Integration Suite does support NCPDP Script messages, which implies that you can manipulate the message above as an easy to navigate/query/manipulate XML message. For example, this DataDirect XQuery extracts a summary of the information from the NCPDP NEWRX message into an easy to read/summary XML fragment:[cc lang="xquery"]declare option ddtek:serialize "indent=yes";

let $ncpdpPrescription := doc("converter:EDI:long=yes?c:/prescription1.ncpdp") return

{ $ncpdpPrescription/NCPDP/NEWRX/PVD[./PVD01-ProviderCoded = "PC"]/PVD02-ReferenceNumber[PVD0202-ReferenceQualifier = "SPI"]/PVD0201-ReferenceNumber/text() }

{ $ncpdpPrescription/NCPDP/NEWRX/PVD[./PVD01-ProviderCoded = "PC"]/PVD05-Name/PVD0501-PartyName/text() }

{ $ncpdpPrescription/NCPDP/NEWRX/PVD[./PVD01-ProviderCoded = "PC"]/PVD05-Name/PVD0502-FirstName/text() }

{ $ncpdpPrescription/NCPDP/NEWRX/PVD[./PVD01-ProviderCoded = "PC"]/PVD07-PartyName/text() }

{ $ncpdpPrescription/NCPDP/NEWRX/PVD[PVD01-ProviderCoded = "P2"]/PVD02-ReferenceNumber[PVD0202-ReferenceQualifier = "D3"]/PVD0201-ReferenceNumber/text() }

{ $ncpdpPrescription/NCPDP/NEWRX/PVD/PVD01-ProviderCoded[. = "P2"]/../PVD07-PartyName/text() }

{ $ncpdpPrescription/NCPDP/NEWRX/PVD[PVD01-ProviderCoded = "P2"]/PVD08-Address/PVD0802-CityName/text() }

{ $ncpdpPrescription/NCPDP/NEWRX/PVD[PVD01-ProviderCoded = "P2"]/PVD08-Address/PVD0803-CountrySub-EntityIdentification/text() }

{ $ncpdpPrescription/NCPDP/NEWRX/PTT/PTT05-ReferenceNumber[./PTT0502-ReferenceQualifier = "SY"]/PTT0501-ReferenceNumber/text() }

{ $ncpdpPrescription/NCPDP/NEWRX/PTT/PTT03-Name/PTT0301-PartyName/text() }

{ $ncpdpPrescription/NCPDP/NEWRX/PTT/PTT03-Name/PTT0302-FirstName/text() }

{ $ncpdpPrescription/NCPDP/NEWRX/PTT/PTT04-GenderCoded/text() }

{ $ncpdpPrescription/NCPDP/NEWRX/DRU/DRU01-Drug/DRU0102-ItemDescription/text() }

{ concat($ncpdpPrescription/NCPDP/NEWRX/DRU/DRU02-Quantity/DRU0202-Quantity, " (", $ncpdpPrescription/NCPDP/NEWRX/DRU/DRU02-Quantity/DRU0201-QuantityQualifier, ")") }

{ $ncpdpPrescription/NCPDP/NEWRX/DRU/DRU03-Directions/DRU0302-Dosage/text() }

{ let $CCYYMMDD := $ncpdpPrescription/NCPDP/NEWRX/DRU/DRU04-Date/DRU0402-DateTimePeriod return xs:dateTime(concat(substring($CCYYMMDD, 1, 4), "-", substring($CCYYMMDD, 5, 2), "-", substring($CCYYMMDD, 7, 2), "T", current-time())) }

{ $ncpdpPrescription/NCPDP/NEWRX/DRU/DRU06-Quantity/DRU0602-Quantity/text() }

[/cc] ...and the result is: [cc lang="xquery"]

123456789012 Apple Bob Green Fruit Medical Services

1234567 Ripe Aid

Wakefield MA

123456789 Orange Bill M

fruittoil 12.5 mg oral tablet 30 (Y6) 1 tab PO 2 times a day 2007-08-07T15:21:21.806-04:00 7

[/cc] Now, going back to the original goal of this post: what if I want to store the information about prescription, related provider, pharmacy and patient in a database? As DataDirect Data Integration Suite supports the ability to update/insert data in databases directly from XQuery, the process only requires a executing an XQuery somewhat different from what we used above. Let's assume our database schema looks like this: The XQuery that you can run in DataDirect Data Integration Suite shredding an incoming NEWRX NCPDP Script message into that database schema is fairly simple; notice that the XQuery avoids creating duplicates for already "registered" patients, providers and pharmacies:[cc lang="xquery"]let $ncpdpPrescription := doc("converter:EDI:long=yes?c:/prescription1.ncpdp") let $provider-id := $ncpdpPrescription/NCPDP/NEWRX/PVD[./PVD01-ProviderCoded = "PC"]/PVD05-Name/PVD0501-PartyName/text() let $provider := collection("PROVIDER")/PROVIDER[ID = $provider-id] let $pharmacy-id := $ncpdpPrescription/NCPDP/NEWRX/PVD[PVD01-ProviderCoded = "P2"]/PVD02-ReferenceNumber[PVD0202-ReferenceQualifier = "D3"]/PVD0201-ReferenceNumber/text() let $pharmacy := collection("PHARMACY")/PHARMACY[ID = $pharmacy-id] let $patient-ssn := $ncpdpPrescription/NCPDP/NEWRX/PTT/PTT05-ReferenceNumber[./PTT0502-ReferenceQualifier = "SY"]/PTT0501-ReferenceNumber/text() let $patient-id := if ($patient-ssn) then $patient-ssn else concat($ncpdpPrescription/NCPDP/NEWRX/PTT/PTT03-Name/PTT0302-FirstName,"##",$ncpdpPrescription/NCPDP/NEWRX/PTT/PTT03-Name/PTT0301-PartyName) let $patient := collection("PATIENT")/PATIENT[ID = $patient-id] return ( if (not($provider)) then ddtek:sql-insert("PROVIDER", "ID", $provider-id, "LASTNAME", $ncpdpPrescription/NCPDP/NEWRX/PVD[./PVD01-ProviderCoded = "PC"]/PVD05-Name/PVD0501-PartyName, "FIRSTNAME", $ncpdpPrescription/NCPDP/NEWRX/PVD[./PVD01-ProviderCoded = "PC"]/PVD05-Name/PVD0502-FirstName, "AFFILIATION", $ncpdpPrescription/NCPDP/NEWRX/PVD[./PVD01-ProviderCoded = "PC"]/PVD07-PartyName ) else (), if (not($pharmacy)) then ddtek:sql-insert("PHARMACY", "ID", $pharmacy-id, "NAME", $ncpdpPrescription/NCPDP/NEWRX/PVD/PVD01-ProviderCoded[. = "P2"]/../PVD07-PartyName, "ADDRESS", $ncpdpPrescription/NCPDP/NEWRX/PVD[PVD01-ProviderCoded = "P2"]/PVD08-Address/PVD0801-StreetAndNumberPOBox, "CITY", $ncpdpPrescription/NCPDP/NEWRX/PVD[PVD01-ProviderCoded = "P2"]/PVD08-Address/PVD0802-CityName, "STATE", $ncpdpPrescription/NCPDP/NEWRX/PVD[PVD01-ProviderCoded = "P2"]/PVD08-Address/PVD0803-CountrySub-EntityIdentification ) else (), if (not($patient)) then ddtek:sql-insert("PATIENT", "ID", $patient-id, "FIRSTNAME", $ncpdpPrescription/NCPDP/NEWRX/PTT/PTT03-Name/PTT0302-FirstName, "LASTNAME", $ncpdpPrescription/NCPDP/NEWRX/PTT/PTT03-Name/PTT0301-PartyName, "GENDER", $ncpdpPrescription/NCPDP/NEWRX/PTT/PTT04-GenderCoded ) else ddtek:sql-update($patient, "FIRSTNAME", $ncpdpPrescription/NCPDP/NEWRX/PTT/PTT03-Name/PTT0302-FirstName, "LASTNAME", $ncpdpPrescription/NCPDP/NEWRX/PTT/PTT03-Name/PTT0301-PartyName ), ddtek:sql-insert("PRESCRIPTION", "DATE", adjust-dateTime-to-timezone( let $CCYYMMDD := $ncpdpPrescription/NCPDP/NEWRX/DRU/DRU04-Date/DRU0402-DateTimePeriod return xs:dateTime(concat(substring($CCYYMMDD, 1, 4), "-", substring($CCYYMMDD, 5, 2), "-", substring($CCYYMMDD, 7, 2), "T", current-time())),()), "PROVIDER", $provider-id, "PHARMACY", $pharmacy-id, "PATIENT", $patient-id, "DESCRIPTION", $ncpdpPrescription/NCPDP/NEWRX/DRU/DRU01-Drug/DRU0102-ItemDescription, "QUANTITY", concat($ncpdpPrescription/NCPDP/NEWRX/DRU/DRU02-Quantity/DRU0202-Quantity, " (", $ncpdpPrescription/NCPDP/NEWRX/DRU/DRU02-Quantity/DRU0201-QuantityQualifier, ")"), "DOSAGE", $ncpdpPrescription/NCPDP/NEWRX/DRU/DRU03-Directions/DRU0302-Dosage, "REFILLS", $ncpdpPrescription/NCPDP/NEWRX/DRU/DRU06-Quantity/DRU0602-Quantity ) )[/cc] As you can see, the XQuery is similar (especially in terms of used XPath expressions) to what we used before to extract an "XML summary" of the incoming NEWRX message; but in this case we are relying on ddtek:sql-insert() functions to actually perform SQL INSERT operations into the connected database. What we are doing in this final XQuery looks simple, and it should; but what's going on under the cover is not entirely trivial. You have a streaming conversion (performed by DataDirect XML Converters) which is parsing the incoming NCPDP NEWRX message and sending XML events to the DataDirect XQuery engine; the DataDirect XQuery engine is using those XML events to check values in a RDBMS and issuing SQL INSERT statements when needed (avoiding duplicate insertions) using data that originated from the incoming NCPDP NEWRX message. And we don't need to worry about the NCPDP syntax, or the SQL dialect supported by the RDBMS. Simple, huh?