Oracle 7.3 - Oracle 23c 20+ years of experience Version (Released: )

Oracle XML DB (Part 1) – Creating a data import interface

23 Jan 2023

Introduction

Hello again. In a new series of two articles, we will explain how you can use KeepTool 15 in conjunction with Oracle XML DB.  As you may remember, Oracle’s out-of-the-box XML solution allows storing and retrieving of XML documents within the database. You can register XML schemas and perform queries on object-relational data structures. This first blog entry introduces the fundamentals and ends up with a working data import interface for the HR.EMPLOYEES table.

The next blog we will follow up with creating a data export interface. We will show you how to technically handle a master/detail relation such as HR.DEPARTMENTS and HR.LOCATIONS.
Overall, we will strictly focus on the essentials. There is still plenty space to complete the logic to add more of your own needs.

A sample use case

Let’s consider a regional location of a large enterprise. Locally, they keep their own copy of the human resources (HR) database. Employee data are maintained centrally at the headquarters. The local site gets updates of new and changed employee data via an XML interface from the main site:

  • The main database at the headquarters exports new and changed employee data as XML structure.
    That occurs periodically; for example nightly, or on each update of an EMPLOYEES data record.
    There are several ways the XML structure can be transferred to the local site, for example via Web service or using remote database access.
  • The local site receives the XML data structure and imports the content into its own database.
    New employee data are inserted and existing employees are updated locally.

In the first part of our series we will show how an XML structure can be imported.  EMPLOYEES data in the local HR database are either inserted or updated, respectively. The second blog will follow up with describing data export technology.

See how it works!

Explore possibilities of KeepTool.

i,g

Creating PL/SQL types

Our design approach is to import data into the EMPLOYEES table of the human resources (HR) demo schema. The first step is to define a couple of PL/SQL types that allow Oracle XML DB to create a XSD schema definition.
More specifically, our XML approach needs 3 PL/SQL types that depend on each other:

  • EMPLOYEE_XML_ITEM
    is an object type that contains members, each matching a column of the EMPLOYEES table structure.
  • EMPLOYEES_XML_LIST
    is a collection type and represents one or more EMPLOYEE_XML_ITEM elements.
  • EMPLOYEES_XML_ELEM
    is another object type that contains one single element EMPLOYEES_XML_LIST.
    We need it to encapsulate the collection type into an object type.

On Hora’s SQL page you can run the following code to create these PL/SQL types:

CREATE OR REPLACE TYPE EMPLOYEE_XML_ITEM AS OBJECT ( EMPLOYEE_ID NUMBER (6,0), FIRST_NAME VARCHAR2 (20), LAST_NAME VARCHAR2 (25), EMAIL VARCHAR2 (25), PHONE_NUMBER VARCHAR2 (20), HIRE_DATE DATE, JOB_ID VARCHAR2 (10), SALARY NUMBER (8,2), COMMISSION_PCT NUMBER (2,2), MANAGER_ID NUMBER (6,0), DEPARTMENT_ID NUMBER (4,0) ) / CREATE OR REPLACE TYPE EMPLOYEES_XML_LIST AS TABLE OF EMPLOYEE_XML_ITEM / CREATE OR REPLACE TYPE EMPLOYEES_XML_ELEM AS OBJECT ( EMPLOYEE EMPLOYEES_XML_LIST ) /

Now you can find the types on Hora’s PL/SQL Types page. Select the ‘All types’ tab to see object types and collection types together as one list:

As you can see, Hora automatically displays warnings and error messages from the database server.

Creating the XSD schema

Now we use the dbms_xmlschema.generateschema procedure to create a XML schema description:

select dbms_xmlschema.generateschema( 'HR', 'EMPLOYEES_XML_ELEM', 'EMPLOYEES_LIST') from dual;

It returns the XSD as CLOB. The result can now be passed to the dbms_xmlschema.registerschema procedure:

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 anonymous block, the XSD has been registered in the database. As a result, you can see the XSD on Hora’s XML page:

On the next tab, Hora shows the mapping of XSD elements and PL/SQL types:

For your convenience, we put the described PL/SQL logic together in a PL/SQL package XML_LOGIC:

Using the package, all we currently need is calling the register_xsd method:

begin
  XML_LOGIC.register_xsd( 'EMPLOYEES');
end;

Besides the registration in the database, the procedure stores the XSD as a file in a predefined Oracle directory.
You can pass this XSD file to the software development team that provides XML data for import.

Interface table for data import

The XML_EMPLOYEES_TRANSFER table is used for managing the data import interface. The EMPLOYEES data provider will be advised to insert data into this table.
The following screenshot shows the table structure:

For each import, the external system adds one new record to the table. A trigger sets the value of primary key column ID using by fetching data from a sequence.
The XML_DATA column contains an XML representation of the import data. The remaining columns provide metadata such as time stamps, statistics and an error log. The latter shows information in case of import errors.

The XML_EMPLOYEES_TRANS_DATA_V is built on top of the import table. It selects data from the import table and includes all XML data in a relational form:

Here https://www.oratable.com/xmltable-convert-xml-to-relational-form/ you can find more information about the technology of converting XML data into a relational view. You can see the data content of the view later after we have inserted a record into the XML_EMPLOYEES_TRANSFER table.

Data import logic

Now that we have created all the basics, we can proceed with implementing the import logic. Therefore, we will create a PL/SQL package XML_INTERFACE.

….

It contains a single procedure for XML import. The implementation of the procedure comprises the following steps

  • Iterate the XML_EMPLOYEES_TRANS_DATA_V view for the given AXmlTranferID
  • If the EMPLOYEE.ID already exists,
    • Skip the record, id the EMPLOYEE currently is locked
    • Otherwise, update he EMPLOYEE
  • Else, insert the new EMPLOYEE
  • Finally, update statistics and error log.

Performing the XML import, is now a two-step process:

  • Insert a record into the XML_EMPLOYEES_TRANSFER table and store the XML in the XML_DATA column.
  • Pass the ID of the transfer record to the ProcessEmployeesImport procedure.

Displaying the imported data

After inserting the XML record, you can review the data in Hora:

After executing the procedure, Hora’s data content shows you the inserted record:

The transfer table’s statistics have been updated:

The view XML_EMPLOYEES_TRANS_DATA_V has been described already. It helps us to display the XML content together with the columns of the XML_EMPLOYEES_TRANSFER table as a relational data view:

Conclusion

Now our XML-based data import interface is working. You can download our example and use as a template to build your own data import interface.

Based on this concept, some more complex interface implementations already have been proved in several projects. Developers reported a significant improvement of productivity by using our tools.

In the next blog, we will follow up with building a data export interface.