In this exercise, we will create a Chain that illustrates how to convert an XML file to CSV. Also, we will explore how to compare datasets to isolate differences.

Primary Learning Objective

To highlight the XML BizApp.

Secondary Learning Objectives

This exercise also highlights Tabular Transformation Smart Filters, Change Delimiter, and Advanced Query Commands.

Prerequisites

Supporting Template

XML Data


Step 1: Create a Chain

  • From the Sample Workspace for Transformations Workspace, select the DEV Environment.

  • Add a new Chain.

    • Name the Chain Transformation QS - XML.

  • Save the Chain.


Step 2: Retrieve Data in CSV Format

Use the HTTP BizApp to retrieve headcount-related data in CSV format from a data source for which a OneCloud Integration Studio BizApp does not exist. In later steps, we will retrieve similar headcount information in XML format and then compare the datasets to identify differences.

  • Add a GET Command from the HTTP BizApp to the Start node.

  • Configure the Command with the following:

Name

GET - Headcount in CSV Format

User Name

<leave blank>

Password

<leave blank>

CA Certificate

<leave blank>

Certificate

<leave blank>

Certificate Private Key

<leave blank>

Show Response

Checked

URL

employee-csv Workspace Variable

Query string

<leave blank>

Content type

text/csv

Response

<leave blank>

  • Save the Command


Step 3: Retrieve Data in XML Format

Use the HTTP BizApp to retrieve headcount-related data in XML format from a data source for which a OneCloud Integration Studio BizApp does not exist. As noted in step 2, in a subsequent step, we will compare the XML dataset to the CSV dataset to identify differences.

  • Add a GET Command from the HTTP BizApp to the Chain.

  • Connect the Start Node (GET - Headcount in CSV Format) to the GET Command.

  • Configure the Command with the following:

Name

GET - Headcount in XML Format

User Name

<leave blank>

Password

<leave blank>

CA Certificate

<leave blank>

Certificate

<leave blank>

Certificate Private Key

<leave blank>

Show Response

Checked

URL

employee-xml Workspace Variable

Query string

<leave blank>

Content type

application/xml

Response

<leave blank>

  • Save the Command


Step 4: Add a File Preview

Use the List File Content Command of the File Utilities BizApp to preview the XML file in order to inspect the envelope. Understanding the XML envelope is critical to being able to properly parse the XML data.

  • Add a List File Content Command from the File Utilities BizApp to the Chain.

  • Connect the GET - Headcount in XML Format Command to the List File Content Command.

  • Configure the Command with the following:

Name

List File Content - Preview XML

File Name

Response Output from GET - Headcount in XML Format

Preview Lines

25

Offset

0

  • Save the Command.

  • Publish, Execute, and Run the Chain.

  • Review the Log tab of the List File Content - Preview XML Command node and take note of the envelope information.

ℹ️ The envelope in this example is employee_data and employee.


Step 5: Convert the XML File to Delimited

Use the XML BizApp to convert the XML data to a CSV format allowing us to leverage OneCloud Commands later in the Chain that expect data in a delimited format.

  • Add an Element List to CSV Command from the XML BizApp to the Chain.

  • Connect the GET - Headcount in XML Format Command to the Element List to CSV Command.

  • In the Input File parameter, select the Response Output from the GET - Headcount in XML Format Command.

  • Leave the Input Text parameter blank.

  • In the Path to root parameter, enter the below envelope information:

/employee_data/employee
  • Check the Preview Result parameter.

  • In the Columns section, click the Add button 12 times.

    • For each of the columns added, enter the name of the field from the data record previewed in the previous step in both the Column Name and XPath fields.

      • Example: emp_id

      • ⚠️ The XPath field is case-sensitive and must match the casing of the envelope.

  • In the Multi-value Delimiter parameter, enter a pipe (|).

  • Save the Command

  • Publish, Execute, and Run the Chain.

  • Review the Log tab of the Element List to CSV - All Employees Command node and confirm a list of employees is displayed.

⚠️ Do not skip the execution and results review of this step as a Copy of this Command will be created in the Chain and any errors would subsequently need to be corrected across multiple instances of the Command.


Step 6: Change the Converted XML from Tab-delimited to Comma-delimited

Use the Tabular Transformation BizApp to convert the output generated from the XML conversion from a tab-delimited format to a comma-delimited format.

  • Add a Change Delimiter Command from the Tabular Transformation BizApp to the Chain.

  • Connect the Element List to CSV - All Employees Command to the Change Delimiter Command.

  • Configure the Command with the following:

Name

Change Delimiter - All Employees

Input File

Converted File Output from the Element List to CSV - All Employees Command

Input Delimiter

\t

Output Delimiter

,

  • Save the Command.


Step 7: Compare the CSV and XML Datasets to Identify Differences

Use Advanced Query to systematically identify differences between the XML and native CSV data sets. In this example, identify any records in the XML dataset that are different or missing from the CSV dataset. Any additional records found in the CSV dataset that are not in the XML dataset will not be identified by this exercise.

  • Add an Advanced Query Command from the Tabular Transformation BizApp to the Chain.

  • Connect the Change Delimiter - All Employees Command to the Advanced Query Command.

  • Name the Command: Advanced Query - All Employees.

  • In the Tables section, click the Add button to add a second tab.

    • For the first table, specify the CSV Result Output from the Change Delimiter - All Employees Command. Specify XML as the Table Name.

    • For the second table, specify the Response Output from the GET - Headcount in CSV Format Command. Specify CSV as the Table Name.

  • In the Query parameter, enter the following:

Select * from XML EXCEPT Select * from CSV
  • In the Input Delimiter and Output Delimiter fields, specify Comma.

  • Check the Preview Results option.

  • Publish, Execute, and Run the Chain.

  • Review the Log tab of the Advanced Query - All Employees Command node and confirm the employee IDs match the below for the records returned by the query.

Employee ID
01-6875791
10-4199621
38-3932553
63-0417180
78-3600453
85-6818050
97-2828467


Step 8: Add a Command Group

Add a Command Group to the Chain to better organize Commands. While this step is required in terms of this exercise, this is not a technical requirement for the Chain. This step is merely to illustrate the ability to organize nodes of a Chain.

  • Add a Command Group to the Chain.

  • Connect the GET - Headcount in XML Format Command to the In section of the Command Group.

  • Click the Command Group and then select the dropper icon to change the Command color to red.

  • Name the Group: Active Employees.


Step 9: Filter CSV Data for Active Employees Only

Use a Smart Filter to keep only the employee records where the employee is active. When defining the filter, we assume that the terminated (termed) field being blank indicates that an employee is active.

  • Add a Smart Filter Rows Command from the Tabular Transformation BizApp to the Chain.

  • Connect the Group Start from the Command Group to the Smart Filter Command.

  • Edit the Command.

    • Name the Command: Smart Filter Rows - Active Only, CSV Data.

    • In the Input file parameter, select the Response Output from the GET - Headcount in CSV Format Command.

    • Leave the Output file parameter blank.

    • Specify Comma as the Delimiter.

    • In the Filters section,

      • click the Add button on the Text filters.

      • In the Column name parameter, enter termed.

      • Check the Case Insensitive option.

      • From the Condition dropdown, select Equals.

      • In the Compare Text parameter, enter true.

      • Check the Not option.

      • Leave the Trim checkbox unchecked. This filter will identify any record where the termed field does not contain a value of true (i.e., false or blank).

    • Leave the Preview Result option Checked.

    • The Inverse option allows the Smart Filter to either keep (checked) or remove (unchecked) all rows that match the filter criteria. In this exercise, we want to keep all of the rows where the termed field has a value of true or blank. To achieve this, we Check the Inverse option.

  • Save the Command.


Step 10: Filter XML Data While Converting to Delimited

In this step, we filter the XML data while converting to a delimited format to identify only active employees. This dataset will subsequently be compared with the filtered CSV dataset to identify differences.

  • Copy the Element List to CSV - All Employees Command.

  • Connect the Group Start from the Command Group to the Element List to CSV - All Employees (Copy) Command.

  • Edit the Command.

  • Name the Command: Element List to CSV - Active Employees.

  • In the Path to root parameter, enter the below. The format below filters based on the termed field having a value not equal to true.

/employee_data/employee[termed!='true']
  • Save the Command.


Step 11: Change the Filtered XML Data to Comma-delimited

As with the full employee roster, the filtered active employee roster that has been converted from XML to tab-delimited needs to be converted to comma-delimited.

  • Copy the Change Delimiter - All Employees Command.

  • Connect the Element List to CSV - Active Employees Command to the Element Change Delimiter - All Employees (Copy) Command.

  • Edit the Command.

  • Name the Command: Change Delimiter - Active Employees.

  • Modify the Input file parameter to use the Converted File Output from the Element List to CSV - Active Employees Command.

  • Save the Command.


Step 12: Check for Differences with Active Employees

As with the full employee roster, the filtered active employee roster that has been converted from XML to tab-delimited needs to be converted to comma-delimited.

  • Copy the Advanced Query - All Employees Command.

  • Connect the Out of the Command Group to the Advanced Query - All Employees (Copy) Command.

  • Edit the Command.

  • Name the Command: Advanced Query - Active Employees.

  • In the Tables section, modify the File parameter for both tables

Table

File

XML

CSV Result Output from the Change Delimiter - Active Employees Command

CSV

Smart Filter Row Output from the Smart Filter Rows - Active Only, CSV Data Command

  • Save the Command.


Step 13: Test the Exercise

Now that the Chain is complete, test the result.

  • Publish the Chain.

  • Click Execute and then select Run Chain.

  • Once the Chain has completed, click the Advanced Query - Active Employees node.

    • On the Outputs tab, confirm that 4 records were output.

    • Click the Logs tab and confirm the employee IDs match the below for the records returned by the query.

Employee ID
01-6875791
63-0417180
78-3600453
85-6818050


📚 Topics covered in this exercise:

Command Groups

File Utilities

HTTP

Tabular Transformation

XML


Did this answer your question?