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.
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:
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.
Explore possibilities of KeepTool.
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:
On Hora’s SQL page you can run the following code to create these PL/SQL types:
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.
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.
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.
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
Performing the XML import, is now a two-step process:
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:
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.