Log out?

Exercise 8.2 – SQLXML Inserts

Purpose:

To practice inserting data into a database using SQLXML.



Steps:

  1. 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.


  2. 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.


  3. 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’re looking for.
    3. Assign a polling directory, using the “…” button to open the file chooser. The polling directory should NOT be the directory with all the sample files, since files will be moved/deleted by the listening process.


  4. 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”.


  5. 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 Chooser navigate to where the the sample files are located. Choose the file called “people.xml”. Click “OK”, and then click “Read Format”.
    3. Use the “Open Target Format” button above the “Target Format” panel to re-open the dialog. This time, select the “SQLXML” format reader.
    4. In the configuration panel that appears, enter the database information:
      1. Username: “eip”
      2. Password: “eip”
      3. JDBC Driver: “org.h2.Driver”
    5. For the last item, the JDBC URL, enter the JDBC URL from the H2 login screen.
    6. Click “Read Format”. A new dialog should open showing a list of the tables in that database. Select the “People” table and click “OK”.
    7. The source and target format panels should now be populated.


  6. 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.


  7. Add Insert
    1. In the target format tree, find the node called “Insert”. Select it and drag it onto the center mapping panel, dropping it on the “for-each” node.
    2. Expand the “Insert” node in the target format tree. It will have a child node called “People”, which represents the “People” table. Select it and drag it onto the center mapping panel, dropping it onto the “Insert” node there.
    3. Expand the “People” node in the target format tree. Its child elements will be the columns in the “People” table. Select them all and drag them all onto the center mapping panel, dropping them on the “People” node there.
    4. Map the child nodes of the “Person” node in the source format tree onto the “People” table column nodes in the center mapping panel:
      1. Source: @id -> Target: Person_ID
      2. Source: Name/First -> Target: First_Name
      3. Source: Name/Middle -> Target: Middle_Name
      4. Source: Name/Last -> Target: Last_Name
      5. Source: Gender -> Target: Gender
      6. Source: SSN -> Target: SSN
      7. Source: BirthDate -> Target: Birthdate
      8. Source: Occupation -> Target: Occupation
      9. Source: Vehicle/Make -> Target: Vehicle_Make
      10. Source: Vehicle/Model -> Target: Vehicle_Model
      11. Source: Address/Line1 -> Target: Address_Line_1
      12. Source: Address/Line2 -> Target: Address_Line_2
      13. Source: Address/City -> Target: City
      14. Source: Address/State -> Target: State
      15. Source: Address/Zip -> Target: Zip


  8. Save Mapping
    1. Now that the mapping is complete, select the button with a floppy disk icon from the top toolbar to save changes. 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”.


  9. Configure the Transport
    1. Select the “Transport” stage in the “Route Editing Grid”.
    2. Select “Database (SQL)” 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. In the configuration panel, open the “Connection” tab.
    4. Enter in the database credentials:
      1. Username: “eip”
      2. Password: “eip”
      3. JDBC Driver: “org.h2.Driver”
    5. For the last item, the JDBC URL, enter the JDBC URL from the H2 login screen.


  10. 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


  11. Prepare to Test
    1. In the menu bar at the top, select “Route” -> “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.


  12. 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. Re-open the H2 database, and log back into it.
    4. Perform a select query on the People table (type this in and press the Run button): select * from people;
    5. The records from the people.xml file should be in the database table.


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

Thanks!

Our editors are notified.

Close