We’ll start by clearing the rows from our existing “People” database table. Execute this query against it:

DELETE FROM PEOPLE

You should get a message indicating that rows have been affected:

We’ll continue by creating a new Route called “SQLXML – Inserting and Updating Rows,” adding and configuring a Directory Listener to check a folder for “people.xml” files, and adding a “Database SQL Transport”:

We’ve chosen to name this particular Transport “Inserting Outbound,” as we’ll later be creating a second to handle updates.

Configure the “Connection” tab using the credentials and information from the previous tutorial:

Username: sa
Password:
Driver: org.h2.Driver
URL: jdbc:h2:tcp://localhost/~/tutorial

Then click the “Test Connection” button to verify it all works correctly:

In the previous tutorial, we configured a Listener using the “Input File” field to provide the SQLXML to be used. For this Transport, we’ll do things a bit differently. Since we’re expecting the “people.xml” sample to be picked up by our Listener, we’ll want to make use of that data. We can, therefore, configure an XSLT document to transform the “people.xml” format to SQLXML, which in turn will be executed by the Transport.

On the Target Transform stage, add a new Format called “People XML to Inserts”:

Uncheck the “Use Direct Relay” checkbox and click “New” to open the Data Mapper. Read “people.xml” in for the Source Format:

We’ll now want to read in the Target format, for which we’ll use a new type of Format Reader called the “SQLXML Format Builder”:

This dialog expects the same configuration values used by the Listeners and Transports we’ve covered. Provide the credentials and connectivity information, click “Test” to ensure they’re correct, then click “Read Format”:

This will raise a dialog which allows you to select from a list of available database tables. Select “People” and hit “OK”:

This will populate the Target format with a fairly complex structure:

The Target format is now a representation of the entire set of SQLXML structures. Underneath these, you’ll find your database-specific information, where applicable. For example, click on the “Select” we used for the last tutorial:

Working with this format is, therefore, a matter of dragging and dropping its items into the mapping panel while maintaining its provided structure.

We’ll want to perform an insert for each Person in our Source, so begin by mapping “People” onto the “stylesheet” element:

Drag the “SQLXML” root from the Target onto the added People element:

Drag XSLT Structures → Flow Control → for-each onto the added SQLXML element:

Drag the “Person” element from the Source onto the added “for-each”:

Next, drag the “Insert” element from the Target onto the “for-each” element, then the table name, “People,” onto that:

Map each column name you’re interested in populating (all except “PERSON_ID”) onto the table name:

Now we just need to map each equivalent value from the Source onto these values:

We’ve now mapped our “people.xml” to SQLXML, creating an “Insert” instruction for each “Person” in the source document.

Return to the eiConsole, switch to the Testing Mode, and run the “people.xml” file all the way through:

Here we can see the original “people.xml” contents. In the Target Transform stage, view the output after the XSLT stage:

We can see the SQLXML generated. If you then look at the Transport stage, you’ll see that it has executed successfully. There’s no output to view, but we can go query our database with:

SELECT * FROM PEOPLE

We can see that our Insert statements worked successfully.

Let’s now explore the “Update” instruction. Add and configure a new Target similar to the first, naming this one to indicate it’s intended for updates:

Add a new Format to this Target’s Transform stage, this time called “People XML to Updates”:

Open the Data Mapper once again and read the same Source and Target formats in:

For this mapping, we’ll just provide one Update (not under a “for-each”):

We’ll update each row with a “GENDER” value of “Female” to have a “FIRST_NAME” value of “Updated.” Add both columns to the mapping:

Add a  XSLT Structures → Output → text value to “FIRST_NAME” with the value “Updated,” add an “attribute” to “GENDER” with the name “key” and a “text” value of “true,” and then add a “text” value to “GENDER” of “Female”:

This will now update all PEOPLE records where “GENDER” is “Female” with a “FIRST_NAME” of “Updated.”

Save the mapping, switch to the eiConsole Testing Mode, and run the “people.xml” just through the new Target Transform onward:

Once again, we’ll confirm our updates by querying the database directly. Try this query:

SELECT * FROM PEOPLE WHERE GENDER = 'Female'

You’ll see that each such entry now has a “FIRST_NAME” of “Updated”:

If the results match the above you are all set. This concludes this tutorial.