Can XML columns in Oracle/MS SQL Server replace document oriented databases to some extent?
1
vote
0
answers
191
views
Just a strategic question: I am currently working with frequently changing data schemas and it takes a lot to keep track of versions and specific content.
I do, however, have a classic OLTP application, which requires ACID, transactions, joins, single-point-responsiblility; plus, the DB load seems not to be extreme, so a document oriented ("no SQL") database seems not to be a good solution.
I currently have numerous objects/tables with XML documents in clob/varchar(max) columns, the database being completely ignorant of their content. Also, many don't contain "real" XML, but simply a collection of XML elements with content. In fact, I use the XML columns much like noSQL documents.
As I have read, both Oracle and SQL Server have a kind of XML columns which is not bound to a schema, effectively stored in clob/blob style, but the database can have indices on certain content, such as an XPath to a certain XML element which frequently occurs and contains important data. Much like document databases can index documents by content, which not even every document may contain. They even can handle simple XML element collections, which are not true XML documents, if I understood right.
Looks like I can use some features of document oriented DBs through XML, living without a fixed schema, but still making parts of the XML data structure visible to the DBMS and it's clients without parsing the XML on client side. For example, I can *SELECT* all *OrderItems* which have a 2 year warranty stored somewhere in the XML, without a badly performing and possibly wrong "*where Terms LIKE '%2%'*".
Any experiences with this?
BTW, the XML is mostly read-only, few costly updates, most variable data is still in classic relational columns.
Asked by Erik Hart
(510 rep)
Oct 30, 2013, 02:05 PM