Standardized XML Import File

As of June 2018 version 2 of the file format has been implemented. You should read the Database Updates section for more details but the changes that have been made to V2 of the file are:

Most of the OpenLDR installations are running V1 so the FacilityCode columns have not been updated to the more precise naming conventions. You will need to work with the local database managers to confirm whether they have updated their instance to the new naming conventions.

New: CountryCode nvarchar(2) - Convenience column

New: LIMSVendorCode nvarchar(4) - Used to join to Laboratory table and to clarify code provided by a specific vendor.

ReceivingFacilityCode changes to LIMSReceivingLabCode and size updated to varchar(15)

TestingFacilityCode changes to LIMSTestingLabCode and size updated to varchar(15)

LIMSFacilityCode changes to LIMSRequestingFacilityCode - Contains facility code used internal to the LIMS who logged the specimen. Updated to varchar(15)

RequestingFacilityCode - Remains the same and is the official MOH code for facility requesting the test.

For the fields that were renamed the same data goes into the columns.

This section describes the format and data concepts for creating an XML file which can be imported by an existing process for importing data into the OpenLDR.

Supplying Data Elements Not Directly Mapped to Columns in the LDR

It is highly recommended you read the other pages regarding Data Flow and Data Structure Overview and Data Structure Flexibility and Data Extraction. The LDR is a compromise between a horizontal and a vertical data model for flexibility reasons, rather than to have it purely one way or the other. At first glance the data model of the LDR is pretty simplistic. However, what you will notice is that it does not have a lot of columns beyond basic request and result information. A lot of individual data elements are collected at registration that do not show up as explicit columns in the data model. A "wide" table with each individual data element as a column is a simple way to store and look at data. However, in that model if you need to collect new data elements you must update the data model with new columns and change the processes that transfer the data. The current LDR model handles these new data elements in a flexible way by adding "rows" with related data to each request. For example, for viral load the registration screens ask for whether the patient is Pregnant and if they are BreastFeeding. Rather than add new columns to the data model; additional rows were added to the LabResult table with each row having a column that identifies what data is stored in that row.

The data provider will need to coordinate with the LDR administrator for the PanelCodes, ObservationCodes, FacilityCodes and any of the extra registration data fields that will be sent.

File Format

We are suggesting the data be provided in an XML file format. This is a simplified example of the format and data elements. The examples on this page provide a convenient template for the XML file. On another page is being developed that describes the Mirth Mirth Channel and code which does the actual import and validation of the data supplied in this format.

<Data>

<filetype>FullRequest</filetype>

<provider>SampleXMLProvider</provider>

<RequestID>MZXXXXTBL0000001</RequestID>

<Panel>

<LIMSPanelCode>VIRAL</LIMSPanelCode>

<LIMSPanelDesc>Registry Information</LIMSPanelDesc>

<RequestingFacilityCode>ALTMC</RequestingFacilityCode>

<LIMSObservationDesc>Pregnant</LIMSObservationDesc>

</LabResult>

<LIMSObservationCode>AMAME</LIMSObservationCode>

<LIMSObservationDesc>Breast Feeding</LIMSObservationDesc>

</LabResult>

</Panel>

<Panel>

<LIMSPanelCode>HIVVL</LIMSPanelCode>

<LIMSPanelDesc>Viral Load (CAP/CTM)</LIMSPanelDesc>

<LIMSObservationCode>HIVVR</LIMSObservationCode>

<LIMSObservationDesc>Viral Load (CAP/CTM)</LIMSObservationDesc>

</LabResult>

<LIMSObservationCode>HIVLV</LIMSObservationCode>

<LIMSObservationDesc>Log Value</LIMSObservationDesc>

</LabResult>

</Monitoring>

</Panel>

</Request>

... Multiple Requests optional

</Request>

</Data>

This file format is meant to be a common target for data supplier to create. Most data providers should be able to provide a file with this format. The format models the data structure of the LDR and is also structured to help make the import process simpler. Part of the structure is to help the data providers who do not have control over the data model from which they are pulling data. Attached to this page is a template which has all of the data elements.

There are currently three elements at the Data level that will be used by the import routine to make sure it is trying to import the correct data and structure; filetype, fileversion, provider. Eachh data provider should work with the LDR administrator to come up with a value for 'provider'.

All information related to an individual RequestID is contained within the Request element. A Request has a RequestID and can have multiple Panel's. Each Panel in the file is the same as a record in the Requests table in LDRData. Each Panel can have multiple LabResult elements for each Panel.

The RequestID is available to the import routine right up font in the Request record because the import routine will first remove all records in the Requests table and LabResults table for that RequestID. The reason for this is so the data provider does not have to track which specific data for that request has changed or removed since the last update. Most LIMS or data systems will have a data element indicating when data has changed for a particular sample or request within their data system. Therefore they can easily extract "all" data related to that sample/request and send it to the LDR. The tracking of which data may have been deleted and when it was deleted is tricky and not managed in all data systems. Using this methodology takes that responsibility away from the data provider and also simplifies the import process.

The Request's OBRSetID in the data structure relates directly to the LIMSPanelCode for a Request. To make it easier for the data provider, this is not a required data element; the import routine will automatically populate it if it is missing. This panel's OBXSetID is directly related to the LIMSPanelCode /LIMSObservationCode combination and is also not required and if not supplied will be populated by the import routine.

The import routine can handle multiple Request elements in a single file. Our suggestion is that the fewer Request elements per file the better; we are recommending a single Request per file. The Mirth engine being used for the import process can easily handle the files on the import end and we feel this will help the administrator more easily troubleshoot and manage issues.

Data Elements

<filetype> - Currently, the only valid file type is FullRequest. This identifies the file as having all information about a Request; e.g. all information about each panel and results for each panel.

<fileversion> - This is to denote between changes in the file format. Currently, only version 1.0 is supported.

<provider> - A unique name of who or what is supplying the data. Ideally, this would not be too generic. It will need to identify the unique process sending the data; this could include an organization and location sending the data. This will help with troubleshooting so we know specifically who and what generated and supplied the file.

<Panel> - Each Request can have multiple Panel elements. Each Request must have at least one Panel element in order for any data to be inserted. The Panel sub-elements relate directly to rows in the Requests tables and those elements are needed in order to create a proper record.

<LabResult> - Multiple LabResult elements can exist within each Panel element. However, it is not required that a Result element exist. If no Result elements exist no LabResults records will be created which would indicate that a request for tests has been issued but no results are available.

<Monitoring> - Multiple Monitoring elements can exist within each Panel element. However, it is not required that a Monitoring element exist. They can coexist at the same level as LabResult; ideally the LabResult and Monitoring records would be related but it's not necessary. See more on the Monitoring table discussion page.

Facility Codes and Laboratory Codes

The data provider will need to coordinate with the LDR administrator on what codes they provide in the file so they can be coordinated with other providers. Please also refer to the Date Model pages for each table and the suggestions in that file. It's likely that most locations within the country already have a five character location code so these will need to be confirmed before data is sent.

Most of the OpenLDR installations are running V1 so the FacilityCode columns have not been updated to the more precise naming conventions.

- LIMSPanelCode
- LIMSObservationCode
- RequestingFacilityCode
- LIMSRequestingFacilityCode (V1 = LIMSFacilityCode)
- LIMSReceivingLabCode (V1= ReceivingFacilityCode)
- LIMSTestingLabCode (V1= TestingFacilityCode)
- LIMSPointOfCareDesc

What Data to Send

New data and any data changed since the last transmission.

If a RequestID has been removed from the LIMS and you want to remove it from the LDR do not put in a Panel element; the system will Delete all data related to the RequestID and then just not insert any new data.

Do not send every record on every transfer! This becomes unsustainable. The provider should keep track of the date and time they filter for and only send new and records changed after that date and time.

It's up to the provider as to whether they want to wait to send the data on a request until it has been completed or if they want to send it as it becomes available; e.g. send only request information when the sample is registered and a panel assigned before the results have been finalized. However, it is suggested that you do not wait for all results on a sample have been finalized because sometimes that can take months to complete when most of the results are completed within a few days. However, ultimately it is up to the provider but they need to communicate with the team writing the import routines and designing the reports.

We suggest you resend all information for a Request if any of the information regarding that Request changes (including any new results). This allows the import process to be relatively simple and only have two import steps:

- Remove all information from the LDR related to all Requests/LabResults being imported
- Insert all Requests/Results rows from the import files.

Example with Viral Panel Containing Registration Information

<Data>

<filetype>FullRequest</filetype>

<RequestID>MZXXXXCEE00000025858</RequestID>

<Panel>

<LIMSPanelCode>HIVVL</LIMSPanelCode>

<LIMSPanelDesc>Carga Viral de HIV</LIMSPanelDesc>

<LIMSPointOfCareDesc>Sofala: HR Buzi</LIMSPointOfCareDesc>

<EncryptedPatientID>HTpzXIgo7z8FJZELEE2kq0/izvrcCYXQxzZvMlVi9/I=</EncryptedPatientID>

<LIMSObservationCode>HIVVR</LIMSObservationCode>

<LIMSObservationDesc>Viral Load (CAP/CTM)</LIMSObservationDesc>

</LabResult>

<LIMSObservationCode>HIVRL</LIMSObservationCode>

<LIMSObservationDesc>Log Value</LIMSObservationDesc>

</LabResult>

</Panel>

<Panel>

<LIMSPanelCode>VIRAL</LIMSPanelCode>

<LIMSPanelDesc>Informações de Registro</LIMSPanelDesc>

<LIMSPointOfCareDesc>Sofala: HR Buzi</LIMSPointOfCareDesc>

<EncryptedPatientID>HTpzXIgo7z8FJZELEE2kq0/izvrcCYXQxzZvMlVi9/I=</EncryptedPatientID>

<LIMSObservationCode>AMAME</LIMSObservationCode>

<LIMSObservationDesc>Actualmente est a amamentar</LIMSObservationDesc>

</LabResult>

<LIMSObservationDesc>Actual encontra-se gr vida</LIMSObservationDesc>

</LabResult>

</Panel>

</Request>

</Data>