Exercise 8.3 - SQLXML Querying and Mapping - Online Resource Center

Purpose:

To practice querying data from a database and mapping it using SQLXML.

References:

Tutorial – SQLXML Selecting Rows

Note: This exercise assumes the completion of Unit 8.2, so data will currently be populated in the H2 database. If that exercise hasn’t been completed, this tutorial will not have sample data to retrieve.

Create a Route

1. From the eiConsole File Management screen, select the Add Route button.
2. In the dialog that appears, enter the name DB to People 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 H2 Database.
3. Select the Choose Source Icon button, and scroll down to select one of the database icons.

Configure the Database Listener

1. Select the Listener stage in the Route Editing Grid. The configuration panel will appear at the bottom.
2. Select Database Polling (SQL) 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. In the Basic tab of the Listener’s configuration, click the … button next to the Input File text field. When the File Selector opens, navigate to the sample files directory and select the file called query_people.sql.xml. When prompted to copy it into the route’s directory, press Yes.
4. Set the Polling Interval to 30 seconds.
5. Go to the Connection tab and in the configuration panel that appears, enter the following database information:
  1. Username: sa
  2. Password: (leave this blank)
  3. JDBC Driver: org.h2.Driver
  4. JDBC URL: jdbc:h2:~\test
6. In the Advanced tab, click the checkbox Use Single Output Stream.

Add Source Format and Open the 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 DB to People 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

1. Use the Open Source Format button above the Source Format panel to open the Select Format dialog. Choose XML from the Format Reader selection box.
2. Click the Add button and in the File Selector, navigate to where the sample files are located. Choose the file called sql_select.xml. Click OK and then click Read Format.
3. Use the Open Target Format button above the Target Format panel to reopen the dialog. Select the XML format reader again and select the sample file called people.xml. Click OK and then click Read Format.
4. The Source and Target Format panels should now be populated.

Map the Root Nodes and Add Iteration

1. Select the EIPData 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 People 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 EIPData 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 People 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.
6. Select the Person node that is the child of the People node in the Target format tree and drag it onto the for-each node in the center mapping panel.

Map SQL Data to People XML

1. Expand all children of the Person node in the Target format tree and drag all the children onto the center mapping panel, dropping them onto the Target Person node that is there.
2. Map the children of the Source PERSON node onto those Target nodes:
  1. Target: @id -> Source: Person_ID
  2. Target: Name/First -> Source: First_Name
  3. Target: Name/Middle -> Source: Middle_Name
  4. Target: Name/Last -> Source: Last_Name
  5. Target: Gender -> Source: Gender
  6. Target: SSN -> Source: SSN
  7. Target: BirthDate -> Source: Birthdate
  8. Target: Occupation -> Source: Occupation
  9. Target: Vehicle/Make -> Source: Vehicle_Make
  10. Target: Vehicle/Model -> Source: Vehicle_Model
  11. Target: Address/Line1 -> Source: Address_Line_1
  12. Target: Address/Line2 -> Source: Address_Line_2
  13. Target: Address/City -> Source: City
  14. Target: Address/State -> Source: State
  15. Target: Address/Zip -> Source: Zip

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

1. Select the Transport stage in the Route Editing Grid. The configuration panel will appear at the bottom.
2. Select Directory/File from the Transport Type selection box. Optionally, click the … button to open the Module Search Dialog, which provides a faster way to find the module you’re looking for.
3. Select the Target Directory using the … button to open the file selector.
4. Set people-output as the Target File Name.
5. Set xml as the Target File Extension.

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 People XML File.
3. Select the Choose Target Icon button and scroll down to select one of the XML 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.

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.
3. In the OS File Explorer, open the directory selected as the Target Directory. The file people-output.xml should be there, with the transformed contents produced by the XSLT.

Output Files

1. Compare the results from the exercise with the following example output files to see if they are correct.
  1. people-output.xml

Structured Data

Exercise 8.3 – SQLXML Querying and Mapping