OWB 11gR2 – XML
By David Allan on Apr 13, 2011
An XML post I did a while back was on Leveraging XDB, which illustrated how to leverage the XML SQL capabilities of the Oracle database. A couple years on, this post could as well have been titled Leveraging ODI, since here I’ll show how with a new XML platform defined, you can leverage the ODI XML JDBC driver and build code template mappings to extract and integrate XML in the same manner as ODI.
First up copy the snpsxmlo.jar file from ODI 10g into the OWB owb/lib/ext directory and also on the server if you are running distributed. You’ll need to restart any processes (OWB, control center agent etc.) to pick up the jar. Next download and execute this XML platform script here to define the XML platform in the OWB repository – the platform script defines datatypes and some properties of the platform (default URL, driver class etc.). You can execute this from OMBPlus or from the OMBPlus panel in the designer. This will create an XML node under the Databases node in the Projects panel and Locations panel.
You build XML modules in the same way as you build any other module.
Create the module and define the location using the URL for the XML JDBC driver (see doc here) – just like in ODI we can stage the data in a different database rather than what the default driver does, below we are just using the default in-memory mode;
You can hit Test Connection and validate your input. If you get class not found ensure you put the driver JAR file in the correct directory. There is a bit of typing going on as you can see, so double check your work.
After that thought its easy street. Now you can import just like regular modules.
Select the table type, the import for XML will use the JDBC database metadata to return ‘tables’ corresponding to the elements in the XML.
I just selected all the elements…
After the import is complete, just like in ODI we get the columns for our elements and a table for each element, You can then view the data in the elements;
Note there are some extra columns from our XML element definition;
<!ELEMENT city (client*)>
CITY_ID ID #REQUIRED
CITY_NAME CDATA #IMPLIED
POPULATION CDATA #IMPLIED
The columns CITYORDER, CITYPK and REGIONFK are the additional columns the XML JDBC driver has added to ‘relationalize’ the nested XML element.
You can then go on and build code template mappings manipulating the XML, below I join region, city and client and load the result into an Oracle target table, I could do all sorts along the way.
The execution unit view has the XML elements being joined in one execution unit and the Oracle target in another, this was the default execution plan generated by OWB. I used the SQL to Oracle load code template shipped with OWB and the Oracle target code template for the integration execution unit.
Deploying and executing the mapping we can then view the resultant data in the target table, pretty straightforward.
That’s a whirlwind pass on extracting information from XML in a very straight forward manner. The platform definition provided has been used here for demonstration to illustration how simple this can be. Writing XML is another interesting capability also possible with such an approach using this driver, but that’s another story.