Copyright ã 2006 Yevgeniy Guseynov

        All rights reserved

 

Universal XML Interface to Database

 

Yevgeniy Guseynov

Optimal Solutions & Technologies,

2001 M Street, NW, Suite 3000,

Washington, DC 20036, USA

Ph.: 443 465 6587

Fax: 202 466 8117

Email: gyevg@yahoo.com

Abstract

Many modern technologies in distributed computing, database applications, and Web Services are relying on the efficient exchange and presentation of information in XML format and database vendors such as IBM, Microsoft, Oracle, and Sybase have developed proprietary specialized tools to present information from relational tables as XML documents. In general, these tools are extensions to Structured Query Language (SQL) with embedded new XML functionality or specialized software for processing XML templates with SQL queries. This paper presents vendor-neutral Universal XML Interface to Database (UXID) to process XML templates to extract data from data sources and present them as XML documents. UXID is a layer above any universal data access technologies like ODBC, JDBC, OLE DB, ADO.NET, or others that provide API to expose data sources in tabular form called rowsets. UXID is based on a Contiguous Memory Tree formatting scheme and its rich API (including SAX event processing and DOM API) that completely resolves Parsing and Processing Efficiency, permitting an efficient interchange format for XML which is crucial for database applications. Particularly, if extracted hierarchical data need to be stored or exchanged with other applications, they would be copied as a stream to disk or any media and copied back without serializing and parsing, to be instantly accessed by application. The transformation of relational data by UXID uses Query Translation Schema (QTS) as the only input that users should prepare to build a desired XML document. The QTS is based on two simple templates and describes transformation and formatting rules to build XML output. If database tables had changed or data to be extracted has to be changed, only the QTS needs to be rewritten, but UXID itself remains intact.

 

Key Words

XML Document, Relational Data, Data Extraction, Data Exchange, XML Processing Efficiency

 

1. Introduction

Relational Databases have become the primary tool for data storage and retrieval systems. In addition, modern data-intensive applications require the integration of information stored in file systems, indexed-sequential files, desktop databases, spreadsheets, electronic mail, directory services, multimedia data stores, spatial data stores, and other data sources. Evolved powerful strategies such as Microsoft Universal Data Access [7] or IBM Universal Database [10] provide solutions for distributed, multiplatform client/server and Web-based data-driven applications to define, query, modify, and control the data in universal way and make all types of data sources easy to access and maintain by vendor independent applications. Based on these strategies, universal API to data sources exposes information uniformly, using a common abstraction called the rowset [1]. A rowset is the unifying abstraction that enables API to expose data from data sources in tabular form. Conceptually, a rowset is a multi-set of rows where each row has columns of data. Query processors present the result of queries in the form of rowsets. Basic functionalities that rowset objects have are the iterating through rows in the rowset sequentially, accessing data in a row, and providing information about the columns of the rowset.

A rowset is self-describing object that contains data and metadata, created by executing a query against the data source. Because of its self-describing nature, rowset information can be easily represented in a descriptive and flexible language such as XML to provide the hierarchical view of data and support a streaming and persisting format for rowsets. Extensible Markup Language, abbreviated XML, was defined in the XML 1.0 Specification [2] published by the Worldwide Web Consortium (W3C). It identifies a class of data objects called XML documents and partially describes the behavior of computer programs which process them as an XML Processor. XML documents are well-formed, meaning in particular, that components that constitute the documents are also XML documents, are extensively self-descriptive, and allow an XML processor to read XML documents to provide access to their content, structure, and all information inside the documents. These XML features, along with the well defined syntax and the textual encoding, allow the formation of the rich structure of information and data that can be examined by people, and exchanged and interpreted by computers in a self-documented, self-describing, and extensible way.

Many modern technologies in distributed computing, database applications, and Web Services, particularly SOAP messages, are relying on the efficient exchange and presentation of information in XML format and database vendors such as IBM, Microsoft, Oracle, and Sybase have developed proprietary specialized tools to present information from relational tables as XML documents. In general, these tools are extensions to Structured Query Language (SQL) and based on newly developed standard SQL/XML [15]. Implementations have built in database SQL new XML functions - XMLELEMENT, XMLATTRIBUTES, and others to query relational data and return XML documents [12, 14].       

Another approach to create XML documents from queries is based on templates where a database vendor offers specialized software to build and process XML templates. A query to extract data from a database is embedded in the template that presents the resulting rowset table columns as elements in the XML document. This XML template is used by specialized API to query the database, retrieve the results in XML format, and use the results in an XSLT processing to create the required XML document [14].

This paper presents a Universal XML Interface to Database (UXID) to process vendor-neutral XML templates to extract data from data sources based on universal data access and present them as XML documents. UXID is a layer above any universal data access technologies like ODBC, JDBC, OLE DB, ADO.NET, or others. Hierarchical Row Set (HRS) API and Data Access Programming Interface (DAPI) constitute UXID. HRS uses few methods to connect to the database, execute SQL statements (queries), get information about the column from the query, and convert the data from SQL type to a given type and back. These methods are included in DAPI, a uniform interface for different software makers' connection to databases, and their implementations wrap appropriate methods from particular data access technology that is used by applications, but DAPI itself as interface, is general for all of them. HRS comprises methods enabling the user to query relational data, extract information, and build desired XML documents to present or exchange them with other applications or users.

In some database applications, when document size and processing time are the issues, the advantages of XML usage are tempered by the inefficiencies that stem from the original textual encoding [8]. The main properties considered lacking with XML for a potentially efficient interchange format are Compactness and Processing Efficiency and Parsing, the step where components of an XML document are transformed from a stream of text data to application objects, being the main deterrent to Processing Efficiency. Although there are many parsers available on the market, it still remains the obstacle in processing XML documents [4, 11, 13]. The Contiguous Memory Tree (CMT) and its Memory XML API completely resolve Parsing and Processing Efficiency, permitting an efficient interchange format for XML [5]. CMT is based on the presentation of XML documents as a tree that contiguously resides in memory, and is simultaneously a stream that can be directly copied as a message as well as an application object that can be directly accessed through the Memory XML API. It provides methods for development of robust applications to manipulate various XML documents without initial parsing or serialization.

HRS extends Memory XML API [5] and inherits all its advantages against other formatting schemes for XML processing, like [4, 11, 13], that are crucial for database applications. Memory XML API and HRS have all the functionality of the SAX parser [9] and DOM Interface [6] and in addition, does not need to read and evaluate markup or decode information items, and thus is significantly more efficient than any known parser by the elapsed time that a parser needs to parse or search an XML text.

HRS API transforms each row set into a CMT hierarchy and then produces different XML presentations of extracted data. As with any XML transformation, it is based on processing templates [3], in this case, Query Translation Schema (QTS), which is the only input for API to run a query and build XML document based on the result set. There are only two simple templates to build any QTS as deep as necessary and the process to write QTS based on queries is described later in this paper.

HRS API is also useful for Embedded SQL category interfaces where SQL statements are incorporated into the source code of an application. In this case QTS cannot be used, but embedded into the application SQL statements should extract data directly into HRS CMT and then XML documents could be built based on HRS API.

UXID that comprises HRS and DAPI is vendor independent way to extract information from databases in XML format regardless of operating systems and programming languages like C++ with direct access to memory or Java, Perl, Visual Basic, and others with the ability to contiguously allocate arrays in memory. If extracted hierarchical data need to be stored or exchanged with other applications, they would be copied as a stream to disk or any media and copied back without serializing and parsing, which is the main feature inherited from CMT. Another major advantage comes from independence of HRS API to any database structure. If database was changed or data to be extracted has to be changed, only QTS needs to be rewritten but HRS remains intact.

To verify UXID functionality a sample project was built in Microsoft Visual C++ 6 environment with ability to use any QTS and database that has ODBC driver to run extraction process and build desired XML documents. This project available from the author upon request via email and users may try their own schemes and databases to extract relational data in XML format.    

 

2. Query Translation Schema

Query Translation Schema (QTS) is the only artifact that users should prepare to run the extraction process and build desired XML documents with UXID. A transformation expressed in QTS describes rules to execute a query and transform the row set into a CMT hierarchy. For our consideration, we will use QTS samples from Appendixes A, B, and C. Sample queries in these QTSs are from Microsoft Access sampdata.mdb that is included in the UXID sample project. QTS elements <Query>, <Statement>, <Table>, <Columns>, and <Rows> are instructions for the HRS API and element tags tell API how to process them. The basic templates in QTS are <Query> and <Table>:

 

<Query>

   <Statement/>

   <Table/>

               

</Query> ;

 

<Table TNAME=”TableName”>

   <Columns>

      <Column1 KEY=”someKey” CFORMAT=”NO” />

      <Column2 CFORMAT=”attributeName” />

     

   </Columns>

   <Rows/>

  

</Table>

 

Each <Query> template has one <Statement> element and at least one <Table> element. The value of the <Statement> element is the text of the query to be executed. The SQL SELECT statement queries data from tables in the database and specifies a list of columns and expressions to be retrieved from the tables. All top-level <Table> elements for <Query> are independent from each other. Columns and expressions from the query are distributed, in the order in which they appear, between its <Table> children and grand children.

Each <Table> template has at least two children: <Columns> and <Rows>. <Columns> children Column1, Column2, … correspond to columns and expressions from the query list in <Statement>, and the element <Rows> collects the result from the query row set in hierarchical order. <Table> may have one or many <Table> elements as children positioned after <Rows>. In this case, the parent <Table> has key elements that are indicated as attribute KEY in the corresponded <Columns> elements. All <Table> elements have the attribute TNAME. <Table> may also have children <Query> elements positioned after <Rows> element. A child <Query> has input parameters that are values from the parent <Columns> elements that are indicated in the <Statement>.

These two basic templates are the building blocks to write QTS for queries. Once the queries have been tested and the structure of the output XML document has been determined, we may start to write QTS to run extraction and formatting processes. We may always reorder the queries list of columns and expressions to facilitate building the XML hierarchy with QTS. The first section in QTS defines the root element <RootQuery>. The root element of QTS is not an instruction and it may be named to reflect the output XML document. The root element may have several independent <Query> elements on the top level. The query text should be the value in the <Query> child <Statement>. Next, we have to map the query list of column names and expressions to <Table> elements and sub-elements in the order they appear in the list. It mainly depends on how we would like to format the output XML document and hierarchical dependencies between list items. Like in any canonical table-based or result set mapping [12, 14], the names of the child elements for <Columns> map to the query list of columns and expression names in the order they appear in the result set. The names of the child elements in the <Columns> do not necessarily have to match the row set names and naming should be based on the output XML document construction, but the order for how names appear in the result set and QTS is essential.     

There are two ways to implement hierarchy in QTS, based on relation and parameters. The first way models the relation for two rows via an equality predicate between key column values. A parent <Table> and a child <Table> create the relation hierarchy. For this case, the first part of the query list of columns and expressions constitutes the parent <Table> <Columns> and the second part, the child <Table>. If key columns values in any two rows from row set match then the child rows from child <Table> <Rows> has the same parent row from parent <Table> <Rows>. This parent-child relationship between <Table> elements may be repeated at any level if the query has several tables with key columns.

The QTS example in Appendix A presents the relation hierarchy. In the first query, the item list consists of CategoryID, CategoryName, ProductName, ProductSales columns and the column CategoryID is the primary key in the table Categories. Items from the query list were split into two <Table>s in QTS: <Table> Categories with columns CategoryID and CategoryName and <Table> ProductSales with columns ProductName, ProductSale. <Table TNAME=“ProductSales”> is child in the <Table TNAME=“Categories”> and presents one-too-many relationships in a row set, because we do not want to repeat the rows with the same CategoryID in <Table> ProductSales.

Another type of hierarchy in QTS models relation between the parent <Table> and its child <Query> based on parameters for when a child <Query> statement has some parent <Table> columns as input parameters. After a <Query> statement for parent <Table> is executed, the child query should be run for each row from the parent query, with parameter values from the parent row. This parent-child relationship between the <Table> element and its child <Query> may be repeated at any level with parameter values from the parent and grandparent rows.

The QTS example in Appendix B presents the parameterized hierarchy. It produces the same output XML document, but has a different structure. The query that we discussed in the relation hierarchy is split here into two, where the first query is processed with <Table> Category and the second is the child query in this element. The child query has an input parameter that is prefixed by the @ sign, followed by the corresponding child name from the <Columns> in the <Table> Category. This syntax tells the HRS API that child query in the <Table> Categories should run for each row from the first query and use the value CategoryID from this row as the input parameter. The choice between two structures of QTS in Appendixes A and in B depends on implementation and is based on queries performance or other reasons to split the parent query.

Another component in QTS is formatting rules to produce a desired output XML document. Like standard-based functions XMLELEMENT, XMLATTRIBUTES, and others [12, 14] they transform a value from a row set into an XML element or its attribute. By default, all values from the row set are presented in a formatted XML document. The attribute CFORMAT=”NO” in a <Columns> child in QTS indicates that we do not want this element to be included in the output XML and it would be skipped when formatting. Also, if we want that <Columns> child to be written as an attribute in the <Table> element, we present it in QTS like <Column2 CFORMAT=”attributeName” />. Here attributeName is our choice for the attribute name in the output XML document and the Column2 value we want to be presented as attribute value. Two different formatting schemes for the same row set hierarchy are presented in Appendixes B and C. 

All described rules are interpreted in the HRS object when it is built.

 

3. Hierarchical Row Set

The HRS API transforms the data from relational database into an HRS hierarchical structure and presents them in XML format based on given QTS. Its main functionalities are implemented in the methods:

- buildCMT(char* document): interprets QTS to build HRS object as the base structure for processing;

- translate(): executes queries and transforms result sets into hierarchical structure;

- writeXML(char* document): writes a required XML document based on formatting rules built in HRS object from QTS.

HRS that extends Memory XML API inherits all its features, such as equivalently fast the SAX event processing and DOM API. Like Memory XML API, HRS is based on Contiguous Memory Tree (CMT) defined by three arrays [5]:

The array of integers, Hierarchy[], to hold the hierarchical (tree) structure of the document;

The array of characters, SchemaComponents[], to hold tag names, attribute names, and other components for all documents with the same XML schema;

The array of characters, DocumentValues[], for each document to hold elements and attributes values for the whole document.

In addition to these three arrays that were inherited from CMT, the HRS has one more:

The array of characters, RowSet[], to hold extracted data from database in one buffer.

These arrays contiguously reside in memory and can be directly copied to a disk and brought back to be instantly accessed by an application without parsing or serialization steps. This significant advantage Hierarchical Row Set Contiguous Memory Tree (HRS CMT) against other formatting schemes, coupled with direct access to any node in the CMT schema without navigating a tree structure. To find a node on the tree and use its attributes and values, an application has to navigate the tree to compare the nodes with a given one. After HRS CMT or Hierarchy[], SchemaComponents[], DocumentValues[], and RowSet[] arrays were built, they remain unchanged, even when they are stored, and we may write out starting positions for their elements as constants and make them available for applications for direct access or fast search.

3.1. Building HRS CMT

QTS describes the rules to run the extraction process and build desired XML documents in human readable standard textual format. In order for an application to access the content of a XML document, it must be interpreted as application objects. Method buildCMT(char*) takes QTS as an input, interprets translation and formatting rules, and builds an HRS CMT object. This step should be done only once. The process to build CMT or arrays Hierarchy[], SchemaComponents[], DocumentValues[] described in [5]. Based on the main properties of CMT after HRS CMT is built, it may be copied to a disk or used by application to have instant and direct access to all elements and information without parsing, which is much more efficient than parsing original QTS again.

buildCMT() also verifies queries and explores metadata to find platform dependant size and type for all <Columns> children elements. Extraction from database are kept without changes in RowSet[] buffer, while size and type of each column would be used by DAPI to calculate the relative position in memory to locate and provide the value of the column from the RowSet[] to the application.

3.2. Transformation process

Once HRS CMT for any particular QTS is built, the application may execute translate() method to extract data from database into HRS CMT hierarchy. First, an application should instantiate an HRS object and copy the HRS CMT from the disk if it is not in memory. Now it is instantly ready to run translate() method that recursively processes HRS CMT by repeatedly calling getNextEvent(bool &startTag) method. Like in any SAX event, processing this method allows one to navigate whole XML tree structure by repeatedly calling it and returns startTag = true if it encounters the start of an XML element, otherwise it is false. The transformation process also uses methods from DAPI described later.

As mentioned above, there are two basic templates to be processed.

 

Processing <Query> template.

1. When translate() processing encounters an element <Query> with StartTag = true, it passes control to its child <Statement>  to execute the method executeQuery(qDex, stmt) from DAPI, where stmt is the query statement and qDex is the query handle. Values stmt and qDex are stored in <Query> element for subsequent use. Before call executeQuery translate() sets all parameters in the stmt using values from the parent rows that have already saved.

2. At this point in the <Query> element translate() executes DAPI::getRow(rowPosition, qDex) to copy a row to the buffer rowPosition from the query row set with handle qDex that was saved in the <Query> element. All row sets from QTS queries, row by row are to be copied directly into a buffer RowSet[] in the HRS object. When translate() repeatedly calls getRow the char *rowPosition is the position in the buffer RowSet[] that is available to write the next row from a rowset. This approach has two advantages: the application does not need any additional transformation to use the data from queries output and the buffer RowSet[] contiguously resides in memory and if application needs to store row sets it simply copies buffer with whole HRS CMT to the disk.

3. If DAPI::getRow returns no row, thus all rows were processed or there was no row in the row set, translate() sets startTag = false and calls getNextEvent(startTag) to go out from this <Query> element.

4. If DAPI::getRow returns a row translate() copies it in the buffer RowSet[] and passes control to the first element <Table> for further processing as a <Table> template.

5. When all <Table> elements from <Query> were processed the method getNextEvent(startTag) sets the <Query> element and returns startTag = false. At this point translate() completed processing the current row from query result set and returns execution flow back to step 2 to get new row.  

 

Processing <Table> template.

Processing  <Table> template builds two hierarchies in HRS CMT: rows in the <Table> elements as database table and parent-child relationship between rows from all row sets from QTS queries. The Root Row element for second hierarchy is stored in HRS object when it was built. Parent Row for each <Table> element is set by translate() before each <Table> template is to be processed. For top-level <Table> element in QTS Parent Row is set to Root Row.

The row from a row set is in the buffer RowSet[] and the part of row to be processed by this <Table> template is at rowPosition in the buffer. The size of this part derives from the sizes of the <Columns> elements and was saved in the <Table> element when HRS CTM was built.

1. When a translate() process encounters an element  <Table> with startTag = true and there is no primary key among <Columns> children it adds new child element to <Rows>. Added element points to rowPosition, the part of the row in the buffer RowSet[] that is to be processed by this <Table> template, to have direct access to its column values. The second part of added element maintains hierarchy between rows in row sets and translate() sets the added row as a child to the current Parent Row that is saved in <Table> element for further references.

2. If one of the <Table> <Columns> children is a primary key translate() tries to find if the row at rowPosition was previously added to its <Rows> element.

2.1. If the row from step 2 was found and its parent row matches current Parent Row, then a new row is not added and the Parent Row is set to the found element.

2.2 In all other than in step 2.1 scenarios translate() adds new child element to <Rows> as in the step 1. In addition, it sets Parent Row to just added element for processing child <Table> element.

3. After steps 1 or 2 were completed HRS CMT is positioned in <Rows> element with startTag = false and rowPosition is moved right by the size to the next part of the row to be processed.

4. Once translate() encounters <Table> element with startTag = false it sets Parent Row to the element Parent Row that was assign to this <Table> and calls getNextEvent(startTag).

3.3. Formatting Process

Once data were extracted from database into HRS CMT hierarchy, the writeXML(char*) method processes it starting from the Root Row element by repeatedly calling getNextEvent method and writes out XML elements based on formatting rules that were built in HRS CMT from QTS.  Appendixes A and C present two different types of formatting for the same extracted data source.

 

4. Data Access Programming Interface

The objective to have Hierarchical Row Set API absolutely neutral to any database technology drives the creation of separate Data Access Programming Interface (DAPI ) that comprises methods to access a database in UXID. Existing Universal Data Access technologies like ODBC, JDBC, OLE DB, ADO.NET, and others provide multi platform uniform programming interface for database-related applications to access data to various database vendors. All of them have methods for connecting to the database, exploring metadata, executing queries, and retrieving results. There are four main methods in DAPI that HRS API uses to extract data from database:

connectDB(char *dataSourceName, char *userID, char *password):establishes connections to a data source;

executeQuery(char *statement, int *              queryIndex): executes a prepared statement to extract data. It returns query handle to be used further in describeColumn and getRow methods;

describeColumn(int *         queryIndex, int column, int &size, int &type): returns column type, column size for given query handle;

getRow(char *rowBuffer, int *queryIndex): retrieves data for one row in the result set into a predefined buffer rowBuffer.

These methods provide a uniform interface for different software makers' connection to databases and make HRS API independent to build desired XML presentation regardless of source data. Methods from DAPI could be implemented based on any technology mentioned above and after particular Data Access technology was chosen the DAPI implementation simply wraps the appropriate method from chosen library to extract data from any data source compliant with this technology. 

To test the UXID functionality we used the ODBC library for Windows platform and CLI library for mainframe OS390.   

 

5. Hierarchical DB Application

Any application that intends to use UXID has to be built on HRS API and DAPI libraries. The UXID sample project was built in a Microsoft Visual C++ 6 environment and uses universal HRS API library and ODBC DAPI library. It demonstrates main functionalities that UXID has:

Builds HRS CMT for QTS that was prepared to extract data and build an XML document;

Extracts data from database into built HRS CMT that can be saved on disk or any other media and retrieved back for further use without parsing or serialization;

Writes required XML document based on QTS formatting.

The UXID sample project consists of

UXID.exe to build HRS CMT, run the extraction process, and write desired XML document based on prepared QTS. It uses a QTS *.xml file as only input to build main object - HRS CMT in *.hdb file to maintain extracted relational data as a hierarchical structure;

Microsoft Access sampdata.mdb sample database;

Users Guide and QTS examples.

Executable UXID.exe allows also trying your own QTS and different database that has ODBC driver to run extraction process and build XML documents from relational data.  

 

6. Conclusions

The Universal XML Interface to Database is the layer above any Universal Data Access Technologies that provide the vendor independent way to extract information from a database in the desired XML format, regardless of operating systems and programming languages.

The transformation of relational data by UXID uses Query Translation Schema as the only input that a user should prepare to build a desired XML document. The QTS describes transformation rules to execute a query and build from relational data a CMT hierarchy that is part of UXID ( HRS) and formatting rules to write a desired XML document. If database had changed or data to be extracted has to be changed, only QTS needs to be rewritten, but UXID itself remains intact.

HRS CMT is also a hierarchical storage for extracted relational data that can be directly copied to a disk and brought back to be instantly accessed by an application without parsing or serialization steps. This feature and the functionality of the SAX parser and DOM Interface gives HRS CMT significant advantages against other formatting schemes for XML processing.

 

7. References

1. Blakeley, J. Data access for the masses through OLE DB, http://portal.acm.org/citation.cfm?id=235968.233329, 1996.

2. Bray, T. et al. Extensible Markup Language (XML) 1.0 (Fourth Edition). http://www.w3.org/TR/xml/, September 2006.

3. Clark, J. XSL Transformations (XSLT), http://www.w3.org/TR/xslt, 1999.

4. Conner, M. CBXML: Experience with Binary XML, IBM Corporation, http://www.w3.org/2003/08/binary-interchange-workshop/19-IBM-CBXML-W3C-Submission-updated.zip, 2003

5. Guseynov, Y. U.S. Patent Application (pending) No. 11/603,299 for Contiguous Memory Tree, Filing Date: November 21, 2006.

6. Le Hégaret, P. et al. Document Object Model (DOM), http://www.w3.org/DOM/, January 2005.

7. Lazar, D. Microsoft Strategy for Universal Data Access, http://msdn.microsoft.com/en-us/library/ms811453.aspx, 1998.

8. Matthiaas, N., Jasmi, J. XML Parsing: A Threat to Database Performance. CIKM’03 November 3 – 8, 2003, New Orleans, Louisiana, USA. http://lists.w3.org/Archives/Public/www-ws/2004Oct/att-0032/MNicola_CIKM_2003_1_.pdf.

9. Megginson, D. Simple API for XML (SAX), http://www.saxproject.org/, April 2004.

10. Rogers, D. Universal Databases - Something for Everyone? http://www.ddj.com/database/184415596, 1998.

11. Sandoz, P. et al. Fast Infoset, http://java.sun.com/developer/technicalArticles/xml/fastinfoset/, 2004.

12. Scardina, M., et al. Oracle Database 10g XML & SQL: Design, Build, & Manage XML Applications in Java, C, C++, & PL/SQL , Oracle Press, 2004.

13. Schneider, J. et al. Efficient XML Interchange (EXI) Format 1.0, W3C Working Draft, http://www.w3.org/TR/exi/, July 2007.

14. Steegmans, B., et al. XML for DB2 Information Integration, ibm.com/redbooks, 2004. http://www.redbooks.ibm.com/redbooks/SG246994/wwhelp/wwhimpl/js/html/wwhelp.htm.

15. XML-Related Specifications (SQL/XML), ISO/IEC 9075-14:2008(E), 2008.


Appendix A. QTS Relation Hierarchy

QTS sample:

<RootQuery>

   <Query>

      <Statement>SELECT DISTINCTROW Categories.CategoryID, Categories.CategoryName,                            

      Products.ProductName, Sum([Order Details Extended].ExtendedPrice) AS ProductSales

      FROM Categories   INNER JOIN (Products INNER JOIN (Orders INNER JOIN [Order Details Extended]    

      ON Orders.OrderID =   [Order Details Extended].OrderID) ON Products.ProductID = [Order Details

      Extended].ProductID) ON   Categories.CategoryID = Products.CategoryID WHERE (((Orders.OrderDate)

      Between #1/1/1994# And #12/31/1994#))

      GROUP BY Categories.CategoryID, Categories.CategoryName, Products.ProductName

      </Statement>

      <Table TNAME="Category">

         <Columns>

            <CategoryID KEY="PK"/>

            <CategoryName />

         </Columns>

         <Rows/>

         <Table TNAME="Product Sales">

            <Columns>

               <ProductName />

               <ProductSale />

            </Columns>

            <Rows/>

         </Table>

      </Table>

   </Query>

   <Query>

      <Statement>SELECT CustomerID, City from Customers where CustomerID='ANATR'</Statement>

      <Table TNAME="Customers">

         <Columns>

            <CustomerID />

            <City />

         </Columns>

         <Rows/>

      </Table>

   </Query>

</RootQuery>

 

Output XML document:

<RootQuery >

   <Table TNAME="Category">

      <CategoryID>1</CategoryID>

      <CategoryName>Beverages</CategoryName>

      <Table TNAME="Product Sales">

         <ProductName>Chai</ProductName>

         <ProductSale>4887.0000</ProductSale>

      </Table >

      <Table TNAME="Product Sales">

        <ProductName>Chang</ProductName>

        <ProductSale>7038.5500</ProductSale>

      </Table >

   </Table>

   <Table TNAME="Category">

      <CategoryID>2</CategoryID>

      <CategoryName>Condiments</CategoryName>

      <Table TNAME="Product Sales">

         <ProductName>Aniseed Syrup</ProductName>

         <ProductSale>1724.0000</ProductSale>

      </Table >

   </Table>

   <Table TNAME="Customers">

      <CustomerID>ANATR</CustomerID>

      <City>México D.F.</City>

   </Table>

<RootQuery >

Appendix B. QTS Parameters Hierarchy

QTS sample:

<RootQuery>

   <Query>

      <Statement>SELECT DISTINCTROW CategoryID, CategoryName from Categories order by    

      CategoryID</Statement>

      <Table TNAME="Category" >

         <Columns>

            <CategoryID />

            <CategoryName />

         </Columns>

         <Rows/>

         <Query>

            <Statement>SELECT Products.ProductName, Sum([Order Details Extended].ExtendedPrice) AS   

            ProductSales FROM Products INNER JOIN (Orders INNER JOIN [Order Details Extended] ON 

            Orders.OrderID = [Order Details Extended].OrderID) ON Products.ProductID = [Order Details 

            Extended].ProductID WHERE (Products.CategoryID = @CategoryID and ((Orders.OrderDate)

            Between #1/1/1994# And #12/31/1994#))

            GROUP BY Products.ProductName</Statement>

            <Table TNAME="Product Sales">

               <Columns>

                  <ProductName />

                  <ProductSale />

               </Columns>

               <Rows/>

            </Table>

         </Query>

      </Table>

   </Query>

   <Query>

      <Statement>SELECT CustomerID, City from Customers where CustomerID='ANATR'</Statement>

      <Table TNAME="Customers">

         <Columns>

            <CustomerID />

            <City />

         </Columns>

         <Rows/>

      </Table>

   </Query>

</RootQuery>

Appendix C. Another formatting for QTS Parameters Hierarchy

QTS sample:

<RootQuery>

   <Query>

      <Statement>SELECT DISTINCTROW CategoryID, CategoryName from Categories order by    

      CategoryID</Statement>

      <Table TNAME="Category" >

         <Columns>

            <CategoryID  CFORMAT=”NO” />

            <CategoryName CFORMAT="NAME" />

         </Columns>

         <Rows/>

         <Query>

            <Statement>SELECT Products.ProductName, Sum([Order Details Extended].ExtendedPrice) AS   

            ProductSales FROM Products INNER JOIN (Orders INNER JOIN [Order Details Extended] ON 

            Orders.OrderID = [Order Details Extended].OrderID) ON Products.ProductID = [Order Details 

            Extended].ProductID WHERE (Products.CategoryID = @CategoryID and ((Orders.OrderDate)

            Between #1/1/1994# And #12/31/1994#))

            GROUP BY Products.ProductName</Statement>

            <Table TNAME="Product Sales">

               <Columns>

                  <ProductName CFORMAT="NAME"  />

                  <ProductSale />

               </Columns>

               <Rows/>

            </Table>

         </Query>

      </Table>

   </Query>

   <Query>

      <Statement>SELECT CustomerID, City from Customers where CustomerID='ANATR'</Statement>

      <Table TNAME="Customers">

         <Columns>

            <CustomerID />

            <City />

         </Columns>

         <Rows/>

      </Table>

   </Query>

</RootQuery>

 

Output XML document:

 <RootQuery >

   <Table TNAME="Category" NAME=”Beverages”>

      <Table TNAME="Product Sales" NAME=”Chai”>

         <ProductSale>4887.0000</ProductSale>

      </Table >

      <Table TNAME="Product Sales" NAME=”Chang”>

        <ProductSale>7038.5500</ProductSale>

      </Table >

   </Table>

   <Table TNAME="Category" NAME=”Condiments”>

      <Table TNAME="Product Sales" NAME=”Aniseed Syrup”>

         <ProductSale>1724.0000</ProductSale>

      </Table >

   </Table>

   <Table TNAME="Customers">

      <CustomerID>ANATR</CustomerID>

      <City>México D.F.</City>

   </Table>

<RootQuery >