Storing XML as a string is really not very useful since you can't query into the document contents. You also can't create indexes on particular elements or attributes for fast access.
Microsoft has a good explanation of why it isn't necessarily appropriate to store XML structures in a conventional RDBMS. I'll quote it here:
"The XML data model has characteristics that make it very hard if not practically impossible to map to the relational data model. XML data has a hierarchical structure that may be recursive; relational databases provide weak support for hierarchical data (modeled as foreign key relationships). Document order is an inherent property of XML instances and must be preserved in query results. This is in contrast with relational data, which is unordered; order must be enforced with additional ordering columns. Re-assembling the result during querying is costly for realistic XML schemas that decompose the XML data into a large number of tables."
http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).asp...
Quoting Microsoft & IBM on database design is like quoting GM & Chrysler on vehicle design. All are years behind and focused on an under-informed and declining customer base.
2 things:
1. Postgres supports both Regular Expressions and indexing on expressions. If you can't index your table on a regex expression, you are probably doing something wrong.
2. Reconstructing full XML documents from a single column based on a query is a lazy anti-pattern. Either a) store the document in a way that it can be retrieved on demand, or b) structure the data and the query in a way that is performant. Jamming a verbose textual representation of data into a single column of a table and then demanding performance is a fundamental misunderstanding and misuse of an RDBMS table.
I deal with complex XML documents that go a dozen or more levels deep. It would certainly be possible to represent that data in a relational structure. But then to query based on the value of a deeply nested element would require a large number of table joins. Keeping everything in a single XML column is much simpler and faster, since the specific inner element values can be indexed.
Hierarchical databases have been around for years, and actually predate relational databases. XML databases are just a specialization of hierarchical databases. Some applications are a better fit for the relational model and some are a better fit for the hierarchical model. With hybrid databases you get the best of both worlds and can choose the right tool for the job.
Man, nope. No disrespect but if you are regularly querying 12 levels deep into an XML file you have stored in your database then IMO you are almost certainly doing something horribly, horribly wrong.
MS SQL Server, since version 2005, has had the ability to do XPath queries on columns containing XML data. In an optimised manner. That's very handy in the average enterprise system that contains mounds of XML data in different schemas - you'd have an explosion of tables if you tried to store all that using conventional RDBMS design.
In my documents table, I have a field called "title". I have an after save hook in my application code to update this field.
If I had an XML-aware database, I could:
"SELECT id, //node[@type='title'] AS title, /@version AS version FROM documents WHERE version=2;"
This is more elegant than creating extra columns and hooks to support future queries, and mutilating your table with tens of extra columns.
SELECT id, xpath('//node[@type=''title'']', doc) AS title,
xpath('/@version', doc) AS version
FROM documents WHERE version=2;
(assuming your "documents" table has a "doc" field of type "xml")Documentation here: http://www.postgresql.org/docs/8.3/interactive/functions-xml...
I think the place you lose is if you have some arbitrary XPath expression for which you haven't constructed a DB index yet, and you want to search on that expression.