XML in the database

By Adrian Smith3 Mar 2008500 words3 mins to read

As I do a lot of programming using open source databases, I couldn't help but notice that inclusion of XML in the database seems to be a hot topic. Postgres 8.1 (just released) and MySQL 5.1 (not released yet) will allow XML documents to be stored as the value of cells, and allow those documents to be manipulated and inspected. (Oracle has had this feature since 2001)

I generally don't like XML (as Robin put it: most formats are designed to be either conveniently readable by humans, or by computer. XML is conveniently readable by neither.)

So I especially wouldn't like to store XML in the database. However, there are some times when one would naturally want to store some hierarchical data and the relational model doesn't really allow that conveniently. Compared to:

I suppose using the XML features provided in the database is better than either of the above solutions.

The following table contains documentation copied from the documentation or the changelog of the database in question:

New in Postgres 8.3
(4th Feb 2008)

"XML Support: New XML data type fully supports the SQL/XML specification of ANSI SQL:2003, including well-formedness checks, type-safe operations, SQL/XML publishing and XPath queries. Version 8.3 also includes additional functions for XML data export."

New in MySQL 5.1
(In development)

"XML functions with XPath support. ExtractValue() returns the content of a fragment of XML matching a given XPath expression. UpdateXML() replaces the element selected from a fragment of XML by an XPath expression supplied by the user with a second XML fragment (also user-supplied), and returns the modified XML. See Section 11.10, 'XML Functions'."

New in Oracle 9.0.1
(Released 2001)

"The introduction of Oracle XML DB and the XMLType datatype provides new techniques that facilitate the persistence of XML content in the database. These techniques include the ability tostore XML documents in an XMLType column or table, or in Oracle XML DB Repository. Storing XML as an XMLType column or table makes Oracle Database aware that the content is XML. This allows the database to: Perform XML-specific validations, operations, and optimizations on the XML content; Facilitate highly efficient processing of XML content by Oracle XML DB."

New in MS SQL Server 2005
(Released 2005)

"SQL Server 2005 introduces a native data type called XML. A user can create a table that has one or more columns of type XML in addition to relational columns. ... Together with a large set of functions, embedded XQuery and data modification languages provide rich support for manipulating XML data."

This article was written by Adrian Smith on 3 Mar 2008

Follow me: Facebook | Twitter | LinkedIn | Email

More on: Databases | MySQL | Oracle | PostgreSQL | SQL Server | Software Architecture | Coding