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 '%<warranty>2</warranty>%'".

Any experiences with this?

BTW, the XML is mostly read-only, few costly updates, most variable data is still in classic relational columns.

  • sounds like the kind of thing that may work but setting up for a fall later. I would keep the blob but data you need to work on in a proper column.
    – JamesRyan
    Commented Oct 30, 2013 at 14:28


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.