Integrating XML into SQL requires two types of integration for a complete seamless and accurate solution. Fully integrating Relational and XML data is very important to get the correct result without any data loss. And the proper integration of SQL and XML operations is also necessary. But these were not being fully satisfied today. It created different external proprietary solutions because the mapping between SQL and XML operation was not totally valid and it also introduced less accurate results because the data integration problem has not been completely solved. ANSI SQL’s hierarchical processing solves both of these integration problems by using the commonality of hierarchical processing in XML and SQL. Lets examine the integration of relational and XML data. By modeling relational flat data as hierarchical structures it becomes possible to integrate relational data logically modeled hierarchically with XML physical hierarchical structures. This is performed by defining relational and XML data both as hierarchical structures using SQL’s hierarchical oriented Left Outer Join. This is done below defining a hierarchical modeled XML view named XML and a relationally modeled hierarchical view named RDB. The only difference in the XML and relational view is the VIEW Keyword in the XML view definitions is defined as XVIEW so that it knows to access the data as XML and to know to map Element types to tables and Element data types to columns. This is a physical XML view definition, but it is externally treated as a SQL logical view. These views are shown below in the RDB and XML views.
Model Relational View RDB
CREATE VIEW RDB AS R SELECT * FROM R | LEFT JOIN D ON R.r=D.d D LEFT JOIN B ON D.d=B.b | B
Model XML View XML
CREATE XVIEW XML AS SELECT * FROM X X LEFT JOIN M ON X.x=M.m / \ LEFT JOIN L ON X.x=L.l M L
The above two hierarchical SQL views consisting of the relational logical view and the XML physical view are themselves hierarchically joined to create the heterogeneous logical view below. Remember, the XML physical view is treated as a standard logical view externally. The view join operations are not performed until the view is invoked in real-time.This Heterogeneous view is shown below.
Create Heterogeneous View
CREATE VIEW RDBXML AS SELECT * FROM RDB LEFT JOIN XML ON R.r=X.x
The modeled heterogeneous view above generated the SQL that defines its joined structures shown below. Notice how the lower level XML view is nested under the upper relational view RDB in its natural SQL view expansion with the automatic placement of the ON clauses. This causes the lower XML view to be fully materialized before being joined to the upper relational view. This happens whether the lower structure is logical or physical. But in the case of a lower level physical structure this turns out to be exactly what is needed to naturally simplify the processing of fixed physical structures which naturally operate as materialized structures. This is shown directly below in the expanded view of RDBXML directly above.
Integrating Relational and XML Data
SELECT * FROM R R LEFT JOIN D ON R.r=D.d / \ LEFT JOIN B ON D.d=B.b D X LEFT JOIN X | / \ LEFT JOIN M ON X.x=M.m B M L LEFT JOIN L ON X.x=L.l ON R.r=X.x
The unified logical expanded view directly above and SQL’s hierarchical processing of it means that the XML data and relational data are being processed the same. In addition, they are both being performed hierarchically. This means there is no data loss. Normally with XML and relationally processing the integration solution is to flatten the hierarchical data, losing valuable semantics. In the solution presented here, the opposite is performed; the relational data is elevated to a hierarchical level through hierarchical data modeling which increases the semantics.
SQL and XML Operation Integration
SELECT R, B, X, L FROM RDB LEFT JOIN XML ON R.r=X.x
The SQL statement above submits a query using the relational and XML view directly to demonstrate that heterogeneous processing can be performed dynamically. In addition, the SELECT list specifies only certain data fields in the nodes to be returned, unselected nodes are not output. This can produce unnecessary pathways that do not require access. With hierarchical structures and their hierarchical data preservation, unnecessary pathways can be ignored without affecting the results. The external expansion for this query is shown directly below.
SELECT R, B, X, L FROM R LEFT JOIN D ON R.r=D.d R LEFT JOIN B ON D.d=B.b / \ LEFT JOIN X B X LEFT JOIN L ON X.x=L.l | ON R.r=X.x L
Notice in the expanded view directly above that the Left Outer join for the M node is removed since the M node is not selected for output or necessary to the query. The D node is still accessed even though it is not output because it is required to navigate to the B node. This natural mapping of the SQL query to operate hierarchical solves the SQL/XML operational integration. Again this is solved because SQL and XML are both operating hierarchically so this mapping is being performed one-to-one producing the correct hierarchical result. This is because the SQL rowset result and the XML external result are both hierarchical allowing for a one-to-one operational mapping. This is how the ANSI SQL inherent hierarchical processing solves both the relational/XML data integration and the SQL/XML external operational integration.