Generating one or more XML documents from a single SQL query

Abstract

A technique is provided for generating one or more XML documents from a single SQL query. Data stored on a data storage device that is connected to a computer is transformed. A query is received that selects data stored in a relational database management system on the data storage device, wherein a data access definition defines: (1) a collection of one or more tables in the relational database management system for storing attributes from an XML document, (2) how data stored in the tables maps to the XML document, (3) a query for mapping the data stored in the tables to the XML document, and (4) a table that will contain the XML document after the XML document is generated. Then, one or more XML documents are generated from the selected data using the data access definition.

Claims

What is claimed is:

1. A method of transforming data stored on a data storage device that is connected to a computer, the method comprising:

receiving a query that selects data is stored in a relational database management system on the data storage device, wherein a data access definition defines: (1) a collection of one or more tables in the relational database management system for storing attributes from an XML document, (2) how data stored in the tables maps to the XML document, (3) a query for mapping the data stored in the tables to the XML document, and (4) a table that will contain the XML document after the XML document is generated; and

generating one or more XML documents from the selected data using the data access definition.

2. The method of claim 1, wherein the one or more XML documents are generated by mapping each column of the tables to an element or attribute of one of the XML. documents.

3. The method of claim 1, further comprising using a document type definition to validate the one or more XML documents.

4. The method of claim 1, further comprising using a document type definition to prepare the document access definition.

5. The method of claim 1, wherein the document access definition further comprises an Extensible Markup Language Path data model based definition of the one or more XML documents to be generated.

6. The meted of claim 1, further comprising mapping column names of the tables to equivalence classes.

7. The method of claim 6, wherein the equivalence classes are defined by a user.

8. The method of claim 6, wherein the equivalence classes are defined by a heuristic approach.

9. The method of claim 1, further comprising removing duplicates from the selected data.

10. The method of claim 1, further comprising, prior to retrieving data, parsing the document access definition.

11. The method of claim 1, wherein the one or more XML documents are generated using XML composition stored procedures.

12. The method of claim 1, wherein the data to generate one or more documents is stored in an XML collection.

13. The method of claim 1, wherein the one or more XML documents are shared between businesses.

14. The method of claim 1, wherein the one or mote XML documents are generated by stored procedures.

15. The method of claim 1, wherein the stored procedures can be called from database client code.

16. An apparatus for transforming data, comprising:

a computer having a data store coupled thereto, wherein the data store data; and

one or more computer programs, performed by the computer, for receiving a query that selects data stored in a relational database management system on the data storage device, wherein a data access definition defines: (1) a collection of one or more tables in the relational database management system for storing attributes from an XML document, (2) how data stored in the tables maps to the XML document, (3) a query for mapping the data stored in the tables to the XML document, and (4) a table that will contain the XML document after the XML document is generated; and generating one or more XML documents from the selected data using the data access definition.

17. The apparatus of claim 16, wherein the one or more XML documents are generated by mapping each column of the tables to an element or attribute of one of the XML documents.

18. The apparatus of claim 16, further comprising using a document type definition to validate the one or more XML documents.

19. The apparatus of claim 16, further comprising using a document type definition to prepare the document access definition.

20. The apparatus of claim 16, wherein the document access definition further comprises an Extensible Markup Language Path data model based definition of the one or more XML documents to be generated.

21. The apparatus of claim 16, further comprising creating mapping column names of the tables to equivalence classes.

22. The apparatus of claim 21, wherein the equivalence classes are defined by a user.

23. The apparatus of claim 21, wherein the equivalence classes are defined by a heuristic approach.

24. The apparatus of claim 16, further comprising removing duplicates from the selected data.

25. The apparatus of claim 16, further comprising, prior to retrieving data, parsing the document access definition.

26. The apparatus of claim 16, wherein the one or more XML documents are generated using XML composition stored procedures.

27. The apparatus of claim 16, wherein the data to generate one or more XML documents is stored in an XML collection.

28. The apparatus of claim 16, wherein the one or more XML documents are shared between businesses.

29. The apparatus of claim 16, wherein the one or more XML documents are generated by stored procedures.

30. The apparatus of claim 16, wherein the stored procedures can be called from database client code.

31. An article of manufacture comprising a program storage medium readable by a computer and embodying one or more instructions executable by the computer to perform method steps for transforming data in a data store connected to the computer, the method comprising the steps of:

receiving a query that selects data stored in a relational database management system on the data storage device, wherein a data access definition defines: (1) a collection of one or more tables in the relational database management system for storing attributes from an XML document, (2) how data stored in the tables maps to the XML document, (3) a query for mapping the data stored in the tables to the XML document, and (4) a table that will contain the XML document after the XML document is generated; and

generating one or more XML documents from the selected data using the data access definition.

32. The article of manufacture of claim 31, wherein the one or more XML documents are generated by mapping each column of the tables to an element or attribute of one of the XML documents.

33. The article of manufacture of claim 31, further comprising using a document type definition to validate the one or more XML documents.

34. The article of manufacture of claim 31, further comprising using a document type definition to prepare the document access definition.

35. The article of manufacture of claim 31, wherein the document access definition further comprises an Extensible Markup Language Path data model based definition of the one or more XML documents to be generated.

36. The article of manufacture of claim 31, further comprising mapping column names of the tables to equivalence classes.

37. The article of manufacture of claim 36, wherein the equivalence classes are defined by a user.

38. The article of manufacture of claim 36, wherein the equivalence classes are defined by a heuristic approach.

39. The article of manufacture of claim 31, further comprising removing duplicates from the selected data.

40. The article of manufacture of claim 31, further comprising, prior to retrieving data, parsing the document access definition.

41. The article of manufacture of claim 31, wherein the one or more XML documents are generated using XML composition stored procedures.

42. The article of manufacture of claim 31, wherein the data to generate one or more documents is stored in an XML collection.

43. The article of manufacture of claim 31, wherein the one or more XML documents are shared between businesses.

44. The article of manufacture of claim 31, wherein the one or more XML documents are generated by stored procedures.

45. The article of manufacture of claim 31, wherein to stored procedures can be called from database client code.

Description

FIELD OF THE INVENTION

This invention relates in general to computer-implemented database systems, and, in particular, to processing Extensible Markup Language (XML) documents.

BACKGROUND OF THE INVENTION

The Internet is a collection of computer networks that exchange information via Hyper Text Transfer Protocol (HTTP). The Internet computer network consists of many internet networks. Currently, the use of the Internet computer network for commercial and non-commercial uses is exploding. Via its networks, the Internet computer network enables many users in different locations to access information stored in data sources (e.g., databases) stored in different locations.

The World Wide Web (i.e., the "WWW" or the "Web") is a hypertext information and communication system used on the Internet computer network with data communications operating according to a client/server model. Typically, a Web client computer will request data stored in data sources from a Web server computer, at which Web server software resides. The Web server software interacts with an interface connected to, for example, a Database Management System ("DBMS"), which is connected to the data sources. These computer programs residing at the Web server computer will retrieve the data and transmit the data to the client computer. The data can be any type of information, including database data, static data, HTML data, or dynamically generated data.

With the fast growing popularity of the Internet and the World Wide Web (also known as "WWW" or the "Web"), there is also a fast growing demand for Web access to databases.

Databases are computerized information storage and retrieval systems. A Relational Database Management System (RDBMS) is a database management system (DBMS) which uses relational techniques for storing and retrieving data. Relational databases are organized into physical tables which consist of rows and columns of data. The rows are formally called tuples. A database will typically have many physical tables and each physical table will typically have multiple tuples and multiple columns. The physical tables are typically stored on random access storage devices (RASED) such as magnetic or optical disk drives for semi-permanent storage. Additionally, logical tables or "views" can be generated based on the physical tables and provide a particular way of looking at the database. A view arranges rows in some order, without affecting the physical organization of the database.

RDBMS software using a Structured Query Language (SQL) interface is well known in the art. The SQL interface has evolved into a standard language for RDBMS software and has been adopted as such by both the American National Standards Institute (ANSI) and the International Standards Organization (ISO).

The SQL interface allows users to formulate relational operations on the tables either interactively, in batch files, or embedded in host languages, such as C and COBOL. SQL allows the user to manipulate the data. The definitions for SQL provide that a RDBMS should respond to a particular query with a particular set of data given a specified database content, but the technique that the RDBMS uses to actually find the required information in the tables on the disk drives is left up to the RDBMS. Typically, there will be more than one technique that can be used by the RDBMS to access the required data. The RDBMS will optimize the technique used to find the data requested in a query in order to minimize the computer time used and, therefore, the cost of performing the query.

Additionally, an index is an ordered set of references to the records or rows in a database file or table. The index is used to access each record in the file using a key (i.e., one of the fields of the record or attributes of the row). When data is to be retrieved, an index is used to locate records. Then, the data is sorted into a user-specified order and returned to the user.

Extensible Markup Language (XML) is a new specification that is quickly gaining popularity for creating what are termed "XML documents". XML documents comprise structured data. XML documents are being shared between multiple businesses and between businesses and customers.

When XML documents are stored as column data, searching for desired XML data can be time-consuming. Typically, a search for XML data would require searching each XML document. This is usually called a document scan. Thus, there is a need in the art for an improved technique for searching for XML documents stored as column data.

With the longstanding use of relational databases, many businesses have stored their data in relational tables. In order to share this data with businesses that are using XML documents, the data in the relational databases may be manually selected, retrieved, and stored into XML documents. This is a long, tedious task. Thus, there is a need for an improved technique of selecting, retrieving, and storing relational data into XML documents.

In order to share relational data with other businesses that are using XML documents, a user may manually convert the relational data into XML documents. This is time consuming and inefficient. Thus, there is a need for an improved technique of generating XML documents from relational data.

Additionally, when an XML document is received, a user may need to store the data from the XML document into a relational database. Currently, this is a time consuming processing in which a user manually transfers the data from the XML document to the relational database. Thus, there is a need for an improved technique of decomposing an XML document and storing the decomposed data into a relational database.

SUMMARY OF THE INVENTION

To overcome the limitations in the prior art described above, and to overcome other limitations that will become apparent upon reading and understanding the present specification, the present invention discloses a method, apparatus, and article of manufacture for a computer implemented technique for processing XML documents.

In accordance with one aspect of the present invention, data is stored in a data store connected to a computer. A main table is created having a column for storing a document, wherein the document has one or more elements or attributes. One or more side tables are created, wherein each side table stores one or more elements or attributes. Then, the side tables are used to locate data in the main table.

In accordance with another aspect of the present invention, data stored on a data storage device that is connected to a computer is transformed. A query that selects data in the data storage device is received. The selected data is retrieved into a work space. Then, one or more XML documents are generated to consist of the selected data.

In accordance with yet another aspect of the present invention, data stored on a data storage device that is connected to a computer is transformed. Initially, a document object model tree is generated using a document access definition. The document object model tree is traversed to obtain information to retrieve relational data. The relational data is mapped to one or more XML documents.

In accordance with a further aspect of the present invention, data stored on a data store that is connected to a computer is transformed. Initially, an XML document containing XML data is received. A document access definition that identifies one or more relational tables and columns is received. The XML data is mapped from the application DTD to the relational tables and columns using the document access definition based on the XPath data model.

BRIEF DESCRIPTION OF THE DRAWINGS

Referring now to the drawings in which like reference numbers represent corresponding parts throughout:

FIG. 1 schematically illustrates the hardware environment of an embodiment of the present invention, and more particularly, illustrates a typical distributed computer system using a network.

FIG. 2 is a diagram illustrating a computer software environment that could be used in accordance with the present invention.

FIG. 3 illustrates an application or main table and its four side tables.

FIG. 4 is a flow diagram illustrating steps performed by the XML System in creating and maintaining XML document data as column data.

FIG. 5 is a flow diagram of steps performed by the XML System to enable a column.

FIG. 6 is a flow diagram of steps performed by the XML System to disable a column.

FIG. 7 is a diagram illustrating code organization to compose XML documents.

FIG. 8 is a block diagram illustrating components of the XML System in one embodiment of the invention.

FIG. 9 is a flow diagram illustrating the steps performed by the XML System to transform relational data into one or more XML documents using SQL mapping.

FIG. 10 is a flow diagram illustrating the process performed by the XML system using RDB_node mapping to compose XML documents.

FIG. 11 is a flow diagram illustrating the steps performed by the XML System to decompose XML documents with application specific mappings.

DETAILED DESCRIPTION

In the following description of an embodiment of the invention, reference is made to the accompanying drawings which form a part hereof, and which is shown by way of illustration a specific embodiment in which the invention may be practiced. It is to be understood that other embodiments may be utilized as structural changes may be made without departing from the scope of the present invention.

A. Hardware Architecture

FIG. 1 schematically illustrates the hardware environment of an embodiment of the present invention, and more particularly, illustrates a typical distributed computer system using a network 100 to connect client computers 102 executing client applications to a server computer 104 executing software and other computer programs, and to connect the server system 104 to data sources 106. A typical combination of resources may include client computers 102 that are personal computers or workstations, and a server computer 104 that is a personal computer, workstation, minicomputer, or mainframe. These systems are coupled to one another by various networks, including LANs, WANs, SNA networks, and the Internet. Each client computer 102 and the server computer 104 additionally comprise an operating system and one or more computer programs.

A client computer 102 typically executes a client application and is coupled to a server computer 104 executing one or more server software. The server software may include an XML system 110. The server computer 104 also uses a data store interface and, possibly, other computer programs, for connecting to the data sources 106. The client computer 102 is bi-directionally coupled with the server computer 104 over a line or via a wireless system. In turn, the server computer 104 is bi-directionally coupled with data sources 106.

The data store interface may be connected to a Database Management System (DBMS), which supports access to a data store 106 by executing, for example, RDBMS software. The interface and DBMS may be located at the server computer 104 or may be located on one or more separate machines. The data sources 106 may be geographically distributed.

The operating system and computer programs are comprised of instructions which, when read and executed by the client and server computers 102 and 104, cause the client and server computers 102 and 104 to perform the steps necessary to implement and/or use the present invention. Generally, the operating system and computer programs are tangibly embodied in and/or readable from a device, carrier, or media, such as memory, other data storage devices, and/or data communications devices. Under control of the operating system, the computer programs may be loaded from memory, other data storage devices and/or data communications devices into the memory of the computer for use during actual operations.

Thus, the present invention may be implemented as a method, apparatus, or article of manufacture using standard programming and/or engineering techniques to produce software, firmware, hardware, or any combination thereof. The term "article of manufacture" (or alternatively, "computer program product") as used herein is intended to encompass a computer program accessible from any computer-readable device, carrier, or media. Of course, those skilled in the art will recognize many modifications may be made to this configuration without departing from the scope of the present invention.

Those skilled in the art will recognize that the exemplary environment illustrated in FIG. 1 is not intended to limit the present invention. Indeed, those skilled in the art will recognize that other alternative hardware environments may be used without departing from the scope of the present invention.

B. XML Background

Extensible Markup Language (XML) is a subset of Standard Generalized Markup Language (SGML). XML works in conjunction with Extensible Stylesheet Language Transformation, (XSLT) and Extensible Markup Language Path (XPath). XML may also work in conjunction with a Document Object Model (DOM) or Namespace.

Extensible Markup Language (XML) is a subset of Standard Generalized Markup Language (SGML). XML is described in XML 1.0, found at the following web site: http://www.w3.org/TR/REC-xml. Extensible Markup Language (XML) is a set of rules or guidelines for designing text formats for structured data using tags. Additional detail may be found at the following web site: http://www.w3.org/XML/1999/XML-in-10-points. For interoperability, domain-specific tags called a vocabulary can be standardized using a Document Type Definition, so that applications in that domain understand the meaning of the tags.

Extensible Style Language Transformer or XSLT is a language for transforming XML documents into other XML documents. The XSLT specification defines the syntax and semantics of the XSLT language. XSLT-defined elements are distinguished by belonging to a specific XML namespace, which is referred to as the XSLT namespace. A transformation expressed in XSLT describes rules for transforming a source tree into a result tree. Further detail about XSLT may be found at http://www.w3.org/TR/xslt.

XML Path or XPath addresses parts of an XML document. XPath gets its name from its use of a path notation as in URLs for navigating through the hierarchical structure of an XML document. Further detail about XML path may be found at http://www.w3.org/TR/xpath.

A Document Object Model (DOM) is a standard set of function calls for manipulating XML files from a programming language. Additional detail may be found at the following web site: http://www.w3.org/TR/REC-DOM-Level-1/.

C. Overview of the XML System

In one embodiment of the invention, the XML System comprises the XML Extender from International Business Machines, Corporation, of Armonk, N.Y. The XML System offers the capability of XML storage and data interchange. By storage, the XML System provides mechanisms for storing and retrieving XML documents in a relational database (e.g., DB2.RTM. from International Business Machines, Corporation) and searching the content of XML with high performance. By data interchange, the XML System provides a mapping between new and existing relational tables and XML formatted documents. Thus, the XML System allows customers to do e-business anywhere, enabling XML with Business to Business (B2B) and Business to Consumer (B2C) applications. For B2B applications, application data flows between database servers, via any network (e.g., the internet or an intranet), either directly without client interaction or indirectly via some client systems. For B2C applications, application data flows between a consumer at, for example, a workstation, and a server connected via a network (e.g., between database servers and web clients via the internet). Thus, the XML System supports Business to Business (B2B) and Business to Client (B2C) applications. In both cases, the following requirements will apply:

Performance

Scalability

Integration with existing business logic

Smart query support

Legacy file support

Developer efficiency

FIG. 2 is a diagram illustrating a computer hardware environment that could be used in accordance with the present invention. In one embodiment, the DB2 XML Extender 200, a product from International Business Machines, Corporation, is at the center of the architecture. An application program 202 and a document access definition (DAD) 204 are received by the DB2 XML Extender 200. The DB2 XML Extender 200 takes an XML document 206 as the input, stores the XML document 206 in DB2 210 (i.e., a relational database) either internally inside DB2 210 or externally on the file system as one of more XML files 208. Then, the stored XML document 206 can also be retrieved from DB2 210 or the file system through the DB2 Extender 200. The processing performed by the DB2 XML Extender 200 will be described in more detail below.

In another embodiment, an application program 202 and a document access definition (DAD) 204 are received by the DB2 XML Extender 200. The DB2 XML Extender 200 takes an XML document 206 as input, decomposes the XML document 206 into fragmented data and stores the fragmented data in DB2 210 (i.e., a relational database). Then, the fragmented data stored in DB2 210 can be regenerated from DB2 210 through the DB2 Extender 200. The processing performed by the DB2 XML Extender 200 will be described in more detail below.

Those skilled in the art will recognize that the environment illustrated in FIG. 2 is not intended to limit the present invention. Indeed, those skilled in the art will recognize that other alternative hardware environments may be used without departing from the scope of the present invention.

C.1 Applications

Different types of applications can benefit from the use of the XML System. Some illustrations follow:

Business to Business (B2B) Applications for E-Commerce

B2B applications mainly use XML as their interchange format, such as Electronic Data Interchange (EDI). The XML System enables maintaining native XML formatted documents, as well as mapping data into/from relational tables. With native XML formatted documents. XML enables storing entire XML documents into a database and searching on known elements or attributes. With mapping, XML System enables an application builder who knots s the relational data model of particular business tables to custom map XML content to or from existing tables.

Web Information Retrieval Applications

These are B2C applications which are often used in interactive Web sites, such as sites for insurance and real-estate industries. The XML documents are usually not very large in size, but have structured information.

The XML System enables storing entire XML documents into a database and using SQL to do a fast search on desired XML elements or attributes with rich data types. Range search for rich data types is often important. Additionally, the XML System enables retrieving data from existing business tables and from XML documents and putting them on a web site for viewing.

For example, an insurance company may set up a call center system in which agents retrieve phone calls from their customers. The information is collected, and the case is archived. The XML System is used to store entire XML documents in a database. Then, an insurance agent can easily display an insurance case on a screen. The XML System also provides a fast and powerful search of these insurance cases, so the insurance agent can quickly retrieve information while still on the phone with a customer. Additionally, alternative ways of searching for information, i.e. numbers, text wildcards, key words, etc., are provided by XML System.

Content Management

This type of application provides advanced content management functions to a user. A user could use XML System as physical storage, and have fast search with indexing. The XML documents are usually large in size. In some cases, it is desirable to partition the XML documents into multiple pieces and perform update in place.

As an extender to DB2.RTM., XML System enhances DB2.RTM. functionality for XML enablement. That is, XML System enables use of SQL as the main access technique, along with database features of: stored procedures, user defined types (UDT) and user defined functions (UDF).

The XML System meets the following requirements:

Physical Storage: for entire document, or shredded structured data, with data types.

Support of flat files: allows data to be stored in flat files, and imported/exported to/from database.

Access via SQL: supports field search, full text search, structural search.

Indexing facility: builds on different data types for better query performance.

Updates XML element/attribute.

Mapping to/from relational: composes/decomposes XML documents from/to) data store in relational tables.

NLV support: support of double byte characters.

C.2 XColumns and XCollections

XML System provides good data and metadata management solutions to handle traditional and non-traditional data. With the content of structured XML documents in a database, a user can combine structured XML information with traditional relational data. Based on the application, a user can choose whether to store entire XML documents in a database as a non-traditional distinct data type or map the XML content as traditional data in relational tables. For non-traditional XML data types, the XML System adds the power to search rich data types of XML element or attribute values. For traditional SQL data, that is either decomposed from incoming XML documents or in existing relational tables to be used to create outgoing XML documents, the XML System provides a custom mapping mechanism to allow the transformation between XML documents and relational data.

The XML System offers the flexibility to store entire XML documents as column data or transform between XML documents and data in existing tables. The transformation includes decomposing an XML document into one or multiple pieces and storing the pieces in the form of relational data, as well as, composing XML documents from the data in existing relational tables. A user can decide how structured XML documents are to be stored or created through a Document Access Definition(DAD).

The DAD itself is an XML formatted document. The DAD associates XML documents to a database through two major access and storage techniques by defining elements Xcolumn and Xcollection. Xcolumn defines how to store and retrieve entire XML documents as column data of the XML user defined type (UDT). An XML column is a column of XML System's user defined type (UDT). Applications can include an XML column in any user table. Operations on the XML column can be processed after the column is enabled with the XML System. A user can access XML column data mainly through the SQL statements and XML System's user defined function (UDF). With the different access and storage techniques, the XML System provides the flexibility of XML data storage and retrieval.

In particular, an XML column is used to store entire XML documents n the native XML format. This approach treats XML format as an non-traditional data type and offers user defined types (UDTs) and user defined functions (UTDFs) for a fast, versatile, and intelligent technique for searching through XML documents. The XML System gives applications the freedom to specify a list of XML elements/attributes as general SQL data types for fast search. The XML System will extract these values from the XML documents and store them in side tables so that a user can create indices on them. The application can query these side tables or join them with the application (i.e., "main") table to do a fast search. For example, a user can input a query such as: "give me all the documents whose prices are greater than $2500.00", providing 2500.00 is the value of an XML element or attribute inside the XML documents.

The XML System provides several user defined types (UDTs) for XML columns. These data types are used to identify the storage types of XML documents in the application table. The XML System supports legacy flat files, and a user is not restricted to storing XML documents inside a database. A user can also store XML documents as files on the local or remote file system, specified by a URL or a local file name.

The XML System provides powerful user-defined function (UDF)s to store and retrieve XML documents in XML columns, as well as to extract XML element/attribute values. The UDFs are applied to XML user defined types (UDTs), thus, these are mainly used for XML columns.

An Xcollection defines how to decompose XML documents into a collection of relational tables or to compose XML documents from a collection of relational tables. An XML collection is a virtual name of a set of relational tables. Applications can enable an XML collection of any user tables. These user tables can be existing tables of legacy business data or the ones newly created by the XML System. A user can access XML collection data mainly through the stored procedures provided by the XML System.

An XML collection is used to transform data between database tables and XML documents. An XML collection achieves the goal of data interchange via XML. For applications that want to compose or decompose XML documents from/into a set of relational tables, the XML System offers a technique to enable an XML collection through a Document Access Definition (DAD). In the Document Access Definition, applications can make a custom mapping between database column data in new or existing tables to XML elements or attributes. The access to an XML collection is by calling XML System's stored procedures or directly querying to the tables of the collection.

The XML System also allows overrides of query conditions explicity or implicitly defined in the DAD, by parsing the SQL or XML XPath based override parameter to the composition stored procedures. In this way, it supports dynamic query for generating XML documents.

With the XML System, an application can:

Store entire XML documents as column data in an application table, either internally or externally as a local file or URL, while extracting desired XML element or attribute values into side tables for search.

Compose or decompose contents of XML documents from/into an XML collection which consists of one or more relational tables.

Perform fast search on XML elements or attributes of SQL general data types by converting character string in XML documents to SQL data types for indexing.

Update the content of an XML element, or the value of an XML attribute.

Extract XML elements or attributes dynamically in SQL query.

Validate XML documents during insertion and update.

The XML System also serves as an XML document type definition (DTD) repository. When a database is XML enabled, a DTD Reference Table (DTD_REF) is created. Each row of this table represents a DTD, with additional metadata information. This table is accessible by users, and allows them to insert their own DTDs. The DTDs in the DTD_REF table are used to validate XML documents and to help applications to define a document access definition (DAD).

C.3 Terminology

This section clarifies some terminology used in this specification.

The XML System uses a subset of Extensive Stylesheet Language Transformation (XSLT) and XML Path Language (XPath), Version 1.0, the W3C working draft of Jun. 17, 1999, to identify XML elements or attributes. The content of the XPath is originally in the XSLT and now is referred by the XSLT, as a part of the stylesheet transformation language. Location path is used to define XML elements and attributes. The XSLT/XPath's abbreviated syntax of the absolute location path is used.

The following is not a formal data model, but a set of abbreviated syntax. The notation of the absolute location path with abbreviated syntax supported by the XML System is listed below.

This section clarifies some terminology used in this specification.

The XML System uses a subset of Extensive Stylesheet Language Transformation (XSLT) and XML Path Language (XPath), Version 1.0, the W3C working draft of Jun. 17, 1999, to identify XML elements or attributes. The content of the XPath is originally in the XSLT and now it is referred to by XSLT as a part of the stylesheet transformation language. Previously, the term "path expression" was used. Now, a subset of the term location path is used in XSLT and XPath to define XML elements and attributes. The XSLT XPath's abbreviated syntax of the absolute location path is used.

The following is not a formal data model, but a set of abbreviated syntax. An absolute location path with abbreviated syntax is listed below. This is supported by the XML System. Again, these are not formal definitions.

a. "/":

Represents the XML root element.

b. "/tag1":

Represents the element tag1 under root.

c. "/tag1/tag2/ . . . /tagn":

Represents an element with the name tagn as the child with the descending chain from root, tag1, tag2, . . . , tagn-1

d. "//tagn"

Represents any element with the name tagn, where "//" denotes zero or more arbitrary tags.

e. "/tag1//tagn"

Represents any element with the name tagn which is a child of element with the name tag1 under root, where "//" denotes zero or more arbitrary tags.

f. "/tag1/tag2/@attr1"

Represents the attribute attr1 of element with the name tag2 as a child of element tag1 under root.

g. "/tag1/tag2/[@attr1="5"]"

Represents the element with the name tag2 whose attribute attr1 has the value `5` and it is a child of element with the name tag1 under root.

h. "/tag1/tag2/[@attr1="5"]/ . . . /tagn"

Represents the element with the name tagn which is a child of the descending chain from root, tag1, tag2, . . . where the attribute attr1 of tag2 has the value `5`.

i. "/tag1/tag2/tag3"="Los Angeles"/ . . . /tagn"

Represents the element with the name tagn which is a child of the descending chain from root, tag1, tag2, . . . where tag3 has the value "Los Angeles".

j. "/tag1/tag2/*[@attr1="5"]"

Represents all elements as children of element "/tag1/tag2" with attr1 of value "5".

There are restrictions on the location path when used by the XML System, and these are listed in the table below.

                              TABLE
              Restriction of Location Path Supported
        Use of the Location Path     Location Path Supported
        Extracting UDFs              a-j
        Text Extender's search UDF   a-j
        DAD column definition        c, f (simple location path)

                              TABLE
       Simple Location Path of an Element and an Attribute
    Subject     location path        Description
    XML         /tag_1/tag_2/ . . . / an element content identified
    Element     tag_n-1/tag_n        by the tag_n and its parents
    XML         /tag_1/tag_2/ . . . / an attribute with name "attr1"
    Attribute   tag_n-1/tag_n@attr1  of the element identified by
                                     tag_n and it parents

        Document Access   The definition used to enable an XML
        Definition(DAD):  System column or an XML collection,
                          which is XML formatted.
        Partition:        The term partition used means the full
                          partition. In other words, the union of all
                          partitioned parts forms the original
                          document.
        Location path:    A subset of the abbreviated syntax of the
                          location path defined by XPath. A
                          sequence of XML tags to identify an
                          XML element or attribute. It is used in the
                          extracting UDFs to identify the subject to
                          be extracted. The terms of path expression
                          and location path may be used
                          interchangeably.
        Side table:       Additional tables created by the XML
                          System to store searchable
                          elements/attributes for an enabled XML
                          Column.
        Valid Document:   An XML document that has an associated
                          DTD. To be valid, the XML document
                          cannot violate the syntactic rules specified
                          in its DTD.
        Well-formed       An XML document that does not contain
        document:         a DTD. A document with a DTD (valid)
                          must also be well-formed.
        XML Attribute:    Any attribute specified by the ATTLIST
                          under the XML element in the DTD. The
                          XML System uses the location path to
                          identify an attribute.
        XML Column:       A column in the application table of the
                          XML System UDT type. The term of
                          XML enabled column and XML column
                          will be used interchangeably.
        XML Collection:   A collection of relational tables which
                          present the data to compose XML
                          documents or to be decomposed from
                          XML documents.
        XML Element:      Any XML tag or ELEMENT as specified
                          in the XML DTD. The XML System uses
                          the location path to identify an element.
        XML Tag:          Any valid XML markup language tag,
                          mainly the XML element. The term tag
                          and element are used interchangeably.
        XML Table:        An XML Table is an application table
                          which includes XML System column(s).
                          The terms XML enabled table and XML
                          table are used interchangeably.
        XML Object:       The terms XML Object and XML
                          document are used interchangeably.
        XML UDT:          User defined type provided by the XML
                          System.
        XML UDF:          User defined function provided by the
                          XML System.

    LineItem.dtd
        <?xml encoding="US-ASCII"?>
        <!ELEMENT Order (customer,Part+)>
        <!ATTLIST Order Key CDATA #REQUIRED>
        <!ELEMENT Customer #PCDATA>
        <!ELEMENT Part(Quantity,ExtendedPrice,Tax,Shipment*)>
        <!ATTLIST Part Key CDATA>
        <!ELEMENT Quantity (#PCDATA)>
        <!ELEMENT ExtendedPrice (#PCDATA)>
        <!ELEMENT Tax (#PCDATA)>
        <!ELEMENT Shipment (ShipDate,ShipMode,Comment)>
        <!ELEMENT ShipDate (#PCDATA)>
        <!ELEMENT ShipMode (#PCDATA)>
        <!ELEMENT Comment (#PCDATA)>

    order.xml
        <?xml version="1.0"?>
        <!DOCTYPE Litem_DTD SYSTEM
     "E:.backslash.dxx.backslash.test.backslash.dtd.backslash.LineItem.dtd">
        <Order Key="1">
        <Customer>General Motor</Customer>
        <Part Key="156">
        <Quantity>17</Quantity>
        <ExtendedPrice>17954.55</ExtendedPrice>
        <Tax>0.02</Tax>
        <Shipment>
        <ShipDate>1998-03-13</ShipDate>
        <ShipMode>TRUCK</ShipMode>
        <Comment>This is the first shipment to service of
     GM</Comment>
        </Shipment>
        <Shipment>
        <ShipDate>1999-01-16</ShipDate>
        <ShipMode>FEDEX</ShipMode>
        <Comment>This the second shipment to service of
     GM.</Comment>
        </Shipment>
        </Part>
        <Part Key="68">
        <Quantity>36</Quantity>
        <ExtendedPrice>34850.16</ExtendedPrice>
        <Tax>0.06</Tax>
        <Shipment>
        <ShipDate>1996-04-12</ShipDate>
        <ShipMode>BOAT</ShipMode>
        <Comment>This shipment is requested by a call.
        from GM marketing.</Comment>
        </Shipment>
        <shipment>
        <ShipDate>1998-08-19</ShipDate>
        <ShipMode>AIR</ShipMode>
        <Comment>This shipment is ordered by an email.</Comment>
        <Shipment>
        </Part>
        </Order>

        dad.dtd
    <?xml encoding="US-ASCII"?>
    <!ELEMENT DAD (dtdid?, validation, (Xcolumn .vertline. Xcollection))>
    <!ELEMENT dtdid (#PCDATA)>
    <!ELEMENT validation (#PCDATA)>
    <!ELEMENT Xcolumn (table*)>
    <!ELEMENT table (column*)>
    <!ATTLIST table name CDATA #REQUIRED
            key CDATA #IMPLIED
            orderBy CDATA #IMPLIED>
    <!ELEMENT column EMPTY>
    <!ATTLIST column
                name CDATA #REQUIRED
                type CDATA #IMPLIED
                path CDATA #IMPLIED
                multi_occurrence CDATA #IMPLIED>
    <!ELEMENT Xcollection (SQL_stmt*, prolog, doctype, root_node)>
    <!ELEMENT SQL_stmt (#PCDATA)>
    <!ELEMENT prolog (#PCDATA)>
    <!ELEMENT doctype (#PCDATA .vertline. RDB_node)">
    <!ELEMENT root_node (element_node)>
    <!ELEMENT element_node (RDB_node?,
                attribute_node*,
                text_node?,
                element_node*,
                namespace_node*,
                process_instruction_node*,
                comment_node*)>
    <!ATTLIST element_node
             name       CDATA #REQUIRED
             ID        CDATA #IMPLIED
             multi_occurrence  CDATA "NO"
             BASE_URI CDATA #IMPLIED>
    <!ELEMENT attribute_node (column .vertline. RDB_node)>
    <!ATTLIST attribute_node
            name CDATA #REQUIRED>
    <!ELEMENT text_node (column .vertline. RDB_node)>
    <!ELEMENT RDB_node (table+, column?, condition?)>
    <!ELEMENT condition (#PCDATA)>
    <!ELEMENT comment_node (#PCDATA)>
    <!ELEMENT namespace_node EMPTY>
    <!ATTLIST namespace_node
             name CDATA #IMPLIED
             value   CDATA #IMPLIED>
    <!ELEMENT process_instruction_node (#PCDATA)>

         Litem_DAD1.dad
            <?xml version="1.0"?>
    <!DOCTYPE Order SYSTEM "E:.backslash.dtd.backslash.dxxdad.dtd">
    <DAD>
        <dtdid>E:.backslash.dtd.backslash.lineItem.dtd</dtdid>
        <validation>YES</validation>
        <Xcolumn>
            <table name="order_tab">
                <column name="order_key"
                     type="integer"
                     path="/Order/@Key"
                     multi_occurrence="NO"/>
                <column name="customer"
                     type="varchar(50)"
                     path="/Order/Customer"
                    multi_occurrence="NO"/>
            </table>
            <table name="part_tab">
                <column name="part_key"
                     type="integer"
                     path="/Order/Part/@Key"
                     multi_occurrence="YES"/>
            </table>
            <table name="price_tab">
                <column name="price"
                    type="double"
                    path="/Order/Part/ExtendedPrice"
                    multi_occurrence="YES"/>
            </table>
            <table name="ship_tab">
                    <column name="date"
                       type="date"
                       path="/Order/Part/Shipment/ShipDate"
                     multi_occurrence="YES"/>
            </table>
    </Xcolumn>
    </DAD>

    CREATE TABLE sales_tab
        (invoice_number char(6) NOT NULL PRIMARY KEY,
        sales_person varchar(20),
        order    XML Varchar);

    User table sales_tab:
    Column Name     invoice_number    sales_person  order
    Data Type       char(6)           varchar(20)   XMLVarchar

    Side_table order_tab:
    Column Name   order_key       customer        invoice_number
    Data Type     integer         varchar(50)     char(6)
    Location Path /Order/@Key     /Order/Customer N/A
    Side table part_tab:
    Column Name   part_key                     invoice_number
    Data Type     integer                      char(6)
    Location Path /Order/Part/@Key             N/A
    Side table price_tab:
    Column Name   price                        invoice_number
    Data Type     double                       char(6)
    Location Path /Order/Part/ExtendedPrice    N/A
    Side table ship_tab:
    Column Name   date                         invoice-number
    Data Type     date                         char(6)
    Location Path /Order/Part/Shipment/ShipDate N/A

                         Input
    Default UDT          Parameter   Return
    Cast Function        Type        Type          Description
    db2xml.XMLVarchar()  varchar     XMLVarchar    Input from
                                                   memory buffer
                                                   of varchar
    db2xml.XMLCLOB()     clob        XMLCLOB       Input from
                                                   memory buffer
                                                   of clob
    db2xml.XMLDBCLOB()   dbclob      XMLDBCLOB     Input from
                                                   memory buffer
                                                   of dbclob
    db2xml.XMLFile()     varchar     XMLFile       Only store file
                                                   name
    db2xml.XMLURL()      datalink    XMLURL        data type

    Default Cast    Input         Return
    Function        Parameter Type Type    Description
    db2xml.varchar() XMLVarchar    varchar XML document in variable
                                          length of char
    db2xml.clob()   XMLCLOB       clob    XML document in CLOB
    db2xml.dbclob() XMLDBCLOB     dbclob  XML in double byte CLOB
    db2xml.varchar() XMLFile       varchar XML filename in variable
                                          length of char
    db2xml.datalink() XMLURL        datalink URL of XML document

    EXEC SQL BEGIN DECLARE SECTION;
         varchar(3k) xml_buff;
    EXEC SQL END DECLARE SECTION;
    EXEC SQL CONNECT TO mydb
    EXEC SQL DECLARE cl CURSOR FOR
    SELECT Content(order) from sales_tab
         WHERE sales_person = `Sriram Srinivasan`
    EXEC SQL OPEN cl;
    do {
        EXEC SQL FETCH cl INTO :xml_buff;
        if(SQLCODE != 0) {
         break;
         }
        else {
         /* do whatever is needed to do with the XML doc in buffer */
         }
        }
    EXEC SQL CLOSE cl;

        function name     caller        input       output
        dxxEnableDB()     main() in     dbName      errCode,
        (enable_db)       dxxadm.sqc                errMsg

        function name     caller          input     output
        dxxDisableDB()    main() in       dbName    errCode,
        (disable_db)      dxxadm.sqc                errMsg

    function name   caller            input     output
    isDBCS_DB()     dxxEnableDB(),    dbName    TRUE or FALSE
                    dxxDisableDB()

    function name      caller        input           output
    dxxEnableColumn    main() in     2. dbName,      1. errCode
                       dxxadm.sqc    3. tabName,     2. errMsg
                                     4. colName,
                                     5. dadBuf,
                                     6. tablespace,
                                     7. defaultView,
                                     8. rootID,

    function name caller               input         output
    check_table   dxxEnableColumn(),   1. dbName,    1. errCode
                  dxxDisableColumn()                 2. errMsg

    function name   caller             input           output
    check_column    dxxEnableColumn(), 1. table name,  1. errCode
                    dxxDisableColumn() 2. column       2. errMsg
                                       name

    function name   caller             input           output
    createSide      dxxEnableColumn()  1. pDAD,        1. errCode
    Table()                            2. rootid,      2. errMsg
                                       3. rootid
                                       definition
                                       4. tablespace,

    function name      caller             input       output
    rootidTrigger_BIT  dxxEnableColumn()  1. pDAD     1. errCode
                                                      2. errMsg

    function name      caller      input             output
    insertTrigger_AIT   dxxEnable  1. pDAD           1. errCode,
                       Column(),   2. trigger_suffix, 2. errMsg
                                   3. rootid,

    function name     caller            input           output
    deleteTrigger_ADT dxxEnableColumn() 1. pDAD         1. errCode
                                        2. trigger_suffix, 2. errMsg
                                        3. rootid,

    function name     caller            input           output
    updateTrigger.sub.--  dxxEnableColumn() 1. pDAD,        1. data,
    AUT                                 2. trigger_suffix 2. errCode
                                        3. rootid       3. errMsg

    function name     caller            input         output
    validationTrigger.sub.--  dxxEnableColumn() 1. pDAD,      1. errCode,
    VBIT                                2. trigger.sub.--
                                        suffix        2. errMsg

    function name     caller            input           output
    validationTrigger.sub.--  dxxEnableColumn() 1. pDAD,        1. errCode
    VBUT                                2. trigger_suffix 2. errMsg

    function name     caller            input           output
    createDefaultView dxxEnableColumn() 1. pDAD,        1. errCode
                                        2. rootid,      2. errMsg
                                        3. tablename,
                                        4. default_view

    order_tab:
     order_key  customer_name   customer_email  customer_phone
         1      General Motor   parts@gm.com    800-GM-PARTS
    part_tab:
       part_key     color   qty     price      tax    order_key
          156       red     17    17954.55    0.02        1
          68        black   36    34850.16    0.06        1
          128       red     28    38000.00    0.07        1
    ship_tab:
    date          mode      comment                   part_key
    1998-03-13    TRUCK     This is the first shipment to     156
                            service of GM.
    1999-01-16    FEDEX     This the second shipment to     156
                            service of GM.
    1998-08-19    BOAT      This shipment is requested     68
                            by a call. from GM
                            marketing.
    1998-08-19    AIR       This shipment is ordered by     68
                            an email.
    1998-12-30    TRUCK     NULL                         128

    order.sub.--  customer.sub.--  customer.sub.--  part.sub.--
      key    name       email          key  color qty   price    tax    ship_id
          date     mode
       1    General     parts@gm.com   68   red   36  34850 16  0.06
     4.58.825484 1998-08-19  BOAT
            Motor
       1    General     parts@gm.com   68   red   36  34850.16  0.06
     4.58.825537 1998-08-19  AIR
            Motor
       1    General     parts@gm.com   128  red   28  38000.00  0.07
     4.58.825589 1998-12-30  TRUCK
            Motor

    <?xml version="1.0"?>
    <!DOCTYPE Order SYSTEM
     "E:.backslash.dxx.backslash.test.backslash.dtd.backslash.litem.dtd">
    <Order key="1">
        <Customer>General Motor</Customer>
        <Part key="68">
            <Quantity>36</Quantity>
            <ExtendedPrice>34850.16</ExtendedPrice>
            <Tax>0.06</Tax>
            <Shipment>
                 <ShipDate>1998-04-12</ShipDate>
                 <ShipMode>BOAT</ShipMode>
             <Comment>This shipment is requested by a
                call from GM marketing</Comment>
            </Shipment>
            <Shipment>
                 <ShipDate>1998-08-19</ShipDate>
                 <ShipMode>AIR</ShipMode>
             <Comment>This shipment is ordered by an
                email</Comment>
            </Shipment>
        </Part>="128">
            <Quantity>28</Quantity>
            <ExtendedPrice>38000.00</ExtendedPrice>
            <Tax>0.07</Tax>
            <Shipment>
                 <ShipDate>1998-12-30</ShipDate>
                 <ShipMode>TRUCK</ShipMode>
             <Comment>This is the first shipment to
                service of GM</Comment>
            </Shipment>
        </Part>
    </Order>

    Litem_DAD2.dad
        <?xml version="1.0"?>
        <!DOCTYPE Order SYSTEM "E:.backslash.dtd.backslash.dxxdad.dtd">
        <DAD>
        <dtdid>E:.backslash.dtd.backslash.lineItem.dtd</dtdid>
        <validation>YES</validation>
        <Xcollection>
        <SQL_stmt>
        SELECT o.order_key, customer, p.part_key, qty, price, tax, ship_id,
     date, mode,comment
        FROM order_tab o, part_tab p,
                table(select substr(char(timestamp(generate_unique())).16) as
                       ship_id,date,mode,comments from ship_tab) as s
        WHERE p.price > 2500.00 and s.date > "1996-06-01" AND
                    p.order_key = o.order_key and s.part_key = p.part_key
        </SQL_stmt>
        <objids>
            <column name="order_key"/>
            <column name="part_key"/>
            <column name="ship_id"/>
        </objids>
        <prolog>?xml version="1.0"?</prolog>
        <doctype>!DOCTYPE Order SYSTEM
     "E:.backslash.dtd.backslash.lineItem.dtd"</doctype>
        <root_node>
            <element_node name"Order">
                <attribute_node name"Key">
                    <column name="order_key"/>
                </attribute_node>
            <element_node name="Customer">
                <text_node>
                    <column name="customer"/>
                </text_node>
            </element_node>
            <element_node name="Part">
                <attribute_node name="Key">
                    <column name="part_key"/>
                </attribute_node>
                <element_node name="Quantity">
                    <text_node>
                       <column name="qty"/>
                    </text_node>
                </element_node>
                <element_node name="ExtendedPrice">
                    <text_node>
                       <column name="price"/>
                    </text_node>
                </element_node>
                <element_node name="Tax">
                    <text_node>
                       <column name="tax"/>
                    </text_node>
                </element_node>
                <element_node name="Shipment">
                    <element_node name="ShipDate">
                       <text_node>
                           <column name="date"/>
                       </text_node>
                    </element_node>
                    <element_node name="ShipMode">
                       <text_node>
                           <column name="mode"/>
                       </text_node>
                    </element_node>
                    <element_node name="Comments">
                       <text_node>
                           <column name="comment"/>
                       </text_node>
                    </element_node>
                </element_node> <!-- end Shipment -->
            </element_node> <!-- end Part -->
        </element_node> <!-- end Order -->
    </root_node>
    </Xcollection>
    </DAD>