Databases flex their XML

IBM, Microsoft, Oracle, and Sybase compete in our data management gymnastics

1 2 Page 2
Page 2 of 2

ASE does most of the XML data management for you. Building views, stored procedures, and functions based on XML data is not only easy to code, it's easy to manage as well. However, the lack of schema evolution will hinder growing projects. Lack of schema evolution isn't the only shortcoming in ASE. When creating XML files, ASE cannot perform on-the-fly XSLT translation. The end-user is expected to run the newly created XML files through an XSLT processor afterwards, which hinders any efforts to create XML files in a timely fashion. Any organization that needs to generate XML files on a regular basis should find another solution. (I find Microsoft .Net to be very good at this task.)

If it weren't for ASE's lack of XSLT and XQuery support, its XML implementation would be in the same class as IBM or even Oracle. Development was extremely easy, and the tools were very intuitive. Sybase has put together an excellent product that should be exploited by any company currently using ASE.

In the speed tests, ASE held its own except when it came to creating XML files, where performance was affected by my having to script XSLT processing. Had ASE included a method for using XSLT, it would have performed well in comparison with the others.

Microsoft Takes a Lesson

Microsoft SQL Server 2000 falls glaringly short of Oracle, IBM, and Sybase when handling XML. As an XML-enabled database, SQL Server only allows you to store XML documents as unstructured objects in the database or to shred the XML data into relational tables.

You might think that the absence of structured XML support would leave SQL Server dead in the water, but that's not entirely so. SQL Server has plenty of functionality that makes it possible to parse and write XML documents. Foremost, you can easily generate XML result sets in SQL Server using the For XML clause in a T-SQL query. This in itself is useful, but is made more so by the ability to scrub the data through an XSLT style sheet along the way.

SQL Server has other rich options for creating XML documents, allowing you to slice and dice views and present XML data in almost any form users require. You can also use SQL Server as a sort of bidirectional XML repository by mapping XML documents directly to relational tables through the use of Updategrams, which sit between the XML document and the database and allow you to specify what elements look like before and after a change. This allows you to send updates, inserts, and deletes to SQL Server in the form of an XML document. You receive the results in XML as well.

When reading XML documents, SQL Server gets even more sophisticated. For example, SQL Server can pass XML documents into stored procedures as parameters for shredding into the database. And SQL Server's OpenXML function allows you to generate rowsets from XML documents and insert them into the relational database. In fact, using OpenXML, you have access to some nicely featured methods for manipulating XML data. You can retrieve attributes and elements using flags, and you can define rowset schemas by using column patterns or table names.

However, because SQL Server treats the entire XML document as a single piece of data, the database is not aware that XML elements exist until you start parsing through them in the stored procedure. This isn't the end of the world. You can still build queries and views that leverage the hierarchical relationships of the data in XML files; it just takes a lot of extra work (read: coding) to do so. The next version of SQL Server, due in 2005, will support structured XML storage (see "Will Yukon strike XML gold?").

How well SQL Server will meet the demands of an XML project depends on what you need to do. If you merely want to import some XML documents into your relational database, and you're willing to lose the structure of your XML data, then SQL Server will suit you just fine. However, in this scenario, .Net may be a closer match. The .Net tools simply handle some things better, including the creation of XML files.

In my tests, I found that SQL Server speedily imported XML files, but it was the slowest of the group at creating XML files. It was also slowest at reading large XML files but not by a significant margin.

Relating to XML

All four of these relational databases provide useful XML capabilities. Even SQL Server, which lacks support for structured XML storage, allows you to store, manipulate, and create XML in useful ways.

While Microsoft has a lot of catching up to do, IBM DB2 and Sybase ASE suffer from less significant limitations. Their lack of support for XQuery means less granular XML search capabilities than the others provide. Sybase ASE's inability to perform XSLT impromptu transformations means extra time and administrator effort will be necessary to generate XML documents or evolve schema.

Although IBM, Sybase, and even Microsoft offer significant XML functionality, they trail Oracle by a wide margin. Oracle not only does the best job of hiding the complexity of managing XML data, it also offers the richest set of query capabilities and tops it off with extras such as support for schema evolution and WebDAV repositories. If you're using DB2, Sybase ASE, or SQL Server 2000, you'll be able to make good use of XML data.

Jennifer Nelson, database administrator at Info Integration, contributed to this article.

Copyright © 2004 IDG Communications, Inc.

1 2 Page 2
Page 2 of 2
How to choose a low-code development platform