Use kettle to convert an XML document into a data table structure

Use kettle to convert an XML document into a data table structure. In kettle, The Getdatafromxml step and XMLInputStream (StAX) Step read and parse the xml file. The Getdatafromxml step uses dom parsing, which consumes a lot of memory. When the file is large, it cannot

Use kettle to convert an XML document into a data table structure. In kettle, The Get data from xml step and the XML Input Stream (StAX) Step read and parse the xml file. The Get data from xml step is parsed using dom, which consumes a lot of memory. When the file is large, it cannot

Use kettle to convert an XML document into a data table structure
Read and parse the xml file in kettle's Get data from XML step and xml Input Stream (StAX) step. The Get data from xml step is parsed using dom, which consumes a lot of memory and is not desirable when the file is large. The XML Input Stream (StAX) Step parses large and complex files in different ways and can quickly load data. Therefore, we recommend that you use this step.
The following example shows how to use this step. The content of the source xml file is as follows:




8.5
9.8


12.2
9.4


11.1
7.2


The format of the data to be parsed into a data table is as follows:

Use the following steps to implement this function

Xmlinput stream (stax): loading xml documents in stream mode

Filter row: Remove irrelevant document elements

Switch/case step: Separate Level 1 (measurement) and level 2 (item)

Rowdenormaliser: converts multiple rows at Level 2 into one row (row column)

MergeJoin: Merge child elements to the first row (add column)

The example can beDownload here

Rowdenormaliser steps

The easiest way to understand the execution is to preview the execution results without steps (you can directly view the data stream in version 5.x) to display the execution principle of this step.

MergeJoin procedure

Merge two streams from different data sources. In fact, the join principle in SQL is the same, except that kettle is used for stream data, not table data. It is very important that the stream data should be sorted by the key (used in join). The first step in this column is sorted when the xml file is loaded, therefore, the "sort rows" step is not selected.