Exploring XML Database Solutions

A list of links to Native XML databases.

Mysql Native XML functions
http://dev.mysql.com/tech-resources/articles/mysql-5.1-xml.html

Oracle 11g XML DB
http://www.oracle.com/technology/tech/xml/xmldb/index.html

MS SQl Server XML Support
http://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-whats-new-xml.aspx


An Oracle XML Example
At work most of my projects are using Oracle 10, and my latest project stores "process variables" exported as XML from Teamworks as clob types in oracle. On our dev server I did a quick test, created a new XMLType column and copied over the raw clob xml data into the new column using the oracle XMLType.createXML function. From there I was able to run some XPath like queries against the new column pretty easy. Below is a simplified example of doing just that.

NOTE: I ran this example on Oracle 10.2 XE as well as Oracle 10.2 Enterprise Edition. In both cases the XML functionality (what little I used) worked the same. It seems all version of the oracle product support XMLType columns.

--- create test table, store XML as clob for starters
CREATE TABLE MyProcessTable (
ID INT,
ClobXML CLOB
);

INSERT INTO MyProcessTable VALUES(1,'
<process>
<id>1</id>
<name>Test Process 1</name>
</process>
');

INSERT INTO MyProcessTable VALUES(2,'
<process>
<id>2</id>
<name>Test Process 2</name>
</process>
');

COMMIT;

SELECT * FROM MyProcessTable;

/* Query Output:
ID   CLOBXML
---- -------
1    (CLOB)
     <process>
     <id>1</id>
     <name>Test Process 1</name>
     </process>

2    (CLOB)
     <process>
     <id>2</id>
     <name>Test Process 2</name>
     </process>
*/

--- Add new XMLType Column
ALTER TABLE MyProcessTable ADD TrueXML XMLType;

--- Setting TrueXML from the string value of the CLOB column ClobXML
--- Convert clob value to XMLType
UPDATE MyProcessTable SET TrueXML = XMLType.createXML(ClobXML);
COMMIT;

--- Select all process names
SELECT EXTRACTVALUE(TrueXML,'/process/name') FROM MyProcessTable ;

/* Query Output:
EXTRACTVALUE(TRUEXML,'/PROCESS/NAME')
-------------------------------------
Test Process 1
Test Process 2
*/

--- Select process ID 2
SELECT * FROM MyProcessTable WHERE EXTRACTVALUE(TrueXML,'/process/name') = 2 ;

/* Query Output:
ID   CLOBXML                         TRUEXML                    
---- ------------------------------- ---------------------------
2    (CLOB)                                                      
     <process>                       <process>                  
     <id>2</id>                      <id>2</id>                  
     <name>Test Process 2</name>     <name>Test Process 2</name>
     </process>                      </process>                  
*/