Ok, seriously? You're asking an RDBMS to index into XML data you've shoved into a column?
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.
Apparently you don't understand how it actually works. At least in the case of DB2, the database engine doesn't store a "verbose textual representation" of XML documents. It breaks them down into a more efficient internal binary format that still preserves the structure.
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.
"Apparently you don't understand how it actually works."
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.
Comments
Ok, seriously? You're asking an RDBMS to index into XML data you've shoved into a column?
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.
Apparently you don't understand how it actually works. At least in the case of DB2, the database engine doesn't store a "verbose textual representation" of XML documents. It breaks them down into a more efficient internal binary format that still preserves the structure.
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.
"Apparently you don't understand how it actually works."
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.
Apparently you've never worked with complex healthcare data. If you can figure out a simpler way be sure to let us know.