XML - Managing Data Exchange/Database and XML

Native XML Database
The term Native XML database: has become popular since 1999, after the company Software AG released the first version of its native XML server Tamino, which included a native XML database. A definition of a native databases is that it:

"[d]efines a (logical) model for an XML document and stores and retrieves documents according to that model." (Bourret, 2002)"

To model data in XML, two principle approaches are used: Data-centric documents and Document-centric documents.


 * Data-centric documents (for data transport) have fairly regular structure, order typically does not matter, and little or no mixed content.


 * Document-centric documents (usually for human consumption) have less regular or irregular structure, significant order of the elements, and lots of mixed content.

Examples of Native databases

eXist
eXist is an Open Source effort to develop a native XML database system, tightly integrated with existing XML development tools like Apache's Cocoon. The database may be easily deployed, running either standalone, inside a servlet engine, or directly embedded in an application.

Some features that are available in eXist and that can be found in most Native XML databases are :


 * Schema-less storage - Documents do not have to be associated to schema or document type, meaning they are allowed to be well formed only.


 * Collections - A collection plays a similar role to a directory in a file system. When submitting a query the user can choose a distinct part of the collection hierarchy or even all the documents contained in the database.


 * Query languages - The most popular query languages supported by Native XML databases are XPath (with extensions for queries over multiple documents) and XQuery.

Relational Databases
Database vendors such as IBM, Microsoft, Oracle, and Sybase have developed tools to assist in converting XML documents into relational tables.

Let us look at IBM and Oracle:

IBM Technology
DB2 XML Extender provides access, storage and transformation for XML data through user-defined functions and stored procedure. It offers 2 key storage models: XML Colums and XML Collections.

1. XML Column: stores and retrieves entire XML documents as DB2 column data. Use of XML Columns is recommended when XML   documents already exist and/or when there is a need to store XML documents in their entity.

2. XML Collection: composes XML Documents from a collection of relational tables.

A data access definition (DAD) file is used for both XML Column and XML Collection approaches to define the "mapping" between the database tables and the structure of the XML document.

 Specifies that the XML data is either to be decomposed from XML documents into a collection of relational tables, or to be composed into XML documents from a collection of relational tables.

The DAD file defines the XML document tree structure, using the following kinds of nodes:


 * root_node - Specifies the root element of the document.
 * element_node - Identifies an element, which can be the root element or a child element.
 * text_node - Represents the CDATA text of an element.
 * attribute_node - Represents an attribute of an element.


 * XML for DB2 Information Integration Redbook.

Oracle
Oracle's XML SQL Utility (XSU) uses a schematic mapping that defines how to map tables and views, including object-relational features, to XML documents. Oracle translates the chain of object references from the database into the hierarchical structure of XML elements.

A corresponding XML document generated from the given object-relational model looks like:

XSU can be used for executing queries in a Java environment and retrieve XML from the database.

XPath
../XPath/ is a language for addressing parts of an XML document, and is the common locator used by both XSLT and XPointer. An XPath expression is a series of location steps separated by " / ". Each step selects a set of nodes that become the current node(s) for the next step. The set of nodes selected by the expression are the nodes remaining after processing each step in order.

XQuery
../XQuery/ is a query language under development by the World Wide Web Consortium (W3C). The ambitious task is to develop the first world standard for querying Web documents. XQuery is a versatile markup language, capable of labeling the information content of diverse data sources including structured and semi-structured documents, relational databases, and object repositories.


 * Catalog of XQuery Processors at www.SQLSummit.com.

MySQL 5.1
MySQL has a command line utility for executing queries against a MySQL database; it has an option for using XML as their output format. MySQL also allows convertion to XML; more information can be found in ../Converting MySQL to XML/ MySQL allows users to execute any SQL query. mysqldump allows users to specify which tables to dump and to specify a where clause to restrict the rows that are dumped. In its Beta release of MySQL 5.1, several features have been added including new XML functions.

In order to understand these New functions, we will use the following table:

XML Functions
MySQL version 5.1 has functions for searching and changing XML documents: ExtractValue and UpdateXML.

This function takes 2 string arguments: The first parameter correspond to the XML_document string, and the 2nd Parameter XPath_string (XPath expression / locator). This will result in the return of the string containing a value from the document.
 * EXTRACTVALUE (XML_document, XPath_string);

mysql> SELECT EXTRACTVALUE(doc,'//firstname') FROM Customers;

+--+ +--+ +--+ 3 rows in set (0.01 sec)
 * EXTRACTVALUE(doc,'//firstname')         |
 * John                                    |
 * Aminata                                 |
 * Lamine                                  |

mysql> SELECT ExtractValue(doc,'/person[@id="3"]/firstname') as fname FROM Customers;

+-+ +-+ +-+ 3 rows in set (0.02 sec)
 * fname  |
 * Lamine |
 * Lamine |
 * Lamine |

This function takes 3 string arguments: The first two paramaters are similar to the ones used with extractValue, XML_document and XPath_string. The third parameter is the new value that will replace the one found. This function will then returns the changed XML.
 * UPDATEXML (XML_document, XPath_string, new_value);

mysql> SELECT UpdateXML(doc,'/person[@id="3"]/phoneno', ' 111-2233 ') FROM Customers;

+--- +                                                   | +--- +      John Smith 123-5678         |      Aminata Cisse 123-5679      |      Lamine Smith 111-2233 | +--- + 3 rows in set (0.00 sec)
 * UpdateXML(doc,'/person[@id="3"]/phoneno',' 111-2233 ')

Installation
Currently (04/05/06) MySQL 5.1 does not come with the installer (Beta Version).

Quick Windows installation.

Details information can be found in the online Manual:


 * Windows.


 * Mac OS.


 * Linux.


 * and more in the Manual.