Influence xmltype creation when register XML Schema

656158

Hi,

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:complexType>

    <xs:all>

      <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" />

    </xs:all>

  </xs:complexType>

</xs:element>

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

FORCE      => FALSE,

OPTIONS    => DBMS_XMLSCHEMA.REGISTER_AUTO_OOL

The result is that each element gets the following attributes:

oraxdb:propNumber="4986"

oraxdb:global="false"

oraxdb:SQLName="var_number_one"

SQLType="var_number_one928_T"

oraxdb:SQLSchema="SYSTEM"

oraxdb:memType="258"

oraxdb:MemInline="false"

oraxdb:SQLInline="true"

oraxdb:JavaInline="false"

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 http://www.flickr.com/photos/[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

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

Just out of curiousity. How did you / with what program did you create the picture?

656158

"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];

		style=filled;

		color=lightgrey;

		label = "myDocument967_TAB";

		"myDocument947_T"

	}

	subgraph cluster1 {

		node [style=filled,fillcolor=white];

		style=filled;

		color=lightgrey;

		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

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.

Thanks.

Marco Gralike

Cool. Thanks, I will look into it.

656158

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.