The XMLTABLE() function is probably the most powerful function in manipulating XML data using SQL/XML. Some of its uses include, but not limited to, the following:
- to achieve XQuery capability using SQL/XML with XPath. See SQL/XML queries for XQuery usecases.
- to decompose a large document into smaller documents for storage.
- to generate relational view over stored XML data, to bridge the gap between XML storage and relational application software, and also provide reporting over XML data.
Since there are other articles giving detailed account of XMLTABLE function, for example, Matthias's two-part articles of "XMLTABLE By Examples" Part 1 and Part 2, I will just list the highlight on using the XMLTABLE function here:
- XMLTABLE and XMLCAST were delivered post V9 GA. Apply PK51571, PK51572, and PK51573 before trying the functions.
- In general, XMLTABLE() returns multiple rows of multiple columns. The column types can be non-XML SQL types and XML type.
- The column value of non-XML type has to contain at most one item only. Otherwise, an error sqlcode : -16003 sqlstate: 10507 will be returned. In this case, you need to lower the level of nodes the row expression identifies, and use "." (self) in the column path expression if necessary.
- Use XMLNAMESPACES to declare namespaces that are needed across the row path expression and column path expressions, like this: XMLTABLE(XMLNAMESPACES(), ...). The namespaces declared in the row path expression do not cover column path expressions (according to the SQL standard).
- Avoid using the parent axis ("..") due to its overhead. If you need to lower the level, try to cascade XMLTABLE functions. The first XMLTABLE() produces rows at a high level so that the parent axis can be avoided, and the next XMLTABLE function takes as the input the XML column output of the previous XMLTABLE, and generates other columns.
- If you use XMLTABLE to decompose a document into smaller documents, you need to use XMLDOCUMENT() constructor to wrap the pieces into a document to insert into an XML column.
- You can use XMLTABLE() to extract one row (a tuple) of multiple columns, in particular, multiple heterogeneous pieces from different portions of a document, to be used in constructing a new document.
- You can use XMLTABLE() to iterate through a sequence of homogeneous items that XMLQUERY was not able to separate them, each to be used in constructing a new document. XMLAGG() can be used to aggregate multiple parts back into one value.
- You can use XMLTABLE() to define a relational view so SQL queries can treat XML data as relational data, to bridge the gap between XML and relational. This can also achieve more than XQuery 1.0 can do for reporting and light analytics. For heavy analytics, you may need to materialize XMLTABLE result into tables. MQT support for XMLTABLE() is not available at this point.
- You can use XMLTABLE() and XMLPARSE() to consume Web services directly in SQL by using SOAPHTTPNV or SOAPHTTPNC UDFs in DB2 (more to come).
If you have any questions, don't hesitate to contact me or post questions at DB2 pureXML forum.
-Guogen (Gene) Zhang (GGZ)