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/PVD0801-StreetAndNumberPOBox/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
A StWakefield 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: