XML Imports: Insert. Update. Replace?

xml-to-dbAnyone who has worked with XML-based imports in Aspen knows there are two modes: insert and update. Well, technically there are three modes – both is a hybrid of insert and update. The mode determines how the import handles records in the source data file. However, I needed another option for importing medical alerts. Hanover maintains medical data, including alerts, in HealthOffice. Every night, we want to import the medical alerts from HealthOffice and completely replace the existing medical alerts data in Aspen. Insert and/or update modes wouldn’t work because an alert that was deleted in HealthOffice wouldn’t appear in the data file – there was no way to tell Aspen to remove it! To solve this problem we created a new import mode: replace. Keep reading to see how replace mode works and how you can use it in your own imports.

WARNING!

warningBefore we go any further I must explicitly state: replace mode is designed to delete records from your database! Using it improperly could cause unintended data loss. Use with caution, use at your own risk, the developer makes no guarantees about this function, etc.

Overview

Phew, now that that’s been said let’s get dive into how this thing works. Replace mode can be thought of as a two step process:

  1. Delete existing records in Aspen
  2. Insert records from the source data file

These steps are accomplished with a generic Java class and a new tag in the XML definition. The Java class can be uploaded to the source code of any purely XML-based import. The Java class, named ReplaceXmlDefinitionImport, only takes effect if the XML contains a new element (tag) named <replace-criteria>. That element contains a series of children <filter> elements that define which records in the destination table will be deleted. The actual “mode” attribute of the <import> element should be set to “insert”.

An Example

The easiest way to understand replace mode is with an example. Download the bundle for the HealthOffice Alerts import from the wiki and add it to your district. Open the XML definition and you’ll see the following (minor line wrapping edits have been added):

<import table-id="tblStdAlert" mode="insert" force="true">
 <replace-criteria>
  <filter field="alrType" operator="equals" 
          source="constant" value="1">
   <filter field="relAlrStdOid.stdEnrStatus" operator="equals" 
           source="preference" value="sys.std.activeCode" />
  </filter>
 </replace-criteria>

 <direct-fields>
  <direct-field id="relAlrStdOid.stdIDLocal" />
  <direct-field id="alrAlertDesc" />
  <direct-field id="alrSeqNo" />
  <direct-field id="alrType" />
 </direct-fields>
</import>

Lines 2-8 define the replace criteria. The records matching the nested filters will be deleted from the Student Alert table. Notice how this import only deletes medical alerts for active students. It does not delete legal or “other” alerts nor does it delete medical alerts for inactive students. We know it is safe to delete the medical alerts for active students because that is the very data represented by the source file.

Replace Criteria

The <replace-criteria> element can have any number of children <filter> elements. It can even have zero children (i.e., no filtering) which would delete every record in the destination table. This may be necessary for some types of imports but again, please use with caution!

If you don’t have a <replace-criteria> element in your XML definition but still have the ReplaceXmlDefinitionImport as your import’s Java source then no records will be deleted. This is intended to prevent a plain XML definition that is accidentally attached to the wrong Java source from having disastrous results.

Regardless of the filters you add to your replace criteria, it is imperative that you test your import on a non-production database. Accidents happen and mistakes will be made, just make sure they occur on a test system. Nobody wants to go through the data restore process because of a typo.

Other Uses

Besides the medical alerts example, when would you need replace mode? Any data that is managed outside of Aspen but is imported for convenience/accessibility is a candidate for replace mode. Some examples include:

  • IEP records
  • Staff licenses
  • Staff degrees
  • Staff positions

Let me know if you’ve found a need for replace mode in the comments below.

One thought on “XML Imports: Insert. Update. Replace?

Leave a Reply