Designing an XML View in OWB

To write about Travis at The Fillmore or generating views from OWB...? Oh just this once I'll do the OWB thang...


Here we will see one way to design a view in OWB. This is applicable to any view but the XML views are an interesting example, so here goes. The mapping editor can be used to build views and materialized views, this is done by having the target operator as a view operator (or materialized view) for example. After the map is fully designed you can create and bind from the view operator in the map - this will generate the view object with the SQL generated from the map definition for the content of the view.


In the example below we are reading XML from a purchase orders table where the XML is stored in an XMLType column and targeting a view, the map extracts line item information (it uses the components generated from the XML expert on OTN)  along with some details of the purchase order:


XMLViewMap:


When the create and bind is selected a new name can be entered. The view definition generated is shown below:


XMLViewDef:


There are certain parts of the XML/SQL that are not possible in OWB (such as the XMLTable function - these have to be embedded in a view), but the rewrite of these boils down to the same code as the XML/SQL extract style functions anyway. The expression/filter style operators allow a free text style expression to be defined so all kinds of stuff can be done including XQuery within the database.


Back to Travis, I mean work...:)

Comments:

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