Convert Oracle XMLTYPE to Oracle tables

January 27, 2018

In this example we will read XML data from a table with an XMLTYPE column in the Oracle database and convert the XML to tables in the same Oracle database.
We will use Flexter to extract the data to and from the same database in this example.
We need to give Flexter table and column name that contains the XML data.
JDBC input sources require us to include the input path with a jdbc: protocol.
[flexter_banner]

Example:

This command will create one connection and against the source database and retrieve the data sequentially.
To read in parallel with multiple threads (available on a single machine as well), a sequential ID column name and its range must be provided. The options which we are using are given in the below table

We have a table “pnr_xml_msg” which has xml content in the column “source_data”. The Primary Key is seq_nbr. Let’s take a look at the table and the xml content that will parse with Flexter.
We can see that the table contains the “source_data” column of XMLTYPE. This column contains the xml data.
[flexter_button]
 

We then try to generate a logical schema based on the xml content in the column

If the flexter command works fine, we can proceed with generating the logical schema for the xml content in the table

Here, we are using -U and -P parameter as the input username and password to the jdbc URI

Now as we have generated the logical schema we can extract the data to the output jdbc connection. We can use the following options to generate the output as desired

Initially we use the the primary key to partition the data we are extracting using the -I option, We can also parse and extract the entire XML content if we skip the -I option

In the above output we can see that the Flexter is writing the tables in the target db as shown in the output. The XML content parsed can be seen in the form of tables in the given jdbc path.
We can see that the parsed tables are created in the target db as per the jdbc path.

We can run basic queries to see if the data is parsed correctly

[faq_button]