Using XML and a relational database

[Comcast]

Hello all,
I am developing an interface system for an application I was written. This interface will connect to other system to share information. I would like to use xml and xsl to generically describe each interface. The trouble I am having is that it seem that if you export in xml by table then it is impossible to combine the xml tables files back together in a hierarchical fashion. I can do it programmatically but I would like to do it with xsl so that knowledge of the database is not in the code but in the xsl. I can write generic code for dumping tables and sql statements. But formatting should be generic.
Can this be done is xsl?

 

[Gregory Burd]

Hello all,
I am developing an interface system for an application I was written. This interface will connect to other system to share information. I would like to use xml and xsl to generically describe each interface. The trouble I am having is that it seem that if you export in xml by table then it is impossible to combine the xml tables files back together in a hierarchical fashion. I can do it programmatically but I would like to do it with xsl so that knowledge of the database is not in the code but in the xsl. I can write generic code for dumping tables and sql statements. But formatting should be generic.
Can this be done is xsl?

Why are you using a relational database for XML? We have a native XML database that could solve your problems.

-greg

Gregory Burd
Product Manager
Sleepycat Software, Inc. http://www.sleepycat.com/

 

[Mikito Harakiri]

Why are you using a relational database for XML? We have a native XML database that could solve your problems.

Because "XML database (native or not)" is misnomer?

 

[Lemming]

You are asking the wrong question. The question you should ask when considering XML for any task is always the same: "Is XML suited to this task?" The answer is also always the same: "No."

Why are you using a relational database for XML? We have a native XML database that could solve your problems.

Words fail me.

Lemming

 

[DA Morgan]

Why are you using a relational database for XML? We have a native XML database that could solve your problems.

-greg

Gregory Burd
Product Manager
Sleepycat Software, Inc. http://www.sleepycat.com/

The only problems solved by an XML database are having too much unused disk space, too much bandwidth, too much scalability, and too much performance.

I'll put money on every XML database company being out of busines within ten years. Or, perhaps optimistically, joining the large number of successful companies selling object databases.

And thank you for posting to every usenet group you could spell.

 

[Pat (archive)]

The only problems solved by an XML database are having too much unused disk space, too much bandwidth, too much scalability, and too much performance.

I'll put money on every XML database company being out of busines within ten years. Or, perhaps optimistically, joining the large number of successful companies selling object databases.

And thank you for posting to every usenet group you could spell.

If that's the case and native XML databases no longer exist in this world (just like XQL?), will you perceive all XML, XSLT, XQuery, etc (what else?) just vanish?

Just discussion.

 

[DA Morgan]

If that's the case and native XML databases no longer exist in this world (just like XQL?), will you perceive all XML, XSLT, XQuery, etc (what else?) just vanish?

Just discussion.

XML has value. XML databases are at best a niche like pure object databases and more likely a disaster.

My main objection to XML in the database is the huge number of bytes required to store a single byte of data:

<someridiculouslylongtag>1</someridiculouslylongtag>

and then you get to push this to the app server and off to the client. XML was made for a purpose. It should be used for that purpose. Not kludged into every nook and crany imaginable.

 

[Alex Shirshov]

Hello, DA!
You wrote on Sat, 23 Oct 2004 13:42:18 -0700:

[Sorry, skipped]

DM> XML has value. XML databases are at best a niche like pure object DM> databases and more likely a disaster.

I want to store XML docs in DB avoiding file system, source control system, etc. How can I work with documents as xml, but not as CLOBs without a bare support of xml technologies in DBMS?

DM> My main objection to XML in the database is the huge number of bytes DM> required to store a single byte of data:

DM> <someridiculouslylongtag>1</someridiculouslylongtag>

DM> and then you get to push this to the app server and off to the client.

One remark - somenonsensicaltag is the part of the data, the same important as figure 1. And I don't think _any_ db won't optimize the storage to persist the name of the tag in only one instance.

DM> XML was made for a purpose. It should be used for that purpose. Not DM> kludged into every nook and crany imaginable.

XML is the text. It sure is better to keep the data of any type in DB, and xml is not an exception.

With best regards, Alex Shirshov.

 

[DA Morgan]

Hello, DA!
You wrote on Sat, 23 Oct 2004 13:42:18 -0700:

[Sorry, skipped]

DM> XML has value. XML databases are at best a niche like pure object DM> databases and more likely a disaster.

I want to store XML docs in DB avoiding file system, source control system, etc. How can I work with documents as xml, but not as CLOBs without a bare support of xml technologies in DBMS?

DM> My main objection to XML in the database is the huge number of bytes DM> required to store a single byte of data:

DM> <someridiculouslylongtag>1</someridiculouslylongtag>

DM> and then you get to push this to the app server and off to the client.

One remark - somenonsensicaltag is the part of the data, the same important as figure 1. And I don't think _any_ db won't optimize the storage to persist the name of the tag in only one instance.

DM> XML was made for a purpose. It should be used for that purpose. Not DM> kludged into every nook and crany imaginable.

XML is the text. It sure is better to keep the data of any type in DB, and xml is not an exception.

With best regards, Alex Shirshov.

The data can be easily stored in relational form and the XML reconstituted by either the database or by the app server. Benchmarks I've seen support the proposition that reconstitution on the app server gives the best overall performance.

 

[Alex Shirshov]

Hello, DA!
You wrote on Wed, 27 Oct 2004 19:10:47 -0700:

[Sorry, skipped]

DM> The data can be easily stored in relational form and the XML DM> reconstituted by either the database or by the app server. Benchmarks DM> I've seen support the proposition that reconstitution on the app server DM> gives the best overall performance.

Don't be conning! Yes, xml docs with the regular form can be transformed to relational structure, but this is the only one case. The app need to be able to work with ustructured and semistructured xml documents and this is the primary use case. If you said, that xml docs without regular structure also can be easily transformed to relational form, than i will dare to claim you are not working with RDBMS. It's not easy to alter table each time you partner adds another extensibility element to your wonderful, semirelational xml format. I emphasize that storing semistructured xml documents into DB is the primary use case. And we should discuss the problem not only with relation to performance, but also extensibility, maintainability, complexity, etc.

With best regards, Alex Shirshov.

 

[HansF]

I want to store XML docs in DB avoiding file system, source control system, etc. How can I work with documents as xml, but not as CLOBs without a bare support of xml technologies in DBMS?

Just curious whether you have looked at the XMLType and the XML DB capability intrinsic to Oracle?

/Hans

---
Below here is reference and links to Oracle info: Since this is cross posted to a number of non-Oracle groups, for reference to those who have not looked at the material, the relevant documentation links:

Oracle docco:
http://docs.oracle.com

Oracle docco for Oracle9i Release 2:
http://www.oracle.com/pls/db92/db92.docindex?remark=homepage

Oracle docco for 9iR2 XMLDB: (warning - link is split) http://download-west.oracle.com/docs/cd/B10501_01/ appdev.920/a96620/xdb01int.htm

General info portal to Oracle XML capabilities:
http://www.oracle.com/technology/tech/xml/index.html

(I've only referenced 9i Release 2 even though Oracle10g is out and has many relevant improvements. Oracle has had XML support since 8i in the late 90's, and many don't like looking at the latest version.)

I've also dropped defunct newsgroups from the distribution. comp.database, comp.database.oracle, comp.database.oracle.server, comp.databases in general are not relevant or current newsgroups.

FInally, for those who don't have access to the docco because they have not registered with Oracle's TechNet, a snippet from the Oracle Concepts manual:

XMLType

This Oracle-supplied type can be used to store and query XML data in the database. XMLType has member functions you can use to access, extract, and query the XML data using XPath expressions. XPath is another standard developed by the W3C committee to traverse XML documents. Oracle XMLType functions support many W3C XPath expressions. Oracle also provides a set of SQL functions and PL/SQL packages to create XMLType values from existing relational or object-relational data.

XMLType is a system-defined type, so you can use it as an argument of a function or as the datatype of a table or view column. You can also create tables and views of XMLType. When you create an XMLType column in a table, you can choose to store the XML data in a CLOB column or object relationally.

 

[Gene Wirchenko]

Hello, DA!
You wrote on Wed, 27 Oct 2004 19:10:47 -0700:

[Sorry, skipped]

DM> The data can be easily stored in relational form and the XML DM> reconstituted by either the database or by the app server. Benchmarks DM> I've seen support the proposition that reconstitution on the app server DM> gives the best overall performance.

Don't be conning! Yes, xml docs with the regular form can be transformed to relational structure, but this is the only one case. The app need to be able to work with ustructured and semistructured xml documents and this is the

^^^^^^^^^^^ ^^^^^^^^^^^^^^
All data is structured, or it is just noise (and not data).

"semi-structured" is like "half-pregnant".

primary use case. If you said, that xml docs without regular structure also can be easily transformed to relational form, than i will dare to claim you are not working with RDBMS. It's not easy to alter table each time you partner adds another extensibility element to your wonderful, semirelational xml format. I emphasize that storing semistructured xml documents into DB is the primary use case. And we should discuss the problem not only with relation to performance, but also extensibility, maintainability, complexity, etc.

You are right. It appears you realise that the XML is difficult to work with. You should look at those factors.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

 

[Lemming]

Hello, DA!
You wrote on Wed, 27 Oct 2004 19:10:47 -0700:

[Sorry, skipped]

DM> The data can be easily stored in relational form and the XML DM> reconstituted by either the database or by the app server. Benchmarks DM> I've seen support the proposition that reconstitution on the app server DM> gives the best overall performance.

Don't be conning! Yes, xml docs with the regular form can be transformed to relational structure, but this is the only one case. The app need to be able to work with ustructured and semistructured xml documents and this is the primary use case. If you said, that xml docs without regular structure also can be easily transformed to relational form, than i will dare to claim you are not working with RDBMS. It's not easy to alter table each time you partner adds another extensibility element to your wonderful, semirelational xml format.

Nor is it easy to amend the programs which consume the wonderful[1], semi-relational xml format. My current client has a so-called "partner" who every week or two will send xml files containing new elements and/or attributes without telling my client's development team beforehand, and then can't understand why the files can't be processed.

The fact that xml is a chaotic, unstructured, bloated mess and that even apparently experienced xml-oriented developers can't understand that changing their file format has an impact on others is not the fault of the RDBMS.

[1] XML can be described as "wonderful" only for very low values of wonderful.
Lemming

 

[Alex Shirshov]

Hello, HansF!
You wrote on Thu, 28 Oct 2004 06:45:43 GMT:

[Sorry, skipped]

H> Just curious whether you have looked at the XMLType and the XML DB H> capability intrinsic to Oracle?

No, I'm mostly working with MS SQL. AFAIK, new xml data type in SQL Server
9.0 is almost akin to XMLType in Oracle.

[Sorry, skipped]

With best regards, Alex Shirshov.

 

[Oleg Tkachenko MVP]

^^^^^^^^^^^ ^^^^^^^^^^^^^^
All data is structured, or it is just noise (and not data).

"semi-structured" is like "half-pregnant".

Well, in fact semi-structured data in XML world is common term referring to XML documents having sort of variant and flexible substructure (usually traditional text documents, such as books or articles) whose content model can be described using complex union types, xs:choice's, & connectors, xs:any etc. Unstructured data in XML terms is probably DTD/Schema-less documents.

 

[Alex Shirshov]

Hello, Lemming!
You wrote on Thu, 28 Oct 2004 11:49:51 +0100:

[Sorry, skipped]

??>> Don't be conning! Yes, xml docs with the regular form can be ??>> transformed to relational structure, but this is the only one case. ??>> The app need to be able to work with ustructured and semistructured ??>> xml documents and this is the primary use case. If you said, that xml ??>> docs without regular structure also can be easily transformed to ??>> relational form, than i will dare to claim you are not working with ??>> RDBMS. It's not easy to alter table each time you partner adds another ??>> extensibility element to your wonderful, semirelational xml format.

L> Nor is it easy to amend the programs which consume the wonderful[1], L> semi-relational xml format. My current client has a so-called L> "partner" who every week or two will send xml files containing new L> elements and/or attributes without telling my client's development L> team beforehand, and then can't understand why the files can't be L> processed.

You client is right (as always). Formats are changing and your processors should be able to work with new documents. There is a great articles about xml formats evolution: Versioning XML Vocabularies and Designing Extensible, Versionable XML Formats. Versioning is the complex problem which involve both a good design of xml format and a good processor implementation. You should allow extensibility points and be ready to work with them. Otherwise you've got the headache indifferently the storage. My point of view is that it is very complicated to store "versionable" xml in relational form, because you don't know what to do with unknown attributes and elements. There is not mapping for them! Therefore you have to store those unknown fragments as plain text. Or as xml date type. I choose the latter.

L> The fact that xml is a chaotic, unstructured, bloated mess and that L> even apparently experienced xml-oriented developers can't understand L> that changing their file format has an impact on others is not the L> fault of the RDBMS.

I didn't claim that, the impossibility of RDBMS to work with xml is its fault. I claim, that RrrrrrDBMS eminently suitable for rrrrrelational structures, but not the "chaotic, unstructured and bloated mess" data. It is not its fault, it is behavior by design. I think it is better to use XPath or XQuery and native xml, than
1. transform data to relational form
2. query them
3. transform date to xml form

Note, that you also have to prepare for "relational approach" by creating tables for xml representation.

With best regards, Alex Shirshov.

 

[Gene Wirchenko]

Well, in fact semi-structured data in XML world is common term referring to XML documents having sort of variant and flexible substructure (usually traditional text documents, such as books or articles) whose content model can be described using complex union types, xs:choice's, & connectors, xs:any etc. Unstructured data in XML terms is probably DTD/Schema-less documents.

Oh, I know. I think that it is a lousy term.

Sincerely,

Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

 

[Gene Wirchenko]

[snip]

Nor is it easy to amend the programs which consume the wonderful[1], semi-relational xml format. My current client has a so-called "partner" who every week or two will send xml files containing new elements and/or attributes without telling my client's development team beforehand, and then can't understand why the files can't be processed.

The fact that xml is a chaotic, unstructured, bloated mess and that even apparently experienced xml-oriented developers can't understand that changing their file format has an impact on others is not the fault of the RDBMS.

[1] XML can be described as "wonderful" only for very low values of wonderful.

Like "won", I mean "one".

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

 

[DA Morgan]

Hello, DA!
You wrote on Wed, 27 Oct 2004 19:10:47 -0700:

[Sorry, skipped]

DM> The data can be easily stored in relational form and the XML DM> reconstituted by either the database or by the app server. Benchmarks DM> I've seen support the proposition that reconstitution on the app server DM> gives the best overall performance.

Don't be conning! Yes, xml docs with the regular form can be transformed to relational structure, but this is the only one case. The app need to be able to work with ustructured and semistructured xml documents and this is the primary use case. If you said, that xml docs without regular structure also can be easily transformed to relational form, than i will dare to claim you are not working with RDBMS. It's not easy to alter table each time you partner adds another extensibility element to your wonderful, semirelational xml format. I emphasize that storing semistructured xml documents into DB is the primary use case. And we should discuss the problem not only with relation to performance, but also extensibility, maintainability, complexity, etc.

With best regards, Alex Shirshov.

I would have to see a specific example of the semistructured XML to respond. But you should take a look at Oracle's object-relational capabilities if you are not already familiar with them.