Converting XML documents from an Oracle CLOB to a relational format

August 31, 2020

In this post we will show you how easy it is to convert XML data from Oracle CLOB to a relational format. We will be using Enterprise Flexter which ships with a feature that enables us to use Oracle CLOB as source data and to convert it.

Why not give the free online version of our XML converter Flexter a try. See for yourself what XML conversion software can do for you.

Oracle CLOB

CLOB stands for “Character Large Object”. CLOB stores large amounts of character data, e.g. in the form of XML documents. The CLOB data type is similar to a BLOB, but includes character encoding, which defines a character set and the way each character is represented.
Flexter can also convert XML documents that are stored in an Oracle XMLType to relational tables.

Inserting XML data to Oracle CLOB

First we populate a table with XML documents.
We create the table with two columns, an ID column and an XML_COL column where we will insert XML files.


We can now insert the XML documents to the CLOB column. We will be using our standard soundlib XML sample files files for the conversion.
Inserting XML can be done with this simple SQL Query.

Converting Oracle CLOB data with Enterprise Flexter

Flexter allows us to convert Oracle CLOB with just using two additional switches (options). To specify which table will be converted we will use the “-T” switch, and to specify the column we use the “-C” switch.
Converting XML/JSON data can be performed in a couple of simple steps

Step 1 – Define Source data, collect Statistics (Information such as data type and relationships) and Data Flow (Mapping data points in the source to the data points in target schema)
Step 2 – Define Source Schema (XSD)
Step 3 – Convert the XML documents

Step 1 – Define Source, collect statistics and create data flow

In this step we will read XML data, collect metadata, and create a data flow (a logical target schema and the mappings from source to target). The -T switch tells Flexter which table to read from. The -C switch provides the column name containing the XML documents.

Example of output

Step 2 – Define Source Schema (XSD)

In the next step we will define the source schema.

Example of output

Step 3 – Convert data

In the final step we will convert the data to the output folder which we created. We also define the output format, e.g. TSV, ORC, Parquet, Avro files, or a relational database such as Oracle.In our example, we take XML data stored in an Oracle CLOB (Character Large Object) column and convert it into TSV format, which is a type of text file where the values are separated by tabs. Additionally, Flexter offers an alternative option: instead of converting the XML data into TSV files, you can choose to convert it directly into Oracle tables.

Example of output:

Er Diagram

Conclusion.

With Flexter it is very easy and simple to convert XML documents stored in an Oracle CLOB data type to a relational format. You can query your data in a matter of minutes with SQL.

Further Reading

XML conversion
Deep dive on XML converters, XML conversion tools and XML conversion projects
Converting XML and JSON to a Data Lake
How to Insert XML Data into SQL Table?
The Ultimate Guide to XML Mapping in 2024
Optimisation algorithms for converting XML and JSON to a relational format
Re-use algorithms for converting XML and JSON to a relational format

Flexter
Flexter data sheet
Free online version of Flexter