Import XML Data with KeepTool and Oracle XML DB

Updated October 2025

---

Introduction

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.

---

A sample use case

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.

---

Creating PL/SQL Types for XML

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.

---

Creating the XSD schema

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

• A mapping view between XML elements and your PL/SQL types

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.

---

Interface table for data import

To bring in XML documents, we define an interface table named XML_EMPLOYEES_TRANSFER. External systems insert their XML payloads into this table:

Most important 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.

---

Data import logic

Time to implement the core logic in a PL/SQL package XML_INTERFACE. It contains a procedure (e.g. ProcessEmployeesImport) that does:

1. Loop over rows in XML_EMPLOYEES_TRANS_DATA_V for a given transfer ID

2. For each record:

   • If EMPLOYEE_ID already exists:

     • If record is locked, skip

     • Otherwise, update existing employee

   • Else, insert new employee

3. After processing all, update statistics and log any errors

….

Thus the XML import occurs in two steps:

• Insert the XML payload into XML_EMPLOYEES_TRANSFER

• CallXML_INTERFACE.ProcessEmployeesImport(transfer_id)

---

Displaying the imported data

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.

---

Conclusion

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.

---