Last change Oct 2025
Hello again! In this two-part series we’ll show how KeepTool works together with Oracle XML DB to import and export XML data seamlessly.
Oracle’s native XML support lets you store, validate, and query XML documents inside the database. You can register XML schemas, map PL/SQL types, and expose relational data as XML or ingest XML into relational tables.
In this first article, we introduce the fundamentals and build a working data import interface for the HR.EMPLOYEES table. The next article will cover data export and master/detail hierarchies such as HR.DEPARTMENTS → HR.LOCATIONS.
We focus on essentials — you can extend the logic later to suit your specific needs.
Imagine a large company with a central HR database. Regional sites maintain local replicas of employee data, which need to be updated from headquarters via XML:
HQ exports new or changed employee data as XML (e.g. nightly or per update).
The regional site receives that XML and imports it—adding new records or updating existing ones.
In Part 1, we show how to import XML into the local HR schema. In Part 2, we’ll show how to export data back to XML, including handling master/detail relationships.
Explore possibilities of KeepTool.
We’ll work with the HR.EMPLOYEES table, so the first step is to define PL/SQL types to mirror its structure:
EMPLOYEE_XML_ITEM: object type whose attributes map to columns in EMPLOYEES
EMPLOYEES_XML_LIST: a collection (TABLE) of EMPLOYEE_XML_ITEM
EMPLOYEES_XML_ELEM: wrapper object containing one EMPLOYEES_XML_LIST — needed so Oracle XML DB can map to an XML document root
Run the following code in Hora’s SQL interface:
Once created, you can view them under Hora’s PL/SQL Types page (All types tab):
Hora also displays any warnings or database errors automatically.
With your PL/SQL types in place, you can generate an XML Schema (XSD) and register it in the database:
SELECT dbms_xmlschema.generateschema(
'HR',
'EMPLOYEES_XML_ELEM',
'EMPLOYEES_LIST'
) FROM DUAL;
Use that result in an anonymous PL/SQL block to register the schema:
begin
dbms_xmlschema.registerschema(
'EMPLOYEES.XSD',
dbms_xmlschema.generateschema(
schemaname => 'HR',
typename => 'EMPLOYEES_XML_ELEM',
elementname => 'EMPLOYEES_LIST'),
gentypes => FALSE,
genbean => FALSE,
gentables => FALSE);
end;
After running this, the XSD is registered in Oracle XML DB. In Hora’s XML page you’ll see:
The XSD listing
We wrap these steps in a PL/SQL package called XML_LOGIC.
So later you can simply call
XML_LOGIC.register_xsd('EMPLOYEES').The package also saves the XSD as a file in a predefined Oracle directory, which you can hand to upstream systems so they know the format expected.
To bring in XML documents, we define an interface table named XML_EMPLOYEES_TRANSFER. External systems insert their XML payloads into this table:
Key columns:
ID: primary key, set by a trigger using a database sequence
XML_DATA: CLOB containing the full XML document
Metadata columns: timestamps, stats, error logs
On top of this, we build a relational view XML_EMPLOYEES_TRANS_DATA_V that projects XML data into relational columns:
This view uses Oracle XML table or other XML-to-relational techniques to split the XML into record columns. (See references like [Oratable XMLTable conversion] for the technology.)
Once a row is inserted into XML_EMPLOYEES_TRANSFER with XML_DATA, you can immediately inspect the relational result via that view.
Time to implement the core logic in a PL/SQL package XML_INTERFACE. It contains a procedure (e.g. ProcessEmployeesImport) that does:
Loop over rows in XML_EMPLOYEES_TRANS_DATA_V for a given transfer ID
For each record:
If EMPLOYEE_ID already exists:
If record is locked, skip
Otherwise, update existing employee
Else, insert new employee
After processing all, update statistics and log any errors
….
Thus the XML import occurs in two steps:
XML_EMPLOYEES_TRANSFERCallXML_INTERFACE.ProcessEmployeesImport(transfer_id)
Once the import is completed, you can view the results directly in Hora:
After running the procedure, Hora’s Data Content view displays the imported record instantly, confirming that the XML data was processed successfully.
The transfer table statistics have been refreshed, summarizing the outcome of the XML import at a glance.
The previously defined view XML_EMPLOYEES_TRANS_DATA_V presents XML data together with its related transfer information as a single, easy-to-read relational view.
Your XML-based import interface is now functional! You can download our sample and use it as a starting template.
Many projects have adopted this approach — developers report significant productivity gains by using KeepTool’s integrated XML DB support.
In the next installment, we’ll walk through how to build a complementary data export interface, handling master/detail relations and XML output.
➡️ Try KeepTool today!
KeepTool’s Oracle tools are designed for developers, DBAs, and application support teams. Driven by user feedback over 20+ years, we continually improve our features.