Re: Development Trends in Web and Oracle

"Andy Hassall" <andy_at_andyh.co.uk> wrote in message news:u0q631p9v0gn6llub5oak0nimkpdu4hcji_at_4ax.com...

Comments in-line Andy. Thank you for your post.

>
> SQL> insert into t values ('<a>b<c>d</c><e>f<g>h</g></e></a>');
>
> 1 row created.

<snip>

>
> Then done a block dump to have a look at how it's stored. The row comes
> out
> as:
>
> block_row_dump:
> tab 0, row 0, @0x1f58
> tl: 72 fb: --H-FL-- lb: 0x1 cc: 1
> col 0: [68]
> 00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 06 4d 49 00 30 09 00 00
> 00 00 00 00 20 00 00 00 00 00 01 3c 61 3e 62 3c 63 3e 64 3c 2f 63 3e 3c 65
> 3e 66 3c 67 3e 68 3c 2f 67 3e 3c 2f 65 3e 3c 2f 61 3e
> end_of_block_dump
>
> ... which appears to be rather a lot of extra padding around the XML
> itself.
> CLOB storage might even be more space-efficient!

Hmmm. Not good. It's a Sunday. I do not have access to a database at the moment so the following is has no experimental backup.

Two things
1. insert into t values ('<a>b<c>d</c><e>f<g>h</g></e></a>')

This isn't XML, there is no namespace, no schema ref etc. To me this is just marked up text. To the database I am guessing it is the same and stored with the respect it deserves. If this turns out to be the case, then the root of the inefficiency lies in that an XML schema hasn't been registered against the table column.

But ....

2. Here is a snippet I copied from asktom

 (http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:5791984256191)> First, if you store the data in a structured fasion, i.e. based on an> XML Schema that decomposes your XML document into> object-relational tables, there's good news and bad news. The> good news is, Oracle10g now accomodates XML Schema> Evolution, which will let you make changes to an XML Schema> and have your dataThe XML is shredded and under the bonnet stored in tables if schemavalidated. This is what the docs say. In the 9i XMLDB I have worked on twoyears ago, this is what it does too - after insertion of schema validatedXML I have poked around and seen that the tag data has been stored in manyindividual tables. Thus, other than the maintenance required to keepeverything in order, the storage efficiency of these tables containing thedata to recompose the XML must be as efficient as it is in a solelyrelational model, because it is the same underlying engine.>> If you've got an XML Schema isn't it possible to automatically  derive a> relational schema that can store the XML? (Or at least, one possiblerelationalI believe JAXB and JDO are used by the Java camp for this purpose. Perhapssomeone else can elaborate?> This might be where Oracle was trying to go with indexes on XML, but it> strikes me as still being a bit too opaque; you can't issue SQL againstthe> data represented by the XML.I think I understand what you mean here and there are at least 2 ways ofdoing it in 9i.With 10g, you only have to know the hierarchical structureand can expend with XPATH expressions altogether. Care testing the syntaxbelow, I don't have an access to an XMLDB at the moment to test this on.1. Oracle Text eg. SELECT nextOfKin FROM tableName WHERE CONTAINS(xmlcolumname,'freddy INPATH('company/employees...'))>02. SQL with XPATHeg. SELECT EXTRACT(xmlcolumnName,'company/employees/nextOfKin/text()') FROM tableNameRegardsHex Received on Sun Mar 13 2005 - 11:00:56 CST