PilotFish Interface Tutorial - Microsoft SQL Server to XML & Delimited File

eiConsole v.23R1
Tutorial & Interface

End-to-End tutorial: From MS SQL Server to XML and Delimited File

Overview

This tutorial demonstrates and explains how to query a Microsoft SQL Server (MSSQLServer for short) database using SQLXML, transform the results to a simple XML and to a delimited file, and save the resulting files to a directory. In this tutorial we cover:

how to add and configure JDBC drivers
how to configure the “Database Polling (SQL)” Listener to connect to an MS SQL Server
SQLXML basics and the “Select” instruction, including creating and configuring a database and its tables and then selecting data from it
how to transform the resulting database query XML into a simple XML
how to transform the resulting database query XML into a delimited file
storing the resulting files in a directory

The tutorial expands on basic eiConsole topography and configuration, so users should be familiar with those concepts before beginning this tutorial. If you are not familiar with these concepts please review the basic, intermediate and advanced tutorials sections.

Important: the installation and configuration of an MS SQL Server is out of the scope of this tutorial. Before beginning the tutorial, the user must have access to an MS SQL Server database and have the following information on hand:

Database name
Database username
Database password
Database server hostname
Database server connection port

The database user must be able to create tables and insert data into it.

How to Use This Example

Click the link below to download a zip file containing all the files you will need for this tutorial. Once extracted, you should have the following files:

people_create_insert_script.sql
people_delimited.txt
people.xml

Preparing the Database

We’ll start by preparing an MSSQLServer database for this exercise. You can use any database which is accessible via JDBC drivers, though we’ll be using Microsoft SQL Server 2016 Express Edition for this exercise. If you do not have access to an MSSQLServer please contact your IT department or, if authorized, download and install Microsoft SQL Server 2016 from this URL: https://www.microsoft.com/en-us/sql-server/sql-server-downloads. Installation and configuration of an MSSQLServer is out of the scope of this tutorial. If you have problems installing or accessing the MSSQLServer or need credentials to access it, please contact your local IT department.

Once you have access to the MSSQLServer, you will need to create the database that will hold the data used in this tutorial and a user that will be used by the eiConsole to connect to the newly created database. You can use any tool you like to create the database and user or you can ask your local IT department to create them for you.

For this tutorial, our newly created database will be called tutorialdb and will be accessed using the following credentials:

User name = testuser
User password = testpass

Once the database and user are created you can use the downloaded people_create_insert_script.sql script file to create the database table and data required by this tutorial. Once the script is executed you should see a new table called PEOPLE that contains 100 rows. If you have problems or need help running the table generation script please contact your local IT department. The creation of an MSSQLServer database, user and database table is out of the scope of this tutorial.

Once you finish all these steps please copy all these values in a separate file or notepad since we are going to use them later in the tutorial. In addition to these values, you will also need for this tutorial the hostname or IP address of the server hosting the database to which we are connecting and the connection port. For this tutorial, our host name will be TEST_SERVER and the connection port will be 1433.

To recap, the values we are going to be using for this tutorial are:

User name: testuser
User password: testpass
Database name: tutorialdb
Database table: PEOPLE
Server host name: TEST_SERVER
Server connection port: 1433

Adding the database JDBC driver

Now that the tutorialdb has been created, the testuser has been added to it and the tutorial PEOPLE table and data have been created using the downloaded script we are almost ready to proceed with the creation with our Route.

Before proceeding with the creation of the route, we need to make available to the eiConsole the JDBC driver for the database server to which we are going to be connecting, in this case, the MSSQLSERVER. As of the publishing of this tutorial, Microsoft JDBC drivers can be downloaded at http://go.microsoft.com/fwlink/?LinkId=245496. The JDBC file will be a file, with “jar” extension, specific for the database to which you want to connect. If you have problems figuring out the correct JDBC driver file please consult the database company’s help website or contact your local IT department. In our case, the JDBC driver file we will be using to connect to our MSQLSERVER (Microsoft SQL Server 2016 Express) is called sqljdbc4.jar and was provided by Microsoft.

The first step in making the driver available to the eiConsole is to determine your current working folder:

The current Working Directory can be found at the top of the main page, under “File Management”. In our case, the working directory is “c:\Program Files\PilotFish Technology\Getting-Started-Project\”.

Next, we will copy the JDBC driver file to the libraries directory within the working directory. For this step, you need to make sure the eiConsole is closed. If you opened it to determine your current working directory, please close it before proceeding.

With the eiConsole closed, copy your JDBC driver jar to the “lib” directory within your working directory.

In our case, the destination directory will be “c:\Program Files\PilotFish Technology\Getting-Started-Project\lib” and our JDBC driver jar file is called “sqljdbc4.jar”.

After copying the JDBC driver file to the corresponding directory we will create our new route. Please go ahead and start the eiConsole now.

Creating a new Route

We’ll now create a new Route in the eiConsole called “MSSQL to XML and Delimited Files”:

To interact with our database, we have a few standard components available to us: the “Database Polling (SQL)” listener, the “Database SQL Transformation Module” and the “Database SQL” transport. Each of these uses a PilotFish language called “SQLXML,” which can be summarized as an XML-based, database agnostic set of SQL instructions.

Each module can be configured using an “input file” referencing a file containing SQLXML or, if this is not provided, by assuming that the contents of any transaction reaching that module is SQLXML. In either case, the SQLXML received will be interpreted and executed against a configured database connection, which can be a “Data Source” or JDBC-driven.

Configuring a basic “Database Polling (SQL)” listener

We’ll start by configuring a basic “Database Polling (SQL)” listener. Add a new Source and select that Listener from the Listener Type drop-down:

Start by changing to the Connection tab and providing the user name and password we created earlier.

For the JDBC URL value you will need to provide a URL with the following format and using the previously copied values: jdbc:sqlserver://<Server host name>:<Server connection port>;Database=<Database name>

For the JDBC driver value, click on the “…” button to the right of this text field and select the desired JDBC driver name from the pull-down menu that will pop up. For an MSSQLSERVER driver, the name will usually contain the words “microsoft” and “sqlserver” in the name. If you don’t see an entry with these words you can type the driver name directly in the JDBC driver field; for this tutorial, you can type “com.microsoft.sqlserver.jdbc.SQLServerDriver”. If you get any errors later related to the JDBC driver, please review the steps under Adding the database JDBC driver above.

In our example the values used are:

User name: testuser
Password: testpass
JDBC driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
JDBC URL: jdbc:sqlserver://TEST_SERVER:1433;Database=tutorialdb

With the values entered, you can click on the “Test Connection” button. You should receive a “Connection was successfully established!” message. If you get an error message please review the previous steps.

Back on the “Basic” tab, we’ll need to first provide a “Polling Interval,” which is how often our SQLXML is executed against the provided database connection. Since this is a tutorial, we’ll assume 30 seconds is sufficient:

Next, we’ll need to create our SQLXML file. Type “sql.xml” into the Input File field, then click the “Edit” button.

And select “In XML Editor”:

This will open the XML Editor. Here we can write out the body of our SQLXML.

Writing out the body of our SQLXML

As this is an XML file, we’ll start by providing the basic XML header:

<?xml version="1.0" encoding="UTF-8"?>

Our next line will be the root element, “SQLXML.” There’s a particular namespace associated with SQLXML, so we’ll define that using the “xmlns” attribute:

<SQLXML xmlns="http://pilotfish.sqlxml"> </SQLXML>

We can now provide a number of different SQLXML instructions inside the SQLXML body, including Execute, Select, Insert, Update, and Delete. We’ll use the Select instruction. The Select element has a required attribute called “into” which will specify the name of a variable for the selected rows to be placed in. We’ll call ours “records”:

<Select into="records"> </Select>

Our output should look something like this so far:

The structure of the Select instruction is pretty simple; it expects a single child element sharing names with the table to select from. Please note that different databases may impose different restrictions on case sensitivity. Underneath the table name element, we’ll provide empty tags matching the names of columns we’re interested in retrieving. We’ll provide all of them:

<?xml version="1.0" encoding="UTF-8"?> <SQLXML xmlns="http://pilotfish.sqlxml"> <Select into="records"> <PEOPLE> <FIRST_NAME /> <MIDDLE_NAME /> <LAST_NAME /> <GENDER /> <SSN /> <BIRTHDATE /> <OCCUPATION /> <VEHICLE_MAKE /> <VEHICLE_MODEL /> <ADDRESS_LINE_1 /> <ADDRESS_LINE_2 /> <CITY /> <STATE /> <ZIP /> </PEOPLE> </Select> </SQLXML>

Our output:

The Select instruction should now create a SQL query fetching the various provided columns from the PEOPLE table. The results will be stored in a variable called “records.” Now we simply need to output this variable to XML. To do so, we’ll add another instruction next to Select called “XMLOut” with an attribute called “var” specifying our “records” variable:

<XMLOut var="records" />

Our output:

Now to save our file. Click the “save” button and save the file there as “sql.xml” then close the XML Editor.

There’s one more configuration value we’ll change, located under the “Advanced” tab. Enable the “Use Single Output Stream” option:

This will cause all selected rows to come back in a single eiConsole transaction. Unchecked, and by default, the Listener will produce a separate transaction for each row selected.

Saving the “Database Polling (SQL)” listener output for use later as a source format

Save the route and switch to the eiConsole’s Testing Mode and execute the test at the Listener stage:

Once the test runs and you see results populating the table under Test Results, view the test output:

The resulting database polling XML should look like this:

Now we are going to save this output file for later use as the source when creating the transformations to XML and delimited file. Click on the “save” button on the top bar, name your file SQLXMLOutput.xml and save it in a local directory. Be sure to remember the location because we will be using this file later on.

In our case we saved our file in the “data” directory under our working directory, “c:\Program Files\PilotFish Technology\Getting-Started-Project”.

Close the result window and go back to Editing Mode:

Transforming the Database output XML to an XML file XSLT

The “Database Polling (SQL)” listener generates an XML containing all the data extracted from the database. You could use the output XML as-is or you could decide to transform the output XML to a format more suited to your system’s needs. For this tutorial, we will transform the output XML to a simple XML and a delimited file. These transformations are done using XML Stylesheet Language Transformations (XSLTs).

Adding the Polling Output XML to XML XSLT

You can configure XSLTs at either the Source or Target Transform. The reasons for choosing one or the other are usually due to topographical concerns. For this tutorial, it does not matter which we use, so we’ll just arbitrarily choose the Target Transform.

Select this stage and add a new Format. We’ll call this SQLXML to People XML:

To use XSLT, uncheck the Use Direct Relay option:

You now have a variety of options available to you. You can type in the name of an existing XSLT file, browse to one, edit a selected one, specify if you wish to cache the XSLT, enable 2.0 support, and even select which XSLT engine to use. Using the defaults for these options is more than sufficient for most transformations regardless of complexity.

Editing a new Mapping

As we have no existing XSLT document to edit, simply clicking the New button with no document selected will open the Data Mapper to edit a new mapping:

This will open the Data Mapper:

The Data Mapper makes use of three main panels. On the left side of the screen is a tree representing the Source format. The right side of the screen is a tree representing the Target format. The middle panel represents the mapping logic, also organized into a tree. At the bottom-center of the screen are tabs for switching between Mapping, XSLT, and Testing modes.

Reading the Source Format

The first thing you’ll typically do when creating a new mapping is to load in the Source and Target formats you’ll be working with. To do this for each panel, click the Open Source Format button at the top. We’ll start by loading the Source format:

This will raise a dialog allowing you to pick from a list of Format Readers. Format Readers are components that generate a format structure from some source, be it an XML file, schema file, File Specification, database, etc. For this tutorial, we’ll use the XML format reader, which allows us to use an XML sample file to infer a structure from:

Next, we’ll need to add our sample file. Click Add and browse to SQLXMLOutput.xml, which you created in the listener test step:

In our case we saved our file under “c:\Program Files\PilotFish Technology\Getting-Started-Project\data”:

The selected XML file should appear under XML Files. Click Read Format:

You should now have a Source panel that resembles this:

Reading Our Target Format

We’ll now wish to read our Target format. Select the same button on the Target panel at the right side of the screen, select the XML format reader again. You should see the previous sample listed; click Remove to remove it. Click Add and this time, browse to where you saved the downloaded sample files and select the people.xml file. The selected file should be a sample file with the desired format for the output of the transformation. Once the transformation takes place the database polling output SQLXML file will be transformed to an XML having the format of people.xml.

Once the file is added it should show appear under XML Files. Next click on Read Format:

Now you should now have a Target panel that resembles this:

Now that we have our source and target structures we will proceed to create the XSLT using the graphical user interface.

Constructing the Mapping

The first thing to do in constructing the mapping itself is to provide a “template” match. This is some element from the Source which we’ll match against and take some set of actions against. Select the “EIPData” element from the Source and drag-and-drop it onto the “stylesheet” element in the center.

This will create an “EIPData” template in the center:

Creating the People element from the Target

In XSLT, this is creating an instruction called a “template” with an attribute called “match” containing the XPath expression for the EIPData element in the Source. It effectively means, “when I encounter some EIPData element, run these instructions.” The action we want to take is to create a People element from the Target; effectively, we want to say, “When I encounter some EIPData element, I want to create a single People element.” To do this, drag “People” from the Target format onto “EIPData” in the center:

If we were to test this mapping against our Source sample, we’d get an XML output with only a “People” element. In order to create the desired XML we must map the rest of the elements under “People” to the database polling results. These results are stored under the “DATA” element, which means that each “DATA” element is a record resulting from our database poll. So, before continuing, we must tell the transformation that the mapping we are about to do is going to be done for each instance of the “DATA” element. This can be done using an XSLT structure called for-each.

In the middle panel, select the Flow Control tab. Drag the for-each structure to the “People” element we added before:

Next drag the “RECORD” element from the source structure to the “[@select]:?” element of the for-each structure just added:

Lastly, drag the “Person” element from the target structure over the for-each structure. This will tell the transformation engine to create a “Person” element for each “RECORD” element found:

We’ll now want to create the various elements shown in the Target format. Drag-and-drop each of the elements under the target “Person” element onto the “Person” in the center; you can skip the attribute elements (elements with @ next to their name) for this tutorial. Note that child elements will need to be dragged onto the respective parents. For example, “First” should be dragged onto “Name” instead of onto “Person”:

The center mapping should look exactly like this:

Providing Values

If we were to run this transformation now, we’d get a single People element with lots of empty child elements. What we need to do now is to provide each of these with values. To populate the “First” element, drag the “FIRSTNAME” element from the Source format onto it:

Continue this for each of the values. Note that you will not drag “FIRSTNAME” or “LASTNAME” onto the center “Name” – only it’s child elements (so “FIRSTNAME” onto “First”, “LASTNAME” to “Last” and so on). There is no source value for the vehicle year so that field will show empty when we perform the transformation. When you’re finished, it should look something like this:

We have two other sections to investigate now. First, click the XSLT View tab at the bottom of the screen:

This will change the mapping panel to show the underlying XSLT:

You can make changes directly to the XSLT view and they will show up in the Mapping (GUI) view. This particular editor features auto-completion, auto-formatting, and a few other useful features for doing manual editing. Next, click the “Testing” tab:

This will open the Testing mode for the Data Mapper:

This is made up of three panels. The first is the Source sample which, if you used the XML Format Builder, should already have our “SQLXMLOutput.xml” file loaded for testing. The second panel is the “Results” panel, which shows the results of the transformation. Finally, the “Output” panel shows any messages from the XSLT engine, such as errors or warnings.

Executing the Transformation

To execute the transformation, click the “Execute Transformation” button:

The “Results” panel will change to show the results of the transformation:

We’ve now completed our basic mapping. Save the mapping using the “Save” button or menu item (we’ll name ours SQLXMLOutputToPeople) and close the Data Mapper to return to the eiConsole.

Adding the Polling Output XML to Delimited File XSLT

Now that we have created our SQLXML output to XML transformation we will add an SQLXML output to Delimited File transformation to this route. This will create a delimited file with the data polled from the database concurrently with the XML created in the previous steps.

To get the database polling output XML to the desired delimited file we need to perform an XSLT transformation. This transformation will convert the database polling output XML to a predetermined format required by the Delimited and Fixed-Width File Transformation Module. Then we will configure said module to transform the resulting XML file into our delimited file.

We will start by adding a new Target. While in the Route Editing window, click the Add Target button:

You should now have two Targets in the Route Editing window:

Adding the New Format

Now we will add our new Target Transform Format. One important concept to note is that the direction of a transformer is determined by where it appears in the Route. If we configure our Transformer on the Target Transform stage then it will expect XML as input and convert that to the described delimited format. Conversely, configuring the Transformer on the Source Transform will assume delimited input and produce XML output.

The purpose of this transformation is to take the database polling output XML and transform it into a delimited file recognizable by your system. We have provided a sample delimited file with this tutorial called people_delimited.txt. This file will represent, for the purpose of this tutorial, a sample of the type of delimited files my system would expect.

Because we wish to convert from XML to a delimited format, we’ll want to add our Format on the Target Transform stage. Click that stage, then click Add Format:

We’ll name the Format based on the transform direction and the name of the structures. We’ll be using a sample called “people_delimited.txt,” that was provided with this tutorial, so the Format will be named “XML to Delimited People”:

With the Format added, the bottom half of the eiConsole screen changes to reflect the Format configuration. It is at this point that we will add the two steps we mentioned before for getting the database polling output XML to a delimited file: configure an XSLT to get the database polling output XML to a predetermined XML format, and configure the Delimited and Fixed-Width File transformation module to transform the newly transformed XML file to a delimited file. We will start with the second step so that we can obtain the predetermined XML from format to be used by the XSLT in this step.

Configuring the Delimited and Fixed-Width File Transformation Module

We are going to start with the Transformation Module section of the bottom panel. Selected “Delimited and Fixed-Width File” from the “Transformation Module” drop-down:

With the Delimited and Fixed-Width File Transformer selected, you should see a configuration panel like the following:

This particular Transformer makes use of an XML format informally called a “File Specification.” The configuration item specifies the name (and relative location) of such an XML description. If you had such an existing file, you could click “Browse” and select it, which would then copy it to the appropriate location. For this tutorial, we’ll assume that we’re starting fresh. Clicking the Edit button without any “File Specification” selected will open the “File Specification Editor;” do so:

The File Specification Editor is a graphical tool used for defining the structure of a file format. When first opened, you will be presented with a dialog asking you how you wish to begin:

The various options in this drop-down are ways to automatically build or import the structure of a File Specification. For example, you could read in definitions from a CSV file, a Cobol Copybook, or import various industry-specific Formats (depending on what eiConsole version you have).

Building a File Specification From Scratch

If you wish to build a File Specification from scratch, you’ll want to select User-defined:

Click Next and you’ll be presented with this dialog:

This dialog asks you to describe the style of Records and Fields. A File Specification is made up of Records with additional Records and Field as children. In most formats, Records are delimited, meaning that some character or set of characters separates each Record. In our people_delimited.txt sample, each line in the file represents a “Person,” which is a type of Record.

Select Delimited under “Record Style”:

The next item is “Record Delimiter,” which specifies what separates each Record. The default value, “\n,” represents a “line feed.” The convention used for escape characters is that used for Java (and most languages). Most formats use either a single line feed or a “carriage return” (\r) and a line feed (\r\n). Our sample file uses only a line feed, so set the Record Delimiter to “\n”:

Next, we’ll need to define the style of Fields. In our sample file, each Field within a Record is separated by a pipe (“|”) character. Delimited files typically use tabs (“\t”), commas (“,”), pipes, and other such characters. Fields may also be “Fixed Width,” meaning that each field has a specific length. For example, the “First Name” field might be exactly 10 characters long.

Because our sample file is delimited by pipes, select Delimited under “Field Style” and set the “Field Delimiter” to “|”:

Finally, click the Save button to open the main File Specification Editor window.

Reviewing the File Specification Editor

There are a lot of panels and areas for the File Specification Editor, but we’ll review each of these in turn to explain them.

On the left side of the screen is the “Record Structure” panel:

This panel contains the structure (so far, it’s empty) of the File Specification, defined as a hierarchy (a tree) of Records, sub-Records, and Fields. The bottom half of the panel is used to provide information and configurations for the selected Record or Field.

The top-right panel is used to show currently loaded sample data with highlighting for a selected Record:

Until we have a structure defined and a sample file loaded, this will be empty and fairly uninteresting.

Finally, the “Results Preview” panel:

This panel is used to load and/or edit a sample file, transform it, and see the results. We’ll start here.

Defining the File Specification

Click the Load icon and select the people_delimited.txt you downloaded at the start of this tutorial:

With the sample file loaded, the sample file panel should resemble the following:

Defining Our Structure

We’ll now need to define our structure. We can start by defining a Record to represent each “Person” in our sample file. Right-click on the root Node (labeled “Base”) and select Add New Record:

You will next be presented with a dialog where you are expected to name the new Record. Since this Record will represent a Person, we’ll name it accordingly:

Click the OK button and you should see the “Person” record shown in the structure underneath “Base”:

Adding Fields to the Person Record

We’ll now wish to start adding Fields to the Person Record. To do so, right-click on Person and select Add New Field:

Once again, a dialog will be raised asking you to name the Field. Our first Field is the Person’s first name, so we’ll name the Field “First Name”:

Click the OK button and you should see the Field displayed under Person:

For defining the remaining Fields, you could continue to right-click on “Person” and select Add New Field or, with the “First-Name” field selecting, simply hit “Enter / Return” on your keyboard.

Continue adding Fields for each of the following:

Middle Name
Last Name
Gender
Social Security Number
Date of Birth
Occupation
Vehicle Make
Vehicle Model
Address Line 1
Address Line 2
City
State
Zip

When you are finished, the structure should look like this:

Our structure is now fully defined. Next, select the Person Record and then highlight a single line of the sample file (this might be easier if you click at the beginning of that line, hold Shift, and hit End if you are on Windows, or hold Shift+Command, and hit the Right-Arrow key if you are on Mac):

Next, click Set Selected Text:

You’ll notice the top panel now has various fields shown, as well as blue arrows:

If you click on the various Fields in the structure, the appropriate field will be highlighted in this panel. For example, if we click “Occupation”, we’ll see “Long distance operator” highlighted:

This panel allows you to visually confirm that your structure matches your sample (or vice-versa). While this is less important in delimited files, it can be an enormous time saver when dealing with fixed-width formats.

Testing Our Definition

Next, we’ll want to test our definition. Click the Execute Transform to XML button:

You should see various Person elements appear in the results panel:

If you click to the left of these elements, it will expand them to show their respective child elements and values. For example, the first Person element should resemble this:

If you click the “XML” tab, you can also view how this would look in the resulting XML. The resulting XML should look something like this:

The XML shown in this tab is the “predetermined format XML” we mention before we started configuring the current format. We now need to save this XML for later use in the XSLT creation process. Right-click anywhere over the XML and click Select All:

Once the entire XML is selected, right-click over it once more and click Copy. This will send the selected XML to your clipboard so that we can paste it in any text editor and save it for later use.

Now open your favorite text editor, paste the copied XML and save it. You can name it anything you like as long as you remember the name since we are going to use it later. We will name the document people-predetermined-format.xml. Be sure to write down where you saved your file. Here is how the new XML document looks like:

In our case we saved our file in the “data” directory under our working directory, “c:\Program Files\PilotFish Technology\Getting-Started-Project\PilotFish”.

Our File Specification is now fully defined. One interesting aspect to note is that this specification will convert an XML file to the “people delimited” format or the resulting delimited file back to the XML format without any additional configuration or changes; File Specifications are inherently bi-directional.

Save your specification with the Save button, providing any name you feel appropriate (we’ll use Delimited People):

Finally, close the Editor to return to the eiConsole. Our Format configuration should now look like this:

Creating the XSLT for the XML to Delimited File Transformation

Now we are going to perform the second step in configuring this Target Transform, creating the XSLT that will be used for transforming the database polling output XML to the predetermined format XML needed by the Delimited and Fixed-Width File transformation module. The steps are going to be the same we followed in the Transform the database output XML to an XML file XSLT before with the slight difference that we are going to use the people-predetermined-format.xml file we created in the previous step as the “target format”.

Let’s start by unchecking the Use Direct Relay option:

Editing a New Mapping

As we have no existing XSLT document to edit, simply clicking the New button with no document selected will open the Data Mapper to edit a new mapping:

This will open the Data Mapper: