Read XML

XML data sources ..

Steel Wheels has some data sources in XML format. This guided demonstration illustrates the 3 data source options for retrieving XML data.

In this Lab, you will configure the following step:

• Get data from XML

• File

• URL

• Stream

To create a new transformation

1. In Spoon, click File > New > Transformation:

Any one of these actions opens a new Transformation tab for you to begin designing your transformation.

• By clicking New, then Transformation

• By using the CTRL-N hot key

1. XML File

In this workflow, an XML file is parsed via an X-path to retrieve the required dataset.

Get data from XML - file

orders.xml

1.1 Get data from XML

This step provides the ability to read data from any type of XML file using XPath specifications.

1. Drag the ‘Get data from XML’ step onto the canvas.

2. Double-click on the step, and configure the following properties:

XML - file

1. Click on the Content tab, and configure the following properties:

Xpath

1. Click on the Fields tab, and then on the ‘Get Fields’ button.

XML - fields

1. Click OK.

1.2 Dummy

The Dummy step does not do anything. Its primary function is to be a placeholder for testing purposes. For example, to have a transformation, you need at least two steps connected to each other.

1. Drag a ‘Dummy’ step onto the canvas.

2. Create a hop from the ‘Get data from XML’ step.

3. Close the Step.

1.3 RUN

Remember to disable the hops on the second workflow.

1. Click the Run button in the Canvas Toolbar.

2. Preview the data.

Preview

2. XML URI

In this workflow, a URL to an XML data source is parsed via an X-path to retrieve the required dataset.

Get XML - URL

https://www.w3schools.com/xml/plant_catalog.xml

2.1 Genrate rows - Pass URL

In this workshop we're going to pass the URL in a datastream field.

Ensure you have copied the URL into the clipboard for X path.

Generate rows outputs a specified number of rows. By default, the rows are empty; however, they can contain several static fields. This step is used primarily for testing purposes. It may be useful for generating a fixed number of rows, for example, you want exactly 12 rows for 12 months.

1. Drag the ‘Generate Rows’ step onto the canvas.

2. Double-click on the step, and configure the following properties:

Pass URL in datastream field

2.2 Get data from XML - Read URL

The dataset is being parsed from a stream field xmlUrl that’s being passed on from the ‘Pass URL’ step.

1. Drag the ‘Get Data from XML’ step onto the canvas.

2. Create a hop from the ‘Pass URL’ step.

3. Double-click on the step, and configure the following properties:

Read Url

1. Click on the ‘Content’ tab and configure the following properties:

Select X path

1. Click on the ‘Fields’ tab and configure the following properties:

Configure fields

1. Click on the ‘Get Fields’ button.

2.3 Dummy

The Dummy step does not do anything. Its primary function is to be a placeholder for testing purposes. For example, to have a transformation, you need at least two steps connected to each other.

1. Drag a ‘Dummy’ step onto the canvas.

2. Create a hop from the ‘Get data from XML’ step.

3. Close the Step.

2.4 RUN

Remember to enable the hops and disable the hop in workflow 1.

The workflow will fail .. do you know why.?

1. Click the Run button in the Canvas Toolbar

Invalid data type

1. Check the logs.

Logs

Looks like Zone data type is alpha numeric = string instead of integer.

1. Change Zone data type to string and re-run transformation.

2. Click on the Dummy step and Preview data.

Preview Plant Catalog