Exercise 8.2 - SQLXML Inserts - Online Resource Center

Purpose:

To practice inserting data into a database using SQLXML.

References:

Create a Route

1. From the eiConsole File Management screen, select the Add Route button.
2. In the dialog that appears, enter the name People to DB and select OK.
3. The Route should now appear in the table on the screen. Double-click it to open it.

Document Source System

1. Select the Source System stage in the Route Editing Grid. The configuration panel will appear at the bottom.
2. Change the System Name to XML File.
3. Select the Choose Source Icon button, and scroll down to select one of the XML icons.

Configure the Listener

1. Select the Listener stage in the Route Editing Grid. The configuration panel will appear at the bottom.
2. Select Directory/File from the Listener Type selection box. Optionally, click the … button to open the Module Search Dialog, which provides a faster way to find the module you want.
3. Assign a polling directory, using the … button to open the file selector. The polling directory should NOT be the directory with all the sample files since files will be moved/deleted by the Listening process.

Add Source Format and Open Data Mapper

1. Select the Source Transform stage in the Route Editing Grid. The configuration panel will appear at the bottom.
2. Click the Add Format button. In the dialog that appears, name the format People to DB and click OK. The transformation configuration panel will appear below.
3. On the right-hand side, in the XSLT To XML configuration panel, uncheck the Use Direct Relay button. The options in that panel will now be enabled.
4. Click the New button beneath the checkbox to open the Data Mapper.

Load Source and Target Formats

Map the Root Nodes and Add Iteration

1. Select the People Node that is the root of the tree in the Source panel to the left. Drag it onto the center panel and drop it on the stylesheet node that’s already there.
2. Select the SQLXML node that is the root of the tree in the Target panel to the right. Drag it onto the center panel and drop it on the People node that was just dropped there.
3. Above the center mapping panel is the extensions panel. The currently displayed tab, Filter by Pattern, has a text field called Pattern. Type for-each into that field.
4. A single item should be present in the panel. This item should also be called for-each. Select it and drag it onto the SQLXML node in the center mapping panel.
5. Select the Person node that is a child of the People node in the Source Format tree, and drag it onto the @select node beneath the for-each node in the center mapping panel.

Add Insert

Save Mapping

1. Now that the mapping is complete, click the Save icon in the toolbar above the tree. When prompted for a name, use the default, transform.
2. Click the X button in the top right corner to close the Data Mapper and return to the Route Editing Grid.

Configure the Transport

Document the Target System

1. Select the Target System stage in the Route Editing Grid. The configuration panel will appear at the bottom.
2. Change the System Name to H2 Database.
3. Select the Choose Target Icon button and scroll down to select one of the database icons.

Prepare to Test

1. In the menu bar at the top, select Mode -> Testing Mode. Make sure the changes to the Route are saved.
2. Copy the file called people.xml from the Samples directory provided into the directory chosen as the polling directory.

Perform the Test

1. At the top of the Testing Mode screen, select the Execute Test button. 2. If the Route was configured successfully, all the Stages should light up with green checkmarks.
2. Reopen the H2 database and log back into it.
3. Perform a select query on the People table (type this in and press the Run button): select * from people;
4. The records from the people.xml file should be in the database table.

Exercise 8.2 – SQLXML Inserts