Re: [xml-dev] Shredding XML

[Liam Quin <liam@w3.org>]

On Thu, Oct 29, 2009 at 05:36:33PM -0400, David A. Lee wrote:
> Ron Bourret has some excellent references here
> http://www.rpbourret.com/xml/XMLDBLinks.htm#Relational
>
[...]
>
> I've been faced with this problem myself on multiple occasions where the  
> organization is not very XML friendly and "needs" the data in a  
> relational DB and inevitably the solution has been
>
> 1) Dont do it.
> --> XML ended up in files , sometimes a DB table with top level topics  
> or some basic metadata only.

Yes, this is what I recommended in a book on the subject, years ago,
and is what generally works the best.

> 2) Put XML in "Blob" fields
> --> solves the "letter of the law" -- yea its in "A Relational Database"  
> but its useless

If the purpose (as so often) of the relational database is warm fuzzies,
it's far from useless.  Plus it'll use massively more disk storage! :-)

> 3) Its a royal PITA
> A shredding can end up with about 2x the # of distinct elements into  
> tables.   Handling mixed content is excruciatingly painful.
> Doing a good job usually ends up with relational data thats almost  
> impossible to access relationally.

I remember waiting 45 minutes for such a system (on a high-end server)
to reconstitute a 5 megabyte SGML document... but they were complex
documents, aircraft manuals, with often dozens of attributes for a single
element.

XML is all about sequence, containment, and unbounded-length text,
none of which are an ideal fit for SQL, although there are SQL
extensions for each of them.

So he best thing is to work out what you actually need for your SQL
queries, and to split tha up and put it in the relational database,
and ot leave the documents out in the file system, but, managed by
a server process so that users can't rename them or change them
without telling the database.

Frankly, if they are using Oracle or DB2 or SQL Server, put the
data into a blob of type XML, and you can use XPath on it,
and XQuery, efficiently, directly from SQL, with most people
completely unaware of it :-)

>> I find it intersting the some of the 'big boys' are at least giving
>> the appearance of providing first-class support for XML both in terms
>> of storage options and manipulation capability. IBM for example has
>> pureXML. I haven't used these enough to know if they're just a thin
>> veneer of whether they have real substance and depth, so again your
>> experiences are welcome.

They have real substance and detph.  All the major databases are
supporting XQuery, and supporting it directly, not as a layer on
top of SQL.  It's being used in production, with large databases,
in real commercial environments.

Liam


-- 
Liam Quin, W3C XML Activity Lead, http://www.w3.org/People/Quin/
http://www.holoweb.net/~liam/ * http://www.fromoldbooks.org/