Module 3: XML to database¶
Introduction¶
Module 3 in the Introduction to the Jitterbit Integration Studio training course demonstrates reading XML data from an SFTP server and upserting it to a PostgreSQL database.
Prerequisites¶
This page assumes you have completed Module 1 - Database to text, where you configured connections to Jitterbit's training PostgreSQL database and SFTP server.
Summary¶
In this module, you will connect to the training SFTP server to pull records from the customer.xml
file and add them to the customer
table on the PostgreSQL database.
The completed operation will look like this:
This operation uses this transformation mapping:
1. Download an XML schema¶
First, download an XML file to provide as a schema in a later step:
- Log in to Jitterbit's training SFTP server.
- In the
DataSets
>Customer
directory, download thecustomer.xml
file.
2. Configure an FTP activity¶
For this module, you continue working within the same project and reuse the same SFTP server connection as in Module 1, but configure a separate FTP Read activity to pull the XML data from the server:
-
Within the same project as Module 1, create a new workflow and name it "Module 3."
-
From the component palette's Project endpoints and connectors tab under the Endpoints filter, click the "SFTP" connection you created in Module 1 to show the connection's activity types:
-
Drag an FTP Read activity type to the component drop zone on the design canvas to create an instance of an FTP Read activity in a new operation.
-
Rename the operation to "XML to DB."
-
Double-click the FTP Read activity within the operation to open its configuration.
-
Configure the FTP Read activity:
- Name: "XML SFTP"
- Provide Response Schema: Select Yes, Provide New Schema and click Upload File to browse to and upload the
customer.xml
file you downloaded from the training SFTP server. - Path: Provide the path to the
customer.xml
file on the training SFTP server: "/DataSets/Customer" - Get Files: "*.xml". Click Next.
- Data Schema: Review the data schema and click Finished to return to the design canvas.
The final screen of activity configuration should look similar to this:
3. Configure a database activity¶
In this step, you use the same connection to the PostgreSQL database as in Module 1, but configure a separate Upsert activity:
-
From the component palette's Project endpoints and connectors tab under the Endpoints filter, click the "Postgres Database" connection you created in Module 1 to show the connection's activity types:
-
Drag a Database Upsert activity to a component drop zone on the right of the FTP Read activity within the operation on the design canvas.
-
Double-click the Database Upsert activity within the operation to open its configuration.
-
Configure the Database Upsert activity:
- Name: "Upsert to Postgres DB"
- Provide the Table Names Reference: Refresh the tables and select the customer table. Click Next.
- Select Update Keys: Clear the
id
(Key) field, and select thecompany
field. Click Next. - Data Schema: Review the data schema and click Finished to return to the design canvas.
The final screen of activity configuration should look similar to this:
4. Configure a transformation¶
Next, you create a transformation to transform data from the FTP source to the database target:
-
On the design canvas, hover over the area between the FTP Read activity and the Database Upsert activity until a component drop zone appears.
-
Click the drop zone and select New Transformation. A new transformation will open for you to configure:
- Transformation Name: "XML to DB"
- Source: The source schema is already provided (on the left).
- Target: The target schema is already provided (on the right).
-
Drag the
customer
source node to thecustomer
target node and select Automap. -
Along the top of the transformation header, click the gray Preview button to begin the preview process for testing and validating the mappings. Click Next to deploy the components listed. On the next screen, select Upload New File to Test and provide the
customer.xml
file that you downloaded earlier. Click Finished. -
The preview screen displays data populated from the source and mapped to the target. Click the right and left arrows to cycle through the imported data.
-
After reviewing the data, click Return to Workflow. Clicking this button saves the work you did in the transformation.
The transformation preview should look similar to this:
5. Deploy and run the operation¶
Finally, with all operation steps configured, you deploy and run the operation:
- In the top right of the operation, click the actions menu icon and from the menu click Deploy and Run. The operation status is displayed in the lower left of the operation.
- Once the operation is successful, log in to Jitterbit's PostgreSQL database and view the additional records in the customer table.