OWB 11gR2 – XML

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.

image

You build XML modules in the same way as you build any other module.

image

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;

image

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.

image

Select the table type, the import for XML will use the JDBC database metadata to return ‘tables’ corresponding to the elements in the XML.

image

I just selected all the elements…

image

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;

image

Note there are some extra columns from our XML element definition;

<!ELEMENT city (client*)>
<!ATTLIST city
    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.

image

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.

image

Deploying and executing the mapping we can then view the resultant data in the target table, pretty straightforward.

image

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.

Comments:

Hi.
I have installed the platform script (running OWB ver 11.2.0.1 on Red Hat) and are experiencing problems during registration of a location(XML). The error message I get is:

A SAXexception was cought while reading the model saying An IOException occured while including another schema saying no protocol:
/u01/oracle/product/11.1.1.5/odi/xmldir/filer/file: GassomsetningType_ODI.xsd

The heading of the xsd-file in the URL is:

<schema xmlns="http://www.w3.org/2001/XMLSchema"
targetNamespace="urn:kontroll:gassomsetning:oppgave:v1"
xmlns:tns="urn:kontroll:gassomsetning:oppgave:v1"
xmlns:gass="urn:kontroll:gassomsetning:basis:v1"
xmlns:type="urn:kontroll:gassomsetning:type:v1"
xmlns:xdb="http://xmlns.oracle.com/xdb" elementFormDefault="qualified">
<import namespace="urn:kontroll:gassomsetning:type:v1"
schemaLocation="GassomsetningType_ODI.xsd" />
<import namespace="urn:kontroll:gassomsetning:basis:v1"
schemaLocation="GassomsetningBasis_ODI.xsd" />

All import files are present in the same directory as the main xsd-file.
I'm using the oracle user in the location registration, and this user has priviledges to read/write/execute all files in the current directory.

The URL I'm using is:
jdbc:snps:xml?f=/u01/oracle/product/11.1.1.5/odi/xmldir/filer/SAGA_Xml_fil.xml&d=/u01/oracle/product/11.1.1.5/odi/xmldir/filer/GassomsetningOppgave_ODI.xsd&s=Gassoppgave

Is this a known error message, and if so, how can this be solved?

Regards,
Bob

Posted by Bob on January 21, 2012 at 12:36 PM PST #

Hi.
I have installed the platform script (running OWB ver 11.2.0.1 on Red Hat) and are experiencing problems during registration of a location(XML). The error message I get is:

A SAXexception was cought while reading the model saying An IOException occured while including another schema saying no protocol:
/u01/oracle/product/11.1.1.5/odi/xmldir/filer/file: GassomsetningType_ODI.xsd

The import statements in the main xsd-file in the URL is like:

<import namespace="urn:kontroll:gassomsetning:type:v1"
schemaLocation="GassomsetningType_ODI.xsd" />
<import namespace="urn:kontroll:gassomsetning:basis:v1"
schemaLocation="GassomsetningBasis_ODI.xsd" />

All import files are present in the same directory as the main xsd-file.
I'm using the oracle user in the location registration, and this user has priviledges to read/write/execute all files in the current directory.

The URL I'm using is:
jdbc:snps:xml?f=/u01/oracle/product/11.1.1.5/odi/xmldir/filer/SAGA_Xml_fil.xml&d=/u01/oracle/product/11.1.1.5/odi/xmldir/filer/GassomsetningOppgave_ODI.xsd&s=Gassoppgave

Is this a known error message, and if so, how can this be solved?

Regards,
Bob

Posted by guest on January 21, 2012 at 12:38 PM PST #

Hi,

Can you give a working example and stop writing 'just like in ODI' Well, not everbody use ODI before OWB.
In XML Location, why do you need userName, Password and schema for an XML file.
How do I tell it the location of my XML file.

Thanks
dean

Posted by dean on February 05, 2013 at 12:45 PM PST #

Hi Dean

The file name is defined in the URL of the location, so if I want to load the file /demo/xml/staff_internal.xml my URL would look like this;
jdbc:snps:xml?f=/demo/xml/staff_internal.xml&d=/demo/xml/staff_internal.dtd&ro=true&s=staff

The ODI XML JDBC Driver documentation is here with a description of all of the parameters;
http://docs.oracle.com/cd/E23943_01/integrate.1111/e12644/xml_file.htm#CACDHIBF

When using in-memory to load the XML, the username and password are ignored, so you can supply any string.

Cheers
David

Posted by David on February 05, 2013 at 01:01 PM PST #

Hi David,

Windows 7 x32
Oracle 11gR2 RDBMS
OMB*Plus: Release 11.2.0.1.0

I'm trying to run the script but I'm getting an error. I start OMB*Plus and connect as follows:
OMBCONNECT edwowner/password@orcl:localhost:1521:orcl

I've saved the command file to c:\\owb_xml.tcl

I then run it from OMB*Plus as follows:
OMB+> source C:\\owb_xml.tcl

Which gives the following error:
OMB00001: Encountered OMBALTER at line: 1, column: 262. Was expecting one of: <EOF>
"ADD" ...
"SET" ...
.
OMB+>

All I did was copy and paste the commands from your link into a Notepad file. Any ideas what I'm doing wrong?

Thanks

--
mohammed

Posted by guest on February 12, 2013 at 12:14 PM PST #

Hi Mohammed

Can you try doing 'Save As' on the link to platform_xml.tcl and sourcing that script? I think if you have copy pasted the text from browser to notepad or something, the file is not formatted properly.

Cheers
David

Posted by guest on February 12, 2013 at 01:36 PM PST #

Hi David,

My apologies. After posting, I realized my mistake. I did as you suggested and it worked perfectly.

BTW, for those searching for snpsxmlo.jar, in the 11gR2 database install, it can be found here:
%ORACLE_HOME%\owb\misc\drivers

Thanks for the article Dave.

--
mohammed

Posted by guest on February 12, 2013 at 03:41 PM PST #

Hi

can anyone mail me snpsxmlo.jar file as I am unable to find it.

My email id is shubhamis@yahoo.com

Regards

Posted by guest on September 16, 2013 at 03:30 AM PDT #

Hi

Its in the ODI installation, if you download and install ODI, the JAR/driver is there.

Cheers
David

Posted by David on September 16, 2013 at 08:00 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

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

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today