Universal Connections Part 2: XML

Welcome to part two of our multipart pasTransfer Universal Connection series.  Today, I am going to introduce you to the pasTransfer XML File connections and give you an overview of how the connections can be used to read and transform XML files. The XML File connections consist of a source and destination connection that are capable of processing the vast majority of XML files without the need for a custom connection. 

The Extensible Markup Language (XML) is a specification that allows the representation of data in a hierarchical, well-formed document.  With the use of XML Schema Definitions (XSDs), we are able to enforce the validity of the document and generate XPath expressions that we use to navigate the document programmatically.  An XSD specifies how to describe the elements in an XML document formally.  With the use of Extensible Stylesheet Language Transformations (XSLTs), we are able to apply transformations on an XML file, formatting it as another XML document, HTML, plain text, and much more.
 
XML File Source Connection
 
The XML File source connection allows you import data in most XML files into all of our wizards.  pasTransfer can read both simple XML files and some of the more complex files with multiple namespaces and complex types.  In order to do this, we need the set of XSDs defining the exact structure the file adheres.  We have provided a few options to get your XSDs into pasTransfer. 

If you already have XSD files for the XML document, the preferred method is to load the files into pasTransfer.  Otherwise, pasTransfer has an awesome feature called Schema Inference. The .NET framework will load up the source file and detect the structure, recurring elements, and types within the documents, creating a collection of XSDs representing the source file.  Typically, in a single-schema file, this works without a hitch.  For complex documents, however, it may be necessary to make minor changes to namespaces within one or more of the XSDs. 

For the more technical users, feel free to edit these XSDs in our code editor (or a text editor of your choice, and paste the results back in), or even add and remove XSDs manually from scratch.  When ready to proceed to the next page, the XSDs validate the source file.

Below, you can see a sample of an XSD inferred from a pasTransfer Native XML file:


 
Now, it is time to define which parts of the XML file you want to import.  Bear in mind, each wizard has different sets of parameters that are required in order to import, but the process to define parameters is constant.  The right hand side of the page will depict parameters specific to the wizard, but the left side will always display the hierarchical layout of the XML file, depicting elements and attributes, parent and child relationships in a tree view.
 

 
The nodes within the tree view on the left hand side represent the elements and attributes within the XML file.  The commercial at (@) sign precedes attribute names, as this is also how you reference them with the XPath language.  The first thing you must do is find the recurring element that represents the line of data you wish to import.  In this specific case, it is the JournalLine tree node.  This is because the XML document contains multiple recurring JournalLine elements inside of the JournalLines tag.  Now we can click-and-drag it to the Journal Lines Path in order to generate a simple XPath expression navigating to those sets of nodes.
 

 
Once you specify the path, dragging tree nodes into other fields will generate our new XPath macro.  We realized it could be necessary to hard-code values, or even use global macro commands and pass in values from pasUnity for some of the fields.  With fields pointing to collections of elements in the document, the result must be an XPath expression.  This is the same for any field designated as a Path, including recursive items. If the field is not labeled as a path, such as the account code, you must tell pasTransfer to evaluate this field as an XPath expression if the value is coming from the document.  This will allow the use of values that are not included in the document.
 

 
Some fields also allow the use of recursion – repeating elements inside of each line element.  Some examples include phone numbers, addresses, and attributes.  For example, if you are in the vendor wizard and each vendor element has multiple address elements, you can set the recursive address path to the location of the address elements relative to the current vendor line path, and extract each address element for each address line inside a single vendor.
 

 
After you have provided values for each field, click next to provide any additional details required for the wizard, and finally import your data.  After data has been imported, you can apply a summarizer, mappings, and many other additional features.  Afterwards, it is time to move on to the destination configuration.
 
XML File Destination Connection
 
The XML File destination connection allows you to apply XSLT transformations onto an XML file we structured off the imported source data.  Depending on how you write your XSLT stylesheet, you can transform the XML into another XML file or other file formats such as HTML, Plain Text, and much more.  XSLT transformations are powerful, and even allow the use of common .NET scripting languages such as C# and VB.Net.
 
The first thing you will notice when you reach the configuration page in the wizard of your choice is that everything is empty.  Everything except a samples drop down box.  We will get to that in a moment though.  This page is nearly identical for every single wizard.  The first thing you will probably want to do is click the Display Tree View button on the bottom left hand side of the page.  We did this because in order to generate the tree view, we need to iterate over all source lines of data to generate a sample of XML.  If you already have a stylesheet defined, and you are trying to run through the wizard as quickly as possible, we do not want to slow you down with unnecessary processing.  Once you click this button, you will see a tree view that shows a complete hierarchical representation of the XML file.
 
If you decide that a detailed, in-depth XML file is what you are looking to create, and the structure defined by us is adequate for your needs, you do not need to do anything else here.  Click next, and you will be able to create a large, detailed XML file containing every aspect of the data that the source connection imported.  If you are looking to apply an XSLT transformation to the XML file, you can begin editing in the code box.  If you are unfamiliar with XSLT and the structure of the stylesheet, you can load one of our samples to see some of the capabilities of transformations.  If you choose to use scripting languages, you must select the enable scripts checkbox.  The XSLT stylesheet must be well formed and valid in order to compile it for the transformation.  If you do not enable scripting and scripting tags are present, the compile will fail.  You may also use macros within the code editor, but be sure that you escape all square brackets that are not macros.
 
Below, you will see the transformation of an XML file into an HTML table and the stylesheet I used.  The first image is a sample of the XML without a stylesheet, the second is the HTML stylesheet, and the third is the HTML table that is the result of the transformation.
 
Conclusion

We have now introduced you to the pasTransfer XML File universal source and destination connections.  You have learned how to use pasTransfer to import data using the XML File source connection and transform that data into both a pasTransfer formatted XML file and an HTML table using the XML File destination connection.  We hope you found this part of series educational and informative.  Come back next week for part three of our series introducing the Excel universal connections.