I was frequently asked by customers as well as IBMers, "what is missing without XQuery?" I'd say that using SQL/XML with XPath support in DB2 9 for z/OS, you miss almost nothing significant in terms of functionality (and performance), and you even have capabilities beyond XQuery 1.0 in SQL/XML! You can build sophisticated applications processing XML data using SQL/XML with XPath today.
XPath is used to navigate within an XML document, but it cannot construct a new document. XQuery includes XPath as its sub-language for the navigational capability. XQuery includes FLWOR expressions and document constructors as two other major features for more complex joins and document generation, among other expressions, such as if-then-else.
SQL/XML queries can embed XPath expressions. An XPath expression embedded inside the XMLQUERY() scalar function can extract pieces of documents. SQL XMLTABLE() function is one of the most powerful functions in processing XML, and with XPath, it provides the "FLW" (for-let-where) capability of a FLWOR expression with "tuples" as the result. SQL XMLAGG() function together with SQL XML constructor functions provides the "OR" (order by-return) part of a FLWOR expression. You can pretty much produce the same result as XQuery using SQL/XML. To prove that, I have converted almost all the queries in W3C XQuery use cases to SQL/XML with XPath (except for 2 due to missing fn:namespace-uri() from DB2 9 for z/OS). The queries are downloadable from here, and executable on DB2 9 for z/OS.
What about performance? You may ask. From DB2 for Linux, UNIX, and Windows (LUW) experiences, SQL/XML queries usually perform equally well or better. For example, when you need to have some grouping beyond the natural hierarchy in XML data, in XQuery, you need to use fn:distinct-values() first, and then for each value find interested aggregate data, which is expensive. In SQL, you can use explicit GROUP BY clause. This is where SQL/XML goes beyond XQuery. Furthermore, you can use XMLTABLE() function to create relational views on XML data, and apply all available SQL functionality over that data for reporting and analytics, including BI functions. This is another area where SQL/XML goes beyond XQuery 1.0. See there articles about XMLTABLE() part 1 and part 2 for more details of its usage.
Looking beyond the languages themselves, we see SQL has well established standardized application environment support. XQuery could be used in any environment with XML input and produce XML as output. However, the XQuery environment takes time to standardize, build and mature. If you want XQuery to access relational data or hybrid relational-XML data, there is no native interface of its own. Use XQuery as the primary (top-level) language embedded/invoked in other host languages? Not many vendors support that from the popular database servers, and no standardized interfaces except for Java, which did not gain much acceptance. XQuery supported by DB2 for LUW as a top-level language does not return rows and does not support host variables or parameter markers yet like in SQL. In contrast, SQL/XML makes extending existing applications with XML capability much easier.
All these reasons led to SQL/XML being the dominant language for XML database application development today. That being said, XQuery adopts a new language style to weave construction with other expressions, which is very nice for document generation. You will be able to use the XQuery features in our next major release.