Oracle Warehouse Builder (OWB) Weblog

I've been meaning to post details on leveraging XDB from OWB for some time, so finally here we go. This note is intended to provide an overview of how to build transformations of XML related objects within OWB 10gR2, it is intended to give a brief overview of what can be achieved and by no means is it a definitive list of capabilities (see the XML DB Developer�s Guide for a complete reference). It cross-references a few examples constructed in the XML DB Developer�s Guide (Part Number B14259-02). From this the XML expert utilities were constructed for generating components based upon common transformation patterns (consuming and generating XML), you can find those on the exchange along with the demos (download here).

With the addition of many Oracle data types/object types in the OWB 10gR2 release and additional operators in mapping, the world of XML DB is exposed within OWB. So what can be done? The following XDB functionality is possible with OWB 10gR2.

• Use an XML source to create Relational targets
• Use Relational sources to create XML targets
• Transform XML source to XML targets
• and probably much more besides
  

To build XML related maps in OWB involves understanding how the various XML SQL operators can be incorporated in OWB mapping operators, after this is grasped there are endless possibilities.

When constructing the maps there are a couple of rules of thumb for the production 10gR2 release.

• Always generate set based code (bug 5049926), and
• Use invoker rights for the mapping (to enable access to the XDBADMIN role from the generated PLSQL package).

Let's build this up example by example....

XML to Relational (XDB Example 3.27)

This example demonstrates how to access the text nodes for each Description element in the PurchaseOrder document. The OWB mapping designed in the following section will construct the following SQL as a source to a target table;

Filtering with existsNode. The where clause can be constructed using the OWB Filter operator, the filter expression is defined in the OWB Expression Builder using the XML SQL operator �existsNode�;


The correlated join between the results of the SQL function table and the row operated on by the SQL function extract is shown below and is a mixture of an expression with the extract operator and the varray iterator operator. The table that provides input to extract must appear before the table expression in the FROM list - OWB generates this code, XMLSEQUENCE returns a varray of XMLType (XMLTypeSequence � this is seeded in OWB as SYS.XMLSequenceType). The correlated join ensures a one-to-many (1:N) relationship between the rows generated by the SQL function table and the row containing the value that is processed by extract;

SELECT extractValue(value(des), '/Description')
  FROM purchaseorder p,
       table(XMLSequence(
               extract(p.OBJECT_VALUE,
                       '/PurchaseOrder/LineItems/LineItem/Description'))) des
  WHERE existsNode(p.OBJECT_VALUE,
                   '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]')
        = 1;



The varray iterator named ITERATE_OVER_TABLE_XMLTYPE is for iterating over the varray (XMLSequenceType) generated by the XMLSequence SQL operator;




The extraction of the description attribute is performed using the expression operator in OWB. Many output expressions can be defined, for example below the attribute DESCRIPTION has been defined with the expression extractValue(INGRP1.VALUE, �/Description�) as below;


Additional attributes can be added to the expression operator�s output group if more information is needed from the XML. For loading large documents in 10g the database introduced API
DBMS_XMLSTORE , which is a good option since it is implemented in the
Oracle kernel in C so will give good performance, but does have the
caveat that the document must be the same shape as the implicit file to
table schema mapping (the mapping between the XML tags and relational
columns names is implicit in the engine, see the Oracle XML
documentation for more details, the map between SQL to XML and XML to
SQL can be found here). (See note 358553.1 for information on
constructing XSL for complex tables).

Loading from non-staged source

It is also possible with OWB to pass the XML document for the ETL mapping as an input parameter to the mapping (or construct it in an expression). You should be wary though you may get the 'ORA-31186 too many nodes' error since for in-memory XMLTypes have a limit in 10gR2 for XPath result sizes of 64k (the 64k limit is the number of nodes matched by the xpath not the total no. of nodes in the document). You can resolve this by loading the XMLType into an XMLTYPE table, registering the XML schema will also be valuable. This approach may be favorable if there is a file input or a real time consumer which takes the XML document from a queue and executes the OWB generated PLSQL mapping package passing the XML document as input parameter (commit control attribute also can be configured). The real time process could be an Oracle Streams apply process for example.
For example to execute the map from a PLSQL block, the XML can be passed as an input parameter to the map execution as follows;

Relational to XML (XDB Example 3.44)

Using SQL/XML Functions to Generate XML. This query generates an XML document that contains information from the tables departments, locations, countries, employees, and jobs:
SELECT XMLElement(
         "Department",
         XMLAttributes(d.Department_id AS "DepartmentId"),
         XMLForest(d.department_name AS "Name"),
         XMLElement(
           "Location",
           XMLForest(street_address AS "Address",
                     city AS "City",
                     state_province AS "State",
                     postal_code AS "Zip",
                     country_name AS "Country")),
           XMLElement(
             "EmployeeList",
             (SELECT XMLAgg(
                       XMLElement(
                         "Employee",
                         XMLAttributes(e.employee_id AS "employeeNumber"),
                         XMLForest(
                           e.first_name AS "FirstName",
                           e.last_name AS "LastName",
                           e.email AS "EmailAddress",
                           e.phone_number AS "PHONE_NUMBER",
                           e.hire_date AS "StartDate",
                           j.job_title AS "JobTitle",
                           e.salary AS "Salary",
                           m.first_name || ' ' || m.last_name AS "Manager"),
                         XMLElement("Commission", e.commission_pct)))
                FROM hr.employees e, hr.employees m, hr.jobs j
                WHERE e.department_id = d.department_id
                  AND j.job_id = e.job_id
                  AND m.employee_id = e.manager_id)))
  AS XML
  FROM hr.departments d, hr.countries c, hr.locations l
  WHERE department_name = 'Executive'
    AND d.location_id = l.location_id
    AND l.country_id  = c.country_id;

The joiner operator relates the 3 HR source tables and defines the join condition to use;

Create a forest
of XML elements from the relational items:

Define the XMLElement for �EmployeeList� that is created from an inline query containing calls to XMLElement, XMLAttributes and XMLForest.



Collate the expression together to create a Department element.



That's a quick run through, with some creative use of OWB map operators you can now see how to leverage the XML SQL functions. There are a few experts for consuming and generating XML including useful utility functions for saving XML to the filesytem. On the blog I've posted a few entries for processing files from a directory which are all useful pieces in the puzzle. The XML generation expert will allow you to pick a bunch of tables and build a document from them, you define the join criteria between the tables and the XML element and attribute names and the XML expert will generate the components to generate the XML. The consuming expert is based on an XML schema which you supply and it will generate a number of pluggable mappings for the components in the schema. Remember they are experts so don't be afraid to open the box and change the template code, you can do all sorts including all kinds of complex XQueries.