Forum Stats

  • 3,826,865 Users
  • 2,260,714 Discussions


Influence xmltype creation when register XML Schema

656158 Member Posts: 5
edited Sep 17, 2008 4:28AM in XML DB

I'm trying to register an XML Schema a little differently from the default Oracle 11 way.

Let say, I've got an element definition in my XML Schema:
<xs:element name="foobar">
<xs:element name="var_number_one" type="myVar" />
<xs:element name="var_number_two" type="myVar" minOccurs="0" />
<xs:element name="var_number_three" type="myVar" />
<xs:element name="var_number_onehundredfiftyone" type="myVar" minOccurs="0" />
<xs:element name="var_number_onehundredfiftytwo" type="myVar" />
This gets processed by Oracle during the registration process, I use these parameters for registerSchema:
LOCAL      => TRUE,     -- local
GENTYPES => TRUE, -- generate object types
GENBEAN => FALSE, -- no java beans
GENTABLES => TRUE, -- generate object tables
The result is that each element gets the following attributes:
which means that each element (var_number_*) is like an SQL Object column/property in the foobar table/object... right?

Here is what I would like to try, if it is possible at all:

I want the name of the element to be a varchar property/column of each row in the foobar table.

The image attached[email protected]/2784396692/sizes/o/ illustrates the current and target situation.

Is it possible to do this by influencing the XML Schema without changing the original xml format, using the oraxdb attributes? Or is their another solution?


  • mdrake-Oracle
    mdrake-Oracle Member Posts: 5,904
    I think you are asking for a name / value pair storage model for the data where each node in the document would become a row in the table, correct ? OR does not support this model. Binary Storage with an complete XML Index uses a variant of this model as the basis for the index under the covers.

    Why do feel that you need a name / value storage model. This results in very large numbers of rows very quickly and nasty joins for evaluating path expressions, particularly if multiple predicates exist.
  • Marco Gralike
    Marco Gralike Member Posts: 4,491 Silver Trophy
    Just out of curiousity. How did you / with what program did you create the picture?
  • 656158
    656158 Member Posts: 5
    "Why draw if you can code"... I use graphviz when I need to generate diagrams, especially when the content varies.
    In this case, I've used the processed XML Schema, after Oracle has done its thing, to gather info on tables and types, programmatically generating the code for the diagram.
    Here's the 'code' I used for the picture (one part at least).
    digraph G {
    label = "Target Situation"
    subgraph cluster0 {
    node [style=filled,fillcolor=white];
    label = "myDocument967_TAB";
    subgraph cluster1 {
    node [style=filled,fillcolor=white];
    label = "foobar935_TAB";
    "myDocument947_T" -> "foobar933_T";

    "foobar933_T" -> "myVar928_T";

    "myVar928_T" -> "myVarSub1929_T";

    "myVarSub1929_T" -> "myVarSub2930_T";

    "myVarSub2930_T" -> "myVarSub3931_T";

    "myDocument947_T" [shape=record,label="{{myDocument}|{}|{header\lcreationDate (DATE)\lupdateDate (DATE)\lfoobar\ldocumentId (NUMBER)\l}}"]
    "foobar933_T" [shape=record,label="{{foobar}|{}|{VarName (VARCHAR2, index)\l}}"]
    "myVar928_T" [shape=record,label="{{myVar}|{mva1 (RAW)\l}|{myVarSub1\l}}"]
    "myVarSub1929_T" [shape=record,label="{{myVarSub1}|{}|{myVarSub2\l}}"]
    "myVarSub2930_T" [shape=record,label="{{myVarSub2}|{mva2 (RAW)\l}|{value (VARCHAR2)\lmyVarSub3\l}}"]
    "myVarSub3931_T" [shape=record,label="{{myVarSub3}|{}|{mva3 (VARCHAR2)\lmva4 (NUMBER)\l}}"]
  • 656158
    656158 Member Posts: 5
    That does confirm my suspicion.
    I did theorize on the resulting size of the table, but was hoping for some Oracle indexing wonder. It would still be a test case, to compare to other possibilities. Too bad it isn't working.
    Maybe I'm going to change the test set and the schema, such that I can test it anyway. (Maybe some miracle will happen.)

    I'm also testing the Binary Storage btw.

  • Marco Gralike
    Marco Gralike Member Posts: 4,491 Silver Trophy
    Cool. Thanks, I will look into it.
  • 656158
    656158 Member Posts: 5
    I've tested on a document structure that puts the data variable name in an attribute value instead of the nodename.
    It proved to be just slightly slower for CLOBs, for SQL Object it didn't work at all and for Binary XML it only worked when the query wasn't too difficult, plus, query time went from 11.5 to 67 sec and from 42.9 to a scary 439 sec.
This discussion has been closed.