Slate to Sunapsis Data Integration Script
Posted: Mon Dec 16, 2019 4:49 pm
At the University of Arkansas I was required to create the script that downloads the incoming Slate XML data and translates it into an XML file according to Sunapsis schema. Additional unexpected incoming fields from Slate created errors in the script for it parses out every value for every row, and every element in that row, and then rewrites a new XML file that ISSLink can process without error. The script does handle missing values, but it needs all the fields expected so if one is not found it replaces it with an empty element that is handled better in the ColdFusion language when the script then writes out the new XML file in the new Sunapsis schema.
I wrote the script in ColdFusion, but regretting it. I just thought it would be one less resource on my server by utilizing existing processes. ColdFusion handles empty elements in XML once it is in an internal document in memory very strangely. I had to spend most of my time coding handling missing elements.
Tyler Clayton, who helped me with formulating this solution (he also has something similar in place at Colorado State) stated the following regarding a C# implementation:
"I wrote that in C#, and I did have a really hard time with missing elements. I actually had to set up an empty element at the top of my script, so that there is always an element to use, even if the Slate XML doesn’t contain it. This is how that looks in C#:
XElement emptyElement = new XElement("Empty");
emptyElement.Value = "";
Then when I want to populate the new XML, I fall back to that any time the element I want doesn’t exist:
// Last Name
dataFeedRecord.Element("biographical").Add(new XElement("prsn_prm_last_nm", (admitElmt.Element("Last") ?? emptyElement).Value));
The “??” operator means use the 1st value (the element from Slate) if it exists, and the 2nd value (the empty element) if it doesn’t." - Tyler Clayton
The following is an outline of how the ColdFusion script functions I wrote (if anyone wants the source: email or message me), although this would be different if written in another language other than ColdFusion.
1) Setup variables used throughout the script--giving users one section to change information about SFTP connections, filenames, file directories, etc.
2) Download the outgoing Slate XML file and save to the Sunapsis server.
3) Read this downloaded XML into an object in memory:
<cfscript>
importedSlateDataXML = fileRead(expandPath("..\batch\UASlateDataExchange\importedSlateData.xml"));
importedSlateDataServerDoc = XmlParse(importedSlateDataXML);
</cfscript>
4) Check for missing elements and insert them as needed, also including "xmltext" (value of the element).
5) There is a debug function that if set to true: the script outputs the old and new xml for comparison. I also have it output a table of useful information at a glance: number of incoming XML nodes for the Sunapsis required fields, rejected records without the required fields (todo: have this write to a file for logging), etc.
6) Output the file in Sunapsis schema:
6.1) While writing admissions.xml: check for missing elements setup in step 4, and handle conditional logic for these missing values.
6.2) While also writing the admissions.xml: Remove special characters in problematic fields (usually address).
6.3) Usually some form of university id (or temp id), first name, last name, birthdate, and gender are usually required fields. Reject records that do not contain this info.
6.4) Replace incoming info with Sunapsis codes, if needed. For example: incoming data may be "F1 - Student" and that needs replacing with the code "F1."
6.5) The script also handles other conditional replacements.
6.5) Append to admissions.xml the entire XML dump by using a loop.
7) Write out a log file of successes, failures, and other statistics.
Those are the steps the script takes. I no doubt could have written it more concisely by using more functions and hope to refine it over time.
If anyone needs help with a Slate to Sunapsis integration: contact me!
I wrote the script in ColdFusion, but regretting it. I just thought it would be one less resource on my server by utilizing existing processes. ColdFusion handles empty elements in XML once it is in an internal document in memory very strangely. I had to spend most of my time coding handling missing elements.
Tyler Clayton, who helped me with formulating this solution (he also has something similar in place at Colorado State) stated the following regarding a C# implementation:
"I wrote that in C#, and I did have a really hard time with missing elements. I actually had to set up an empty element at the top of my script, so that there is always an element to use, even if the Slate XML doesn’t contain it. This is how that looks in C#:
XElement emptyElement = new XElement("Empty");
emptyElement.Value = "";
Then when I want to populate the new XML, I fall back to that any time the element I want doesn’t exist:
// Last Name
dataFeedRecord.Element("biographical").Add(new XElement("prsn_prm_last_nm", (admitElmt.Element("Last") ?? emptyElement).Value));
The “??” operator means use the 1st value (the element from Slate) if it exists, and the 2nd value (the empty element) if it doesn’t." - Tyler Clayton
The following is an outline of how the ColdFusion script functions I wrote (if anyone wants the source: email or message me), although this would be different if written in another language other than ColdFusion.
1) Setup variables used throughout the script--giving users one section to change information about SFTP connections, filenames, file directories, etc.
2) Download the outgoing Slate XML file and save to the Sunapsis server.
3) Read this downloaded XML into an object in memory:
<cfscript>
importedSlateDataXML = fileRead(expandPath("..\batch\UASlateDataExchange\importedSlateData.xml"));
importedSlateDataServerDoc = XmlParse(importedSlateDataXML);
</cfscript>
4) Check for missing elements and insert them as needed, also including "xmltext" (value of the element).
5) There is a debug function that if set to true: the script outputs the old and new xml for comparison. I also have it output a table of useful information at a glance: number of incoming XML nodes for the Sunapsis required fields, rejected records without the required fields (todo: have this write to a file for logging), etc.
6) Output the file in Sunapsis schema:
6.1) While writing admissions.xml: check for missing elements setup in step 4, and handle conditional logic for these missing values.
6.2) While also writing the admissions.xml: Remove special characters in problematic fields (usually address).
6.3) Usually some form of university id (or temp id), first name, last name, birthdate, and gender are usually required fields. Reject records that do not contain this info.
6.4) Replace incoming info with Sunapsis codes, if needed. For example: incoming data may be "F1 - Student" and that needs replacing with the code "F1."
6.5) The script also handles other conditional replacements.
6.5) Append to admissions.xml the entire XML dump by using a loop.
7) Write out a log file of successes, failures, and other statistics.
Those are the steps the script takes. I no doubt could have written it more concisely by using more functions and hope to refine it over time.
If anyone needs help with a Slate to Sunapsis integration: contact me!