Leveraging XDB

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;
SELECT extractValue(value(des), '/Description')
  FROM purchaseorder p,
                       '/PurchaseOrder/LineItems/LineItem/Description'))) des
  WHERE existsNode(p.OBJECT_VALUE,

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';
SELECT extractValue(value(des), '/Description')
  FROM purchaseorder p,
                       '/PurchaseOrder/LineItems/LineItem/Description'))) des
  WHERE existsNode(p.OBJECT_VALUE,
        = 1;

XML Map1:

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,
                       '/PurchaseOrder/LineItems/LineItem/Description'))) des
  WHERE existsNode(p.OBJECT_VALUE,
        = 1;

XML Map2:

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

SELECT extractValue(value(des), '/Description')
  FROM purchaseorder p,
                       '/PurchaseOrder/LineItems/LineItem/Description'))) des
  WHERE existsNode(p.OBJECT_VALUE,
        = 1;

XML Map3:

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;

SELECT extractValue(value(des), '/Description')
FROM purchaseorder p,
                       '/PurchaseOrder/LineItems/LineItem/Description'))) des
  WHERE existsNode(p.OBJECT_VALUE,
        = 1;

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;
  xml XMLType;
  status VARCHAR2(4000);
  -- read the file
  xml := XMLType(bfilename('SUBDIR',
  -- execute the mapping

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:
         XMLAttributes(d.Department_id AS "DepartmentId"),
         XMLForest(d.department_name AS "Name"),
           XMLForest(street_address AS "Address",
                     city AS "City",
                     state_province AS "State",
                     postal_code AS "Zip",
                     country_name AS "Country")),
             (SELECT XMLAgg(
                         XMLAttributes(e.employee_id AS "employeeNumber"),
                           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)))
  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;
  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;

XML Map4:

Create a forest
of XML elements from the relational items:

XML Map5:

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

XML Map6:

Collate the expression together to create a Department element.

XML Map7:

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.


It's a shame that you couldn't demonstrate what 99.99999% of users would like to know. How to load an XML file into a relational (non XML) Table using OWB. XML has been around for more than 10 years and no one has figured out how to load auto load a file without hand coding...

Posted by davidb on January 22, 2008 at 02:16 PM PST #

Hi David

Did you see the expert below, it generates pluggable mapping components (based on an XSD) that you can then use to take your XML and insert into relational tables. The zip has some example illustrating consuming and generating XML, ideally this would simply be another operator in the map, but for now the expert generates these components which under the covers leverage the SQL XML functions.



Posted by David Allan on January 24, 2008 at 05:41 AM PST #

Hi David, This material is just awesome, thanks a lot!!! Do you think that is possible update it with the new features of Oracle 11gR2? Regards, Jorge

Posted by Jorge on April 18, 2010 at 10:30 PM PDT #

Hi Jorge With OWB 11gR2 as well as leveraging the XDB features you can also use the ODIEE XML JDBC driver which makes some of the XML handling significantly easier. What areas would you be interested in hearing about? Cheers David

Posted by David Allan on April 19, 2010 at 02:22 AM PDT #

Hi David, Actually I’m involve in a project where I’m using as data sources Oracle DB, Flat Files and MS SQL, I have to transform the data source and create XML documents based in XSD schemas and then load them to our Oracle XML DB which will work as a kind of Datamart, that means that I need to create indexes and all the optimization that Oracle XML offers. The Goal is doing all this job using OWB directly but I don’t know how well connected are OWB and XML DB in terms of load data performance and if is possible create the indexes directly (I’m planning to use binary storage). As you can see I’m seriously involved in this architecture and if you have some documentation or tips will be so helpful. Regards, Jorge

Posted by Jorge on April 19, 2010 at 05:06 AM PDT #

Hi Jorge So OWB does not do a good job of the data modeling aspect of XML oriented tables, it can capture that there are XMLType columns and there is some support for index creation for function based indexes, but the design of the tables is best done outside. For example if I define the table CREATE TABLE PO_SL_BIX_TABLE OF XMLType XMLType STORE AS BINARY XML; in a schema, importing it into OWB will reverse engineer the hidden column SYS_NC_ROWINFO$ into OWB, you can use this to insert data into the table etc. Where OWB can help is integrating the databases, files, MS SQL etc and allow you to use SQL XML functions to construct and store the XML in XDB. Cheers David

Posted by David Allan on April 19, 2010 at 07:21 AM PDT #

Hi David, Saved me a lot of time in research with that tip. I'm pretty sure that I'm going to bother you later again :) Thank you so much!!! Jorge

Posted by Jorge on April 20, 2010 at 12:33 AM PDT #

Hi David: work your examples from owb_xml_etl_utils.zip in 11gR1 too? I'm trying to start CREATE_XML_FROM_OBJECTS in OWB 11gR1 und one error appear: "Unknown class 'oracle.owb.jexpert.ShuttleObjects ....'" What can it be? thank you, Andrey

Posted by Andrey on April 25, 2010 at 11:17 PM PDT #

Hi Andrey In 11gR1 it was omitted (a bug) but you can download and copy it to owb/lib/ext from here. http://blogs.oracle.com/antonioRomero/gems/jexpert.jar You can read more about the UI extensions here; http://blogs.oracle.com/warehousebuilder/2007/08/ui_expert_extensions.html Cheers David

Posted by David Allan on April 26, 2010 at 12:05 AM PDT #

Hi David: Is it true, that for XML-Target-File and RAC option in OWB i must buy additional License for ODI EE? thank you, Andrey.

Posted by Andrey on April 26, 2010 at 07:09 PM PDT #

Hi Andrey Yes if you use OWB in a RAC requires ODIEE license and same for XML target file operator. Cheers David

Posted by David Allan on April 27, 2010 at 12:46 AM PDT #

Hi David, I'm back :) I want to know which configuration you made in order to use XML functions (XMLForest, XMLElement, etc) because I got the following error: PLS-00201: identifier 'XMLFOREST' must be declared I'm working with Oracle 11g R1 on windows. Thanks for your help. Jorge

Posted by jorge on April 28, 2010 at 05:47 AM PDT #

Hi Jorge Configure the code generation mode and the default operating mode to be Set based. The default is row based and set based that's why you get the error (since there are no equivalent PLSQL/row based versions of the XML SQL functions). Cheers David

Posted by David Allan on April 28, 2010 at 05:50 AM PDT #

Hi Dave, It's working now. Thanks!!! After working a bit with this technology I was wondering which is the best way to refresh data partialy? For example, how can I do if I just want to update the Title of employees or the address of the location in base of the data sources? Do I have to re-populate the entire XML DB? I hope no!!! If you have some tips refered to this, will be great. Thanks again, Jorge

Posted by Jorge on May 03, 2010 at 05:31 AM PDT #

Hi Dave, I've followed your instructions in creating the xml mapping; however, I'm getting the following error when executing the mapping(deployment was successful): ORA-00972: identifier is too long I've make sure all the table and column names are within 30 character long. Please let me know if you know any solution to this problem. Thanks.

Posted by OWB Newbie on May 11, 2010 at 11:43 AM PDT #

Hi Worth checking your expressions ... if your are struggling send the MDL or post somewhere. Cheers David

Posted by David Allan on May 11, 2010 at 02:22 PM PDT #

Hi Jorge There is an UPDATEXML SQL function that you can use to update fragments of the XML, to model this update directly in OWB gets a little trickier I think, may have to wrap in a function, code templates might make some of it easier to model just as a regular map. Did you get anywhere with it? Cheers David

Posted by David Allan on May 12, 2010 at 02:26 AM PDT #

Unfortunately, the link to the experts zip file doesn't seem to be working anymore... What i am more interested in is how to pick up a simple (but big 5Gb) xml file and putting everything directly in a relational table. Just straightforward stuff nothing fancy. Just every element in a record. Can i use a external table for this ? The example listed here assumes that the data is already in a table. I have no idea how to do this ..

Posted by Michael Reitsma on October 07, 2010 at 07:13 PM PDT #

Euuhm, This is somewhat complicated and in line with the first post ... i am with the 99.9% here. I have a 6Gb xml file and need to load that in the database and extract it to 3 different tables. How do i do this in OWB , while using the fastest way as well ? This problem is two-fold i think. -1 get the xml in the db -2 split the xml once it is in the db into 3 relational tables. When this is done i can discard the xml file,i only use the 3 tables. The xml file is just a carrier here. I can use OWB 11GR2, licensing is no issue. I can use another tool to generate XQuery if that can help. Alas looking for XQuery in the help draws a blank. (btw the mentioned link doesn't work anymore)

Posted by Michael R on November 04, 2010 at 12:55 AM PDT #

Hi David, This is an excellent tutorial for loading data from relational to XML. Based on your comments in this article I configured code generation mode and default operating mode to be Set based and now I get an error that the mapping does not support Set based mode. I am using 11g R1 . Is there anything that I am missing. Thanks, Manjula

Posted by Manjula Kethireddy on November 10, 2010 at 09:22 AM PST #

Hi Manjula Sounds like you have used an operator that supports row based only code, which one is it? A procedure? If so, you can change to use a function if possible to change the mapping. Cheers David

Posted by David Allan on November 10, 2010 at 09:37 AM PST #

Hi, Can you use this to create an xml document from relational tables that writes the output to a text file ? Or are there other ways, i am using 11gR2 btw.

Posted by Michael Reitsma on April 07, 2011 at 05:06 PM PDT #

Hi Michael Yes totally, you can have a table function as a target which takes the XMLType and writes to a file, in fact that's what the utility function in the XML utilities does, look at the function SAVE_XML_TO_FILE in the zip on the code sharing site; https://www.samplecode.oracle.com/tracker/TrackerDownload/linkid/prpl1004/remcurreport/true/template/ViewAttachment.vm/attachid/10858/filename/owb_xml_etl_utils.zip You can read about table functions in OWB here; http://blogs.oracle.com/warehousebuilder/2008/06/table_functions_as_source_and.html Also in 11gR2, as another alternative you could use the code templates to integrate and write XML in the identical manner as ODI. This leverages an XML platform definition which can be added into OWB, and uses the ODI XML JDBC driver. I can post information on this if you are interested. Cheers David

Posted by David Allan on April 08, 2011 at 01:44 AM PDT #

Hi David, yes i am interested in the 11gR2 stuff. The first link you mention doesn't work for me so perhaps there is another link ? rgrsd Mike

Posted by Michael Reitsma on April 12, 2011 at 11:08 PM PDT #

Hi Mike The page describing the experts is here; http://www.oracle.com/technetwork/developer-tools/warehouse/owbexchange-099994.html The experts were moved to samplecode.oracle.com You will have to register (or use your OTN account, not sure). Then go code samples, Data Integration, artifact id is S604, then there is an attachment owb_xml_etl_utils.zip. Sorry I couldn't see a way of giving a URL other than the one I posted, this used to be easily accessible from OTN then there was some re-hosting/reorganization . Cheers David

Posted by David Allan on April 13, 2011 at 02:07 AM PDT #

Unfortunately the samplecode site uses a host:7777 construction to login ! Most companys block ports other than 80 and 8080 (and ssl) so i can not login there...

Posted by Michael Reitsma on April 20, 2011 at 07:55 PM PDT #

Hi Michael The zip can also be found below on; http://sourceforge.net/projects/owbland/files/Stuffs_from_Oracle_site/owb_xml_etl_utils.zip/download There are other odds and ends copied here; http://sourceforge.net/projects/owbland/files/Stuffs_from_Oracle_site Cheers David

Posted by David Allan on April 21, 2011 at 12:13 AM PDT #

Hi david, Thanx for the update. I am now able to download it. I looked at the sql file and have some questions about it: (although i haven't actually been able to use it yet and as such some questions may seem pointless..) Am i correct in saying that it needs the XMLRoot() statement to have valid xml, since you need the xml root tag ? In the save_xml_to_file.sql you use xml.getClobVal to serialize to disk. However this post seems to indicate you might run into trouble when the total generated xmltext is larger than 32k. http://forums.oracle.com/forums/thread.jspa?threadID=581801 This happens because this function outputs the entire xml as one line and the max line size is 32k. Instead they suggest the use of xml.extract('/*').getClobVal() which outputs nicely formatted xml causing every tag to go on line. But what happens if there is a single xmltag with more than 32k of data in it, say a picture or something ? Is there an alternative to utl_file ? rgrds Mike

Posted by Michael Reitsma on April 21, 2011 at 04:23 PM PDT #

Hi David, I tried using the save_xml_to_file table function but no luck: I created the table function in the db, and then imported it in my 11gr2 owb. I have source table and expression with xmlforest statement. How do i connect the xmltype into the ref_cursor ? (And specify the dirname and filename at the same time)

Posted by Michael Reitsma on April 21, 2011 at 11:28 PM PDT #

I posted over at the other post, regarding table function operators, why i can not get this to work. There is no option anymore in the client(owb AND the API to specify the group type for an Input Group ! So when i have a TFO with a ref_cursor as input i can not specify what attributes are supposed to be used to cosntruct the ref_cursor. The omb script used there doesn't work on my client....

Posted by Michael Reitsma on April 25, 2011 at 11:55 PM PDT #

Hi Michael I just posted this, hopefully it'll help fill in some of the holes... http://blogs.oracle.com/warehousebuilder/2011/04/owb_11gr2_table_functions.html Cheers David

Posted by David Allan on April 26, 2011 at 02:18 AM PDT #

Why do the images not load anymore ? It seems that they point to antonioromero's blog and that doesn't exiist anymore (404). And where is the tag cloud gone ? It is now not possible anymore to see the shortcut to xdb ..

Posted by Michael Reitsma on May 09, 2011 at 05:39 PM PDT #

Hi David, The images don't show anymore since they all point to the antonio romero blog which doesn't exist anymore.

Posted by Michael Reitsma on May 10, 2011 at 09:53 PM PDT #

Hi David, The images don't show anymore since they all point to the antonio romero blog which doesn't exist anymore.

Posted by Michael Reitsma on May 12, 2011 at 02:33 AM PDT #

Hi is the mapping used here http://blogs.oracle.com/warehousebuilder/resource/gems/xmlmap6.gif also available for download.
I can not figure out how to group employees nodes under their departments nodes while using departmentnames in the departmentnode.
Using xmlagg you can only do this by specifying this by using group by departmentname but the group by bit doesn't end up properly in the generate intermediate sql. See also here: http://forums.oracle.com/forums/thread.jspa?messageID=9697796&#9697796

Posted by Michael Reitsma on June 29, 2011 at 10:23 PM PDT #

Hi Michael

Something along the line of this....

Which will generate code like this;
XMLAttributes("DEPT"."DNAME" as "name"),
XMLElement( "Employee", "EMP"."ENAME")


Posted by David on July 01, 2011 at 08:18 AM PDT #

Hi David,

Thx for the update: yes that works,sometimes it is so simple.
However i have found that the code it generates doesn't use the inputgroup naming of the aggregator in the mapping. So this means i would have to adapt the used xml code to use the input group nameing that owb uses internally.

That is pretty confusing... so instead i have opted to use a inline view object so the code is easier to maintain.

Posted by Michael Reitsma on July 03, 2011 at 10:01 PM PDT #

hi dude, that option for create xml from table is removed from 11g2, is that correct? (i.e. right click talbes, then there is no menu option for create xml as you show in your downables), how should I achieve the same thing here if I am using 11g2?

Posted by allen on October 04, 2011 at 09:39 AM PDT #


The 'Create XML from objects' option does not come with OWB out of the box. This is part of the expert and download, you have to right click on the 'Tables' node for example and select 'Maintain Creation Experts Here', then click on the Public Experts -> XML_ETL -> CREATE_XML_FROM_OBJECTS expert for the option to appear in the tree. After this is done you are good to go.


Posted by David on October 05, 2011 at 03:53 AM PDT #

Hi David.
I´m having problems importing the xml_pluggables.mdl into OWB.
The following error is displayed:

(Running on 11g Enterprise 64bit, so the import is converting to 11g first)

Error occured importing from file: ...owb_xml_etl_utils\owb\xml_pluggables_11_2.mdl
Error at line 10: MDL1261: Error importing PROJECT XML.XML.

Detailed Error Message:
Programmer/Design error: setArrayValues(...) called for array with duplicate values.
Change getArrayValues algorithm or use lower level methods to update the array.

Any idea what causes this? And how to fix it?


Posted by Bjorn on November 10, 2011 at 07:35 PM PST #

this looks good, however, I did not get how the xsd works when it is used to generate the pluggable mapping. here is the scenario I have, let's say I load a delimited file into a staging table, with generic column name col1, col2, col3... then I would like to use a xslt or xsd (config saved in db, based on run time invoking, use a different xslt), once a I get a raw xml block like <records><record><col1>value</col1>...</record><record>...</record></records>, how do I create a xsd or xslt to utilize your expert to generate the pluggable mapping?


Posted by guest on May 11, 2012 at 11:31 AM PDT #

Hi Allen

I am a little unclear as to what you are trying to do, perhaps if you explain a little more it will help.

The expert is a design time mapping component generator, the experts run at design time too, are you trying to just drive it from different information? Or something different?


Posted by David on May 14, 2012 at 03:30 PM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.


« April 2014