Log out?

End-to-end tutorial: From MS SQL Server to XML and delimited file

eiConsole v.13.17R2
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:

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:

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:

icon_sample-data

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:

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 host name 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:

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 the 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:

mssqltutorial001

The current Working Directory can be found at the top of the main page, under “File Management”. In our case, the working directory is “/PilotFish”.

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.

mssqltutorial002

In our case the destination directory will be “/PilotFish/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”:

mssqltutorial003

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:

mssqltutorial004

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.

mssqltutorial005

In our example the values used are:

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:

mssqltutorial006

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

mssqltutorial007

And select “In XML Editor”:

mssqltutorial008

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

Writing out the body of our SQLXML

mssqltutorial009

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:

mssqltutorial010

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 interesting 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:

mssqltutorial011

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:

mssqltutorial012

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

mssqltutorial013

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

mssqltutorial014

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 source format

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

mssqltutorial015

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

mssqltutorial016

The resulting database polling XML should look like this:

mssqltutorial017

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.

mssqltutorial018

In our case we saved our file in the “data” directory under our working directory, “/PilotFish”.

Close the result window and go back to Editing Mode:

mssqltutorial019

mssqltutorial020

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:

mssqltutorial021

To use XSLT, uncheck the Use Direct Relay option:

mssqltutorial022

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:

mssqltutorial023

This will open the Data Mapper:

mssqltutorial024

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:

mssqltutorial025

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:

mssqltutorial026

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

mssqltutorial027

In our case we saved our file under “/PilotFish/data”:

mssqltutorial028

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

mssqltutorial029

You should now have a Source panel that resembles this:

mssqltutorial030

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.

mssqltutorial031

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

mssqltutorial032

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

mssqltutorial033

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.

mssqltutorial034

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

mssqltutorial035

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:

mssqltutorial036

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:

mssqltutorial037

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

mssqltutorial038

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 “DATA” element found:

mssqltutorial039

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”:

mssqltutorial040

The center mapping should look exactly like this:

mssqltutorial041

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:

mssqltutorial042

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:

mssqltutorial043

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

mssqltutorial044

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

mssqltutorial045

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:

mssqltutorial046

This will open the Testing mode for the Data Mapper:

mssqltutorial047

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:

mssqltutorial048

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

mssqltutorial049

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.

mssqltutorial050

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:

mssqltutorial051

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

mssqltutorial052

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 to 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:

mssqltutorial053

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”:

mssqltutorial054

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:

mssqltutorial055

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

mssqltutorial056

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:

mssqltutorial057

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:

mssqltutorial058

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:

mssqltutorial059

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

mssqltutorial060

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”:

mssqltutorial061

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”:

mssqltutorial062

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 “|”:

mssqltutorial063

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

Reviewing the File Specification Editor

mssqltutorial064

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:

mssqltutorial065

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:

mssqltutorial066

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

Finally, the “Results Preview” panel:

mssqltutorial067

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:

mssqltutorial068

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

mssqltutorial069

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:

mssqltutorial070

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:

mssqltutorial071

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

mssqltutorial072

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:

mssqltutorial073

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”:

mssqltutorial074

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

mssqltutorial075

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:

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

mssqltutorial076

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):

mssqltutorial077

Next, click Set Selected Text:

mssqltutorial078

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

mssqltutorial079

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:

mssqltutorial080

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:

mssqltutorial081

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

mssqltutorial082

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:

mssqltutorial083

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:

mssqltutorial084

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:

mssqltutorial085

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.

mssqltutorial086

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:

mssqltutorial087

In our case we saved our file in the “data” directory under our working directory, “/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):

mssqltutorial088

mssqltutorial089

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

mssqltutorial090

Creating the XSLT for the XML to Delimited File transformation

Now we are going the 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:

mssqltutorial091

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:

mssqltutorial092

This will open the Data Mapper:

mssqltutorial093

Reading the Source Format

We are now going to load 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:

mssqltutorial094

Select the XML format reader from the list of available format readers. This will allow us to use an XML sample file to infer a structure from:

mssqltutorial095

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

mssqltutorial096

In our case we saved our file under “/PilotFish/data”:

mssqltutorial097

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

mssqltutorial098

You should now have a Source panel that resembles this:

mssqltutorial099

Reading our Target format

We’ll now wish to read our Target format. Remember that this time we are going to use the people-predetermined-format.xml file created before as the source 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 people-predetermined-format.xml file and select it. The selected file in this step should be a sample file with the predetermined format needed by the Delimited and Fixed-Width File transformation module. Once the transformation takes place the database polling output SQLXML file will be transformed to an XML having the format of people-predetermined-format.xml.

mssqltutorial100

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

mssqltutorial101

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

mssqltutorial102

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. Select the “EIPData” element from the Source and drag-and-drop it onto the “stylesheet” element in the center.

mssqltutorial103

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

mssqltutorial104

Creating the XCSData element from the Target

Drag “XCSData” from the Target format onto “EIPData” in the center:

mssqltutorial105

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

mssqltutorial106

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

mssqltutorial107

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 “DATA” element found:

mssqltutorial108

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.

mssqltutorial109

The center mapping should look exactly like this:

mssqltutorial110

Providing Values

To populate the “FIRST-NAME” element, drag the “FIRSTNAME” element from the Source format onto it:

mssqltutorial111

Continue this for each of the values. When you’re finished, it should look something like this:

mssqltutorial112

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

mssqltutorial113

Send transformed file to a directory

Now that we have extracted some information from a database and transformed the extracted data into an XML and a Delimited file we need to deliver these files to the desired destination. For this tutorial, we will save both files to a local directory. The steps for each file are the same with the exception of the filenames we are going to use for saving the files.

Saving the target XML to a local directory

In order to deliver the output of a transaction, a Transport needs to be configured. We will start by configuring the Transport for our SQLXML to People XML transformation.

Start by clicking on the cell labeled Transport next to the SQLXML to People XML transformation:

mssqltutorial114

Once highlighted, the Transport Configuration panel will appear below. Select the desired Transport Type from the Transport Type drop-down. For this tutorial we will select the Directory / File transport:

mssqltutorial115

Once selected, the transport configuration panel will show:

mssqltutorial116

Tabs and configuration options will vary based on the module selected in the Transport type dropdown.

Next, we will fill in the configuration details in the Transport Configuration panel. Start by selecting the desired destination folder. You can type in the destination folder’s path or click the “…” button, browse to it and select it. In our case, we are going to set our destination folder to “/PilotFish/out”. Please remember your directory name since you are going to search within it for the resulting files when we test the route.

mssqltutorial117

Now we need to name our output file. We will name our file databse-query.xml. In order to name your file, you need to enter the desired filename (database-query) in the “Target file name” field and the file extension (xml) in the “Target file extension” field.

mssqltutorial118

There are other options you can set for this transport but the ones we just set are enough for now. You can explore the other options after this tutorial.

Saving the target delimited file to a local directory

Now we need to configure the Transport for the XML to Delimited People transformation. The steps to follow are the same as for the SQLXML to People XML transport just with a different filename. We will name the output file for this transformation databse-query.txt.

Start by clicking on the cell labeled Transport next to the XML to Delimited People transformation:

mssqltutorial119

Next select the Directory / File transport and configure it with the following values (use your own values if you changed them in the previous step):

mssqltutorial120

Your route is complete. Now open the File menu and click on Save Current Route to save your new route:

mssqltutorial121

Next step will be to test the new route.

Testing the route

Now that the route is configured from Source to Target you can test its behavior. Within the Route menu, select Testing Mode.

mssqltutorial122

When you switch to testing mode, all of the icons between the Source and the Target become question marks, indicating Stages that you may choose to test.

mssqltutorial123

For this tutorial, we will use the default testing configuration since we want the route to connect to the database, retrieve the data and deliver the resulting transformed files to a local directory.

To execute the complete route from Source to target just click on the Execute Test button in the top toolbar:

mssqltutorial124

In a few seconds, the data will be extracted from the database by our listener. As each stage completes the question marks will be replaced by green check marks indicating that the stage has completed successfully.

If there’s an error, the blue question mark will become a red X. You can click on the stage with the red x to view the errors. If for example, the x is in the Source Transform stage, you may want to retrace your steps for that stage to see if you missed a step, or failed to check a box or enter a field, etc.

Once all the stages have finished you may click the Stop button. If everything worked correctly you should see green check marks in all the stages like this

mssqltutorial125

You can now look at how the data appeared at each stage. With the Listener stage selected, click the View Stage Output button. The “Stage Output Viewer” window will show you the database polling output XML:

mssqltutorial126

With the first Transport stage selected, click the View Stage Output button. The “Stage Output Viewer” window will now show you the transformed XML ready to be delivered to your system:

mssqltutorial127

With the second Transport stage selected, click the View Stage Output button. The “Stage Output Viewer” window will now show you the delimited file ready to be delivered to your system:

mssqltutorial128

Now if you open your system’s file browser and navigate to the destination folder we set for the two Transports (“/PilotFish/out” in our case) you will see two files created. If you used our naming convention, the new files are called database-query.xml and database-query.txt.

mssqltutorial129

These files were created by the two transports and contain the output data we saw while testing the route:

mssqltutorial130

This concludes this tutorial. If you did not get the desired output or if you received an error in any stage please go over the steps for that section again.

This is a unique website which will require a more modern browser to work! Please upgrade today!

Thanks!

Our editors are notified.

Close