Monday Oct 24, 2011

Example XDB/XML Mapping

The mapping to construct the XML for the example 3-46 in the XDB documentation can be found in the xdb_example_3_46.mdl MDL file. This MDL is for 11.2.0.2, so you will need at least that version.

The example was described in the earlier post on leveraging XDB, the Oracle doc has changed since that older post and example 3.44 is now 3.46 … so by the time you read this it might be different.

The code can be generated and you can inspect with the SQL in the XDB documentation to see how the different parts have been composed.

The other technique that can be used and described here is the inline view, so you can effectively bury your own SQL in a view that is not deployed in the database but the code is generated inline when used in the mapping.

Both of these illustrations are included in the MDL file mentioned above.

Tuesday Oct 11, 2011

Generating XML with Experts

The leveraging XDB post (here) from a few years ago is one of the most actively read posts, since that was done there have been a few more updates on the expert posted within it. One of the updated areas was in the generation of XML using the Oracle Database using the expert., the areas include supporting generated a single document vs multiple documents and the ability to include/exclude attributes from the content, plus whether to create the attributes as XML properties or XML elements.

A recent query was regarding how the ‘Create XML from objects’ menu option gets created. This is added just by enabling the expert on the ‘Tables’ node in the tree, here we see the sequence of actions to do this in OWB 11gR2, you must first import the expert’s MDL, then add the expert to the tree as follows.

First right click on Tables node and select ‘Maintain Creation Experts Here' (you can add any of your own custom experts to parts of the tree also);

owb_11gr2_xmlgen1

Then in the XML_ETL folder within public experts, enable the CREATE_XML_FROM_OBJECTS expert;

 owb_11gr2_xmlgen2

That’s it! Now you can run the expert from the tree. For example now click on the Tables node, you will see the ‘Create XML from objects’ option.

owb_11gr2_xmlgen3

This then runs the expert, the dialog was enhanced to include a ‘Generate Root’ option – this was added so that all generated XML fragments are wrapped in a single element rather than created as XML documents. Using this lets you generate one document like;

<AllDepartments>
<Department name=’ACCOUNTING’/>
<Department name=’RESEARCH’/>
</AllDepartments>

rather than multiple documents like (where Department is the route node);

<Department name=’ACCOUNTING’/>
<Department name=’RESEARCH’/>

So let’s select ‘Generate Root’ and see how it works….

owb_11gr2_xmlgen4

As before we get to enter the name of the pluggable map that gets generated.

owb_11gr2_xmlgen5

We then choose the tables for or document, and order the master to the detail, we will have departments and the employees nested inside the department;

owb_11gr2_xmlgen6

We then can define the element name for the root (because we selected generate root), and the dept and emp tables.

owb_11gr2_xmlgen7

For each table we can then define the XML element/attribute names for the columns also, we can also define whether to exclude attributes, or define an element name for the attribute rather than a property name.

owb_11gr2_xmlgen8 

For the EMP XML element details we will exclude the foreign key column DEPTNO, and provide nice business names for the properties.

owb_11gr2_xmlgen9

After this, the pluggable mapping is generated. We can use the table function from the earlier post and the pluggable mapping to write the XML to file, for example we generate the following from the SCOTT schema.

owb_11gr2_xmlgen10 

Fairly simple example of leveraging the database along with experts to generate based on some basic inputs from the guided expert.

Thursday Sep 20, 2007

Leveraging XDB

[Read More]

Thursday May 03, 2007

Designing an XML View in OWB

[Read More]
About

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

Search

Archives
« July 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
31
  
       
Today