Apex: XLIFF Translator, Step 3.1

As posted last week, we started developing our own utility to help us in step 3 of the translation process of an Oracle Application Express application.

The global idea is to create an Apex-frontend on top of an XLIFF-file.

XLIFF is an acronym and stands for “XML Localization Interchange File Format“. You can read all about is on following url.

We decided to take a pragmatic approach. We started with the XML-file as it is being exported by Apex into the XML translation file.

The following examples show some lines of the generated XML:

You notice that each translatable element has a source and target element, encapsulated in the tag, identified by an id-attribute.

So it should be quite easy to upload that file in an XMLTYPE-column and transform the xml-structure to a relational table by using one of the XML features of the Oracle Db.

Following SELECT gives a part of the query we used: t.xliff refers to the column storing the complete XLIFF-file in our custom table with the name apex_translation_files:

SELECT extractValue(value(xx), '/trans-unit/@id') id,
extractValue(value(xx), '/trans-unit/source') source,
extractValue(value(xx), '/trans-unit/target') target
FROM apex_translation_files t,
TABLE(XMLSequence(Extract(t.xliff, '/xliff/file/body/trans-unit'))) xx

In the XML-excerpt, you can also see that the same source-value can appear multiple times, but that there is a small difference in the value of the id-attribute.

It is clear that this id should have a specific meaning for the translation process, but we couldn’t find any documentation on this topic.

The id-value has 4-data fragments, separated by a ‘-‘: e.g. S-2-11453021424239212-144

The first element is always a “S”.
The third element contains typical an unique identifier of a record.
The fourth element is obviously the APP_ID of an apex application.

The second element intrigued me. It was a reduced set of possible values; that must be some typology of the text-to-be-translated.

We dived into the flows_020200 schema and noticed two important tables related to the translation process:

  • wwv_flow_translatable_text$: this table contains all data coming from step 2 in the translation process “Seed the Translable Text” and we discovered that the 3th element of our id-attribute corresponds with the translate_from_id -column

  • wwv_flow_translatable_cols$: this table contains all apex-elements that can be translated. The id-column of this table corresponds with the second element of the id-attribute in the xliff-file.

Putting all those elements together, we were able to built the first step in our utility: Upload and Seed the original XLIFF-file…

… and our previous XML excerpt presented in a relational way gives following result, where the value of the id-attribute “S-2-xyz” is referring to “the Navigation Bar Icon text” or “Icon Image Alt”

(will be continued …)