Converting files between flat-file, PDF, EDI, XML and other formats no longer has to be a complex manual programming chore. GoXML lets you create mappings visually to simplify all your transformation tasks.

by Michael Sanchez

Jan 11, 2006

Page 2 of 3

WEBINAR:

On-Demand

Full Text Search: The Key to Better Natural Language Queries for NoSQL in Node.js

Creating a Transformation with GoXML
Start by opening the GoXML Transform Designer. The first time you open the application, you may only see the Source pane and the Target pane. If this is the case, open the Configuration pane by selecting View from the menu bar and checking the Configuration Pane option. Your screen should now look similar to Figure 3.

Figure 3. GoXML Transformation Designer: After opting to view the Configuration pane from the View menu, your screen should show three panes: Configuration, Source, and Target.

In these examples, you won't need to make any custom configuration changes, but if you prefer, you can update the Preference tab in the Configuration pane.

In the Source pane, select the Configuration tab, click the Data Format dropdown field, and select CSV as the source data type. The delimiter field should default to a comma. In the structure field, you need to enter the path to your CSV file or click on the ellipses to browse to the file (see Figure 4). When you've done this, click the Load button. GoXML will read in the file.

Figure 4. Selecting a Source: When the source file is in CSV format, select CSV from the Data Format dropdown, make sure the Delimiter field is set to a comma, and provide the path to the source file in the Structure field.

Next, select the Mapping tab. Here you will see the headers for your CSV file. If you click on the Structure tab, it will show you the actual data, or "structure" of your document. You'll use the headers displayed in the Mapping tab to create our relationships with the target data.

In the Target pane, click on the Configuration tab, open the Data Format list box and select XML. You could click on the "New" button in the XML Formatter frame to create a new XML document, but because you already have a template, just enter the path to the target file, or click on the ellipses to select the mailingList.xml file. Now you can check the Load Structure textbox and select the Mapping tab.
Here you will see a tree-view representation of your XML nodes. Now we can create the relationships between our source data and target data.

The first challenge is that the CSV file contains the user's full name in one field while the target XML file has separate nodes for first and last names. Obviously, you're going to have to split up the single CSV field with code. To handle situations like this, GoXML lets you map target nodes to a function rather than directly to a source field.

Figure 5. Completed Mapping: After mapping all the sample CSV fields to their corresponding elements in the XML target format, your mapping surface should look like this.

To do that, right-click on the firstName element and select "Map to Function," which opens the function editor. In the function pane, expand the STRING_CHARACTER option and then scroll down to the STRTOKEN function. The strtoken function takes the source data, and splits it based on a specified delimiter, creating a list of separated "tokens," returning the token with the "instance" (index) you declare in the second argument. Drag-and-drop the STRTOKEN function to the Function Instance pane. This displays the parameters required by the function. The first parameter is the source data, so drag the name field from the Source pane to the first parameter of the function instance. Next, double-click the second parameter and enter 1, meaning that the function should return the first tokenthe first name. Finally, double-click the third parameter and enter a space for the delimiter, declaring that the tokens are space delimited. Click OK, and you have your first name mapping.

At any time, you can hover over the elements and see the info about that element such as an XPath query for the selected element, a description of the mapping, such as a function name in this case, and the node type.
You follow the same process to map the lastName, except that you change the instance from 1 to 2, causing the function to return the second token in the returned string. Here's a convenient feature that can save you some time when defining repetitive mappings. Right-clicking on the firstName element and selecting Copy Mapping, then right-clicking the lastName element and selecting Paste Mapping copies the function defined for firstName to the lastName element. All you have to do to modify the instance from 1 to 2 is right-click on the lastName element, select "Map to Function," and modify the copied function.

Figure 6. Validating Results: After creating a successful transform, you can validate the results by expanding nodes in the Result pane.

To finish the mapping, drag the email element form the source pane to the email element in the target pane, and do the same for the optIn element. At this point, your mapping should look like Figure 5.
Validating Mappings
To validate the data you have just mapped, click on the Structure tab in the source pane and the Result tab in the target pane. This causes GoXML to transform the data based on your mappings and functions. If everything works properly, you'll get a "Transform successful" message. At this point, you can expand the nodes in the Result pane (see Figure 6) to validate the data and mappings against the data in the source structure.

When you're happy with the results, click the Save button on the main toolbar to save the mappings and configuration for future transformations and then click the Save button in the target pane to save the target results as mailingListFinal.xml. At this point in the scenario you have both an XML document of subscribers suitable for the subscriber import tool, and a mapping that will work with future incoming CSV subscriber files.