RE: SQL to XQuery translation

David and Murali,

You've opened the door for some interesting conversations!

At 13:13 2004-01-15 Thursday, Michael Rys wrote:

>Some reasons why there is not much done in this area:
>
>1. SQL is not powerful enough alone to query true XML data where mixed
>content, order, changing structure really matters.

I agree with Michael (even though I would have said "traditional SQL" or 
used some other clarifying adjective).

>You would have to
>extend SQL fundamentally to allow this. There is some work going on at
>the relational database companies and the ANSI/ISO standards level.

This effort is called "SQL/XML" and its first efforts just reached fruition 
on 9 December, 2003 with the publication of SQL:2003, particularly part 14, 
SQL/XML (ISO/IEC 9075-14:2003, for those of you who like such formalities).

>However, the focus there is mainly to leverage XPath and XQuery in the
>context of SQL expressions. So mapping SQL to XQuery would also mean
>that you define  relational semantics over the XML data, which you
>cannot easily do for general XML documents (see the discussions of
>edge/node-tables to represent XML relationally for some of the
>complexity).

Again, I agree with Michael, although I am somewhat less pessimistic since 
I work for a company that has actually delivered products that accomplish a 
lot of this --- using proprietary technologies, I emphasize, and not 
standardized facilities.  So, my summary here is that it can be done, but 
that there is nothing already standardized or currently in the 
standardization pipeline to do it.

>2. In order to query relational data marked up in XML, most commercial
>systems provide a mapping level into XML (annotated schemata in SQL
>Server 2000, XSD-based OR mapping in Oracle 9i, DADs in IBM DB2) and
>then just let you use SQL to query it. So no need to map it to XQuery
>since the primary implementation architecture is SQL.

Absolutely correct.

>If it would have been easy, I guess we would not have invested into
>XQuery in the first place but just defined a relational semantics on XML
>and be done with it...

Wouldn't that have been nice ;^)

My way of expressing the overall problem is that SQL was designed to manage 
data that is, by definition, fully described by "structural metadata" (the 
SQL schemas, table definitions, etc.) and is completely regular (no 
"missing" data elements, even though data can be missing and represented by 
null values).  By contrast, XML is used to represent semi-structured data 
in which entire components can be unrepresented in any way, and there is no 
inherent requirement that any descriptive (structural) metadata be 
available to describe the XML.

As a consequence, unextended SQL is simply the wrong tool...it's the hammer 
that shouldn't be used to tighten the bolt.  Similarly, XQuery is excellent 
for querying arbitrary XML documents, but cannot really "get at" SQL data 
unless that SQL data is somehow "published" into an XML form...it's the 
crescent wrench that shouldn't be used to drive that nail.

My personal favorite solution is, of course, to combine the two tools in a 
way that allows me to query both types of data from whichever viewpoint I 
need for a given application.  And that is the ultimate goal of SQL/XML -- 
allowing the use of SQL and XML together.

Now, as it happens, I do know of at least one XQuery implementation that 
translates XQuery expressions into SQL (well, technically, it's not into 
actual SQL language as character strings, but into a parsed representation 
of SQL).  The translation seems to be pretty good overall, but the job of 
building that translation software was challenging ;^)  I have no idea 
whether that particular XQuery implementation will ever be commercialized, 
much less industrial strength; it was apparently done (I don't have any 
inside information...sorry) as a proof of concept and not as a prototype of 
a product.

At 13:26 2004-01-15 Thursday, Murali Mani wrote:


>Michael,
>
>Some questions regarding Query Language translations propped up recently.
>This was one that we could not answer..
>
>When you do XQuery -> SQL, how do you translate predicates?
>
>a predicate in XPath such as
>
>path [predicate]/ ...
>
>we need existential semantics over a potential result set.. does this get
>translated to a subquery in SQL???

I don't have the cycles to discuss this in detail, as it could be a long 
discussion.  However, predicates in XQuery/XPath do have reasonable 
correspondences to predicates in SQL.  If your "[predicate]" is something 
simple, such as "[string(b)='abcdef']", then that would have a directly 
corresponding analog in SQL, like "WHERE CAST (b AS CHARACTER VARYING(100)) 
= 'abcdef'".  More complex XPath predicates would have more complex 
"translations" to SQL, but there are not many such XPath predicates that 
don't have pretty reasonably corresponding SQL predicates.


>best, murali.
>
>-------------------------
>
>Note: for SQL -> XQuery might be easier (if we consider simple queries)..
>
>for example,
>SELECT <Arr1>
>FROM <Arr2>
>WHERE <Arr3>
>
>can be translated to (the way we teach SQL semantics in DB I ..)
>
>for <Arr2'>
>where <Arr3'>
>return <Arr1'>
>
>i think it is not difficult to translate these Arr2 -> Arr2' etc..?? Are
>there things that I am overlooking??

In simple cases, it is certainly not difficult.  Nested for loops in XQuery 
correspond to joins (or, equivalently, creative use of subqueries) in 
SQL.  XQuery's where clause corresponds to SQL's WHERE clause.  Many 
correspondences are obvious (or nearly so...there are often some 
complications), while others might be quite elusive.  It's inappropriate to 
expect a single generalized answer to this.

Hope this helps,
    Jim

========================================================================
Jim Melton --- Editor of ISO/IEC 9075-* (SQL)     Phone: +1.801.942.0144
Oracle Corporation            Oracle Email: mailto:jim.melton@oracle.com
1930 Viscounti Drive          Standards email: mailto:jim.melton@acm.org
Sandy, UT 84093-1063              Personal email: mailto:jim@melton.name
USA                                                Fax : +1.801.942.3345
========================================================================
=  Facts are facts.  However, any opinions expressed are the opinions  =
=  only of myself and may or may not reflect the opinions of anybody   =
=  else with whom I may or may not have discussed the issues at hand.  =
========================================================================

Received on Thursday, 15 January 2004 15:50:18 UTC