602SQL Development and Management Client

XML Format and Support in 602SQL

602SQL is equipped with an extension supporting the XML format - this functionality is subject to activation after purchasing the license.

The XML support in some form is a feature found in all modern database systems. To store XML into a database and following access to data, two basic ways are used:

insertion of the whole XML document into one BLOB or CLOB;
parsing of the XML document to individual elements and their insertion into individual columns of the suitable tables.

602SQL implements (and this documentation describes) only the second way. Of course, it is possible to insert the XML document into a LOB (the first way), however, the server does not provide any support for data contained in the XML document (sorting, searching etc.). It would be important only if the database would serve as XML document store and actual data processing would be executed by an external layer.

XML and Relational Databases

Unlike other text formats, the XML format may capture data no matter how the data is structured. Therefore, it is very useful for storing data linked with a relational database.

Data relations are usually presented differently in the XML file than in the relational database. Elements are nested in other elements in XML, while record relations in a relational database are made according to identical key values. Therefore, transformations are done during data transfer.

Data Transfer between XML and the Database

Advantages of transferring data in the XML format:

Generate a structured database output that can be used (after XSL transformation) as HTML pages for web browsers, WML pages for mobile devices, print layouts, etc.
Guarantee data transfer between different systems, such as a database with a different structure.
Guarantee connection to external applications using an open data transfer format.

In order to fulfill these demands, a simple import and export between a database table and a file is not sufficient. It is required to first create a map that describes the transformation of the data structure. That map is called DAD.

Data Transformation Possibilities during Transfer

The transfer description in DAD allows the following:

Elements and attributes in the XML file may have any name and do not need to be related to the names of database table columns.
Database data may correspond with text values in suitable elements (<item_id>1</item_id>) or with suitable attributes (<ORDER item_id="1"/>).
Values transferred between XML and the database can be converted using SQL functions.
Items in the XML file can also be transferred to and from variables defined in an application.
Identical values that repeat themselves in the database may correspond with data suppressed in XML (to the higher level in the nested element structure).
Related records in the database correspond with nested elements in XML. The 1:N relation represents nesting N element occurrences into 1 element of a higher level. However, the value placement in XML does not have to fully respect relational dependence (some values can be suppressed higher or nested lower).
Key values that relate records may be either transferred in XML or generated during import.
Records may either be inserted or updated according to the specified key values when importing data from XML.
Record selection for export can be governed by setting client variable values and using them in conditions.

The relationship between a database data structure and the XML file follows these two principles: relation of N records from another table corresponds with nesting N occurrences of an element containing data from the record. The repeated data may be suppressed to a higher level. The first principle is used more frequently in analytic DADs and the second in synthetic DADs.

ODBC Data Sources

Methods of working with XML data described here can be also utilized for external data sources that are linked via the ODBC interface. The main limitation of access via ODBC is a fact that actual execution of XML transfers from/to ODBC must be done only from the client interface (Development environment, PHP XML API or CDK), while transfers from/to the 602SQL server are possible not only from the client side, but also from the SQL server side using the SQL language. 602SQL server itself has not implemented the ODBC interface. Further limitations are described here.

Operating Systems

UTF-8 and UTF-16 encoding is used for XML. Conversion from/to this encoding are not supported on Windows 95 and Windows NT 3.5, therefore the XML extension cannot be used on these operating systems. This is only supported on Windows 4.0/2000/XP/2003.

XML Support Implementation

The XML extension is implemented in the 602xmlXY.dll library (where XY is the version). This library uses the xerces-c_2_*.dll library distributed under the Apache license.

List of topics:

SQL Language in 602SQL

Obsah

602SQL Development and Management Client