Home | Business News | Browse by Publication | X | XML Journal

Advanced ANSI SQL native XML integration--Part 1: a full, natural, and seamless process.

Article Excerpt
This two-part article will change your view and understanding of standard SQL and its ability to integrate naturally and fully with native XML. The perceived problem with achieving full SQL-based integration of XML is that relational data is flat while XML data is hierarchical, producing a huge impediment to a seamless solution.

This belief has prevented a full integration solution, resulting in SQL vendors resorting to nonstandard SQL and external code, whose solutions fall far short of full XML integration. The usual method of integration used by SQL vendors is to shred or flatten the XML data in order to join it relationally with the relational data. This produces major efficiency problems for processing and memory utilization and ignores the hierarchical semantics in the XML data, which can be valuable.

This article demonstrates how standard ANSI SQL-92 can perform very sophisticated hierarchical processing by naturally raising the SQL level of processing to a hierarchical level. This allows SQL to integrate fully with native XML data at a full hierarchical processing level, even exceeding the nonprocedural hierarchical capabilities found in XML query languages while remaining ANSI SQL standard. This seamless XML integration is important because SQL developers require little or no training and SQL customers trust and feel comfortable with ANSI SQL.

Hierarchical Processing

A new and powerful capability that SQL hierarchical processing brings to XML is the nonprocedural hierarchical querying and processing of their multi-leg hierarchical structures. This means that the hierarchical semantics in the XML data and its structure are automatically used in the processing of the query; for example, the selecting of data in one leg of the hierarchical structure based on the value of data from another leg of the structure. Most XML query languages are based on XPath, which is primarily single path (leg) oriented and would have a difficult time correlating the semantics between two legs. But as this article will demonstrate, even this complex level of multi-leg hierarchical processing is automatically available in ANSI standard SQL syntax and semantics and can be seamlessly and directly applied to integrating native XML documents. In fact, these advanced hierarchical capabilities can perform complex XML operations such as hierarchical node promotion and fragment processing naturally and nonprocedurally in SQL.

Remarkably, the ANSI SQL capabilities that allow SQL to inherently perform full hierarchical processing come together and build on each other seamlessly, producing a truly unified and natural XML integration solution. To demonstrate this, several examples--each encompassing many aspects of this unified solution--will be used. In this way it's possible to demonstrate the big picture of SQL's complete hierarchical operation and full integration of native XML. The examples will show an unbroken progression from query start to finish of exactly how this complete and seamless XML integration is accomplished.

In Figure 1, an XML document and relational tables are joined into a larger hierarchical structure that is hierarchically processed automatically by the invoking ANSI standard SQL query. The relational tables are hierarchically modeled and joined hierarchically with the SQL modeled XML structure. The completed multi-leg structure then undergoes hierarchical data selection. The result can be returned in a relational row set whose flat result accurately reflects its hierarchically preserved data...