Get the most out of this SQL/XML function

Vitor Rodrigues (vrodrig@us.ibm.com), DB2 pureXML Technical Enablement, IBM

Vitor
Rodrigues is a software developer at the IBM Silicon Valley Lab. He
graduated from University of Minho, Portugal, in Computer Science and
Systems Engineering. He joined IBM in 2005 as an intern working on DB2
Everyplace and DB2 9 pureXML. Vitor was a part of the DB2 9 QA team for
pureXML, where he acquired deep knowledge of the XML features in DB2 9.
After his internship, Vitor became a regular employee, now working for
the DB2 9 XML Enablement team.

Matthias Nicola (mnicola@us.ibm.com), DB2/XML Performance, IBM Silicon Valley Laboratory

Dr.
Nicola is the technical lead for XML database performance at IBM’s
Silicon Valley Lab. His work focuses on all aspects of XML performance
in DB2, including XQuery, SQL/XML, and all native XML features in DB2.
Dr. Nicola works closely with the DB2 XML development teams as well as
with customers and business partners who are using XML, assisting them
in the design, implementation, and optimization of XML solutions. Prior
to joining IBM, Dr. Nicola worked on data warehousing performance for
Informix Software. He also worked for four years in research and
industry projects on distributed and replicated databases. He received
his doctorate in computer science in 1999 from the Technical University
of Aachen, Germany.

Summary: XMLTABLE is one of the most powerful functions in the SQL/XML standard and is
available in IBM® DB2® 9 for Linux®, Unix®, and Windows® as well
as DB2
9 for z/OS®.
In part
1

of this two-part series on XMLTABLE you learned how to use XMLTABLE to
retrieve XML data in relational format, how to deal with repeating or
missing XML elements, and how to handle namespaces in the XMLTABLE
function. Part 2 describes common XMLTABLE usage scenarios, such as
shredding XML into relational tables, splitting large documents into
smaller ones, producing hybrid XML-relational storage of your XML
documents, and using relational views over XML data. These techniques
and samples will help you develop powerful XML applications with DB2 9
pureXML.

View more content in this series

Date: 13 Sep 2007

Level: Intermediate

Activity: 4114 views

Comments: 0 (Add comments)

1 star2 stars3 stars4 stars5 stars Average rating (based on 5 votes)

Sample data

For the discussion of XMLTABLE scenarios in this article, the same sample table and
data is used as in part 1 of this series.
The table contains one XML column and two rows with one XML document per row.
The first document describes a department with two employees, the second document a department with just one employee.

Table 1. Sample table and data

create table emp (doc XML);

XMLTABLE can be used for a variety of purposes other than the basic usage of returning XML data in relational format.


Back to top

Split big XML documents into smaller documents

Most programmers find it convenient and efficient to work with an XML document granularity that matches the
logical business objects of the application and the predominant granularity of access.
For example, a single document per purchase order, per trade, per contract, per tax return,
per customer is usually a good idea. Smaller documents can be manipulated more efficiently than large ones,
especially with XML design and authoring tools. Also, indexed access and data retrieval is faster for smaller documents.

However, for a bulk transfer of XML data outside the database, such as FTP,
it is often not convenient to handle thousands or millions of separate documents.
Therefore, it is common to receive large XML documents, often multiple hundred Megabytes per file,
which contain many repeating blocks that represent independent objects.
Many external tools fail, or have severe problems, when you try to open such large XML documents,
typically due to document object model (DOM) parsing and memory limitations.

DB2 can ingest XML documents up to 2GB. Optionally, you can split them into smaller documents using the XMLTABLE function.
As an example, use the data in Table 1, where employee data is aggregated by department, with multiple employees per documents.
Query 1 is embedded in an INSERT statement and extracts each employee from a department document.
Each employee is inserted into the table employee as a separate XML document in a separate row.

Query 1. Split documents into smaller pieces using XMLTABLE

The XMLTABLE function in this query produces one row per employee and a single column of type XML.
The “?” in the passing clause denotes a parameter marker that provides the XML document as input to the statement.
The cast (? as XML)
is used to cast the parameter marker to type XML. Instead of using a
parameter marker, you can also use literal XML documents as input to a
statement, but that is usually clumsy and impractical for all but very
small documents. Parameter markers are preferred.

The XML data model requires a parsed, well-formed XML document to have one document node (as a parent of the single root element of the document).
This document node is not visible in the textual (serialized) representation of an XML document.
The employee sub-trees extracted from an input document does not have document nodes, and
hence cannot be inserted as well-formed documents.
Therefore, the document{} constructor creates a document node for each extracted employee.


Back to top

Decompose XML documents into relational data

Using XMLTABLE-queries in INSERT statements is also a convenient way of decomposing, or shredding,
XML data into a relational table. For example, sometimes XML is only
used as a message format and does not need to be retained once the
message is received. If the XML payload of the message can easily be
mapped to existing relational tables to feed your SQL applications,
then shredding XML to relational can be the right approach. However, in
scenarios with complex and variable XML schemas this is a lot harder,
so that pureXML storage is a better option than shredding.

Following the previous example, Query 2 shows how XMLTABLE is
used to shred the employee data into a new relational table, employeeRel, with one row per employee.

Query 2. Use XMLTABLE to decompose XML data into relational format

In this scenario, you are only shredding a subset of the elements in your documents.
Phone and salary elements were not shredded, but that can easily be added.
For now, a SELECT statement from the table employeeRel returns the following output:

It is also possible to shred XML documents from an existing XML column.
In Query 2, you would simply pass a column name instead of the
parameter marker into the row-generating expression, as you see in Query 5 below as
well as in most of the queries in part 1 of this series.


Back to top

Hybrid approach: XML and relational

There
are situations in which it makes sense to have some of your data in
relational format and some in XML format. For structured and fixed data
items that are unlikely to change over time, relational columns may be
a good choice. Other parts of your data may be semi-structured,
variable, or inherently hierarchical and therefore better stored in an
XML column. A common way to achieve this hybrid approach at insert time
is to extract selected elements or attributes from each XML document
and place them into relational columns in the same row where you insert the full document.
On the relational columns, you can then define multi-column indexes (composite keys), referential integrity constraints,
or triggers, since these concepts are not or only partially available for XML columns.

There are various ways to populate the relational columns in your table along with the XML column.
You can do the value extraction in your application and feed both the XML document and the
extracted values into an SQL INSERT statement.
But, it’s often easier and simpler to just use XMLTABLE in an INSERT statement.
This avoids XML parsing in your application and keeps the logic in the INSERT statement.
If any changes are needed in the future, only this statement — possibly encapsulated in a
stored procedure — needs to be modified instead of altering the application code.

Query 3 shows how the department documents in Table 1 can be inserted with extraction of the bldg attribute into a separate column of type INTEGER.
Note that the row-generating expression $d does not include any
navigation, meaning no XPath steps.
This means that the context of the COLUMNS clause is always a full document including its document node.

Query 3. Insert XML data and extract one attribute into a relational column

Query 4 inserts the same XML documents as Query 3, but it extracts the employee ID, first and last name,
and office number into relational columns.
It splits up the department documents and produces one row per employee with one XML document per employee.
The new documents are fragments of the input documents and therefore require the construction of document nodes.

Query 4. Insert XML data and store multiple elements in relational columns


Back to top

Create a relational view over XML data

In
the previous sections, you saw how to use XMLTABLE to shred XML data to
relational tables or to produce a hybrid representation of your XML
data. The same XMLTABLE expressions can be embedded in view
definitions. This allows you to provide applications with a relational
or hybrid view of the XML data without actually storing the data in
that respective format.

Query 5
is embedded in a view definition and exposes a subset of the XML
elements and attributes from the column doc in a relational format.
Applications can use this view to query the data with traditional SQL
statements, even though the actual underlying data is in XML format.

Query 5. Use a view to expose your XML data in relational format

If you are
familiar with relational views, you will easily recognize the first
line of the statement that defines the view name and its columns. The
query expression in the AS clause defines the content of the view. In
this example, XMLTABLE is used to produce one row for each employee in
the XML data. Each row is composed of four relational columns
containing the employee ID, first name,
last name, and office number, respectively.

Query 6
shows how you can run a simple SQL statement (with no XML extensions)
against this view to obtain the first name and ID of the employee in
office 344.

Query 6. Use SQL to query the XMLTABLE view

Be aware that when you run such SQL queries with SQL predicates against an XMLTABLE view,
DB2 first generates all the relational rows for the view and then evaluates the SQL predicate(s).
This means that the XMLTABLE function is applied to all documents in the underlying
table, even though the SQL query requires data from only one particular document.
This can adversely affect performance.

In
particular, DB2 does not use an XML index to evaluate the SQL predicate
because the predicate is expressed on a relational column of the view
and not on the underlying base table. It is, in general, not straight
forward for a database system to convert relational predicates into XML
predicates. In the next section, you’ll see how to work around this
restriction and still achieve good performance.


Back to top

Performance considerations for XMLTABLE views

The
key to obtaining good runtime performance for queries over XMLTABLE
views is the proper use of indexes to reduce the number of XML
documents that are input to the XMLTABLE function. There are two ways
of doing this:

  • Using XML predicates with XML indexes
  • Using relational predicates on relational columns with indexes

Since Query 6 has a predicate on office, which is ultimately an element in the underlying XML data,
you may want to speed up the query by creating an index on /dept/employee/office for the underlying XML column.
A query does not use this index unless you express the predicate as an XML predicate.
This requires the XML column to be included in the view definition, as shown in Query 7.

Query 7. Include the XML column in the XMLTABLE view

Then you can express Query 6 with an XML predicate that can use the XML index to provide a significant performance improvement.
This is shown in Query 8. Although an XML predicate is used, the
SELECT list still references only relational view columns to return the query result in relational format.

Query 8. Query the XMLTABLE view with an XML predicate

You can also
speed up queries against XMLTABLE views if the underlying base table
has relational columns in addition to the XML column, which is actually
quite common. In that case, you should include these extra relational
columns in the view definition. In Query 9 the columns deptID and unit have been included in the view.

Query 9. Include relational column in the XMLTABLE view

Chances are that your queries for employee information will include predicates on the deptID, or on the unit, or both.
If these predicates are sufficiently selective, then the cost of the XMLTABLE processing is significantly reduced.
In Query 10, a predicate on deptID
has been added. DB2 evaluates this predicate first so that the XMLTABLE
function is only applied to the employees in department 9473. This is a
small set compared to the total size of the table. Among those, the
second predicate finds the employee in office 344 quickly.

Query 10. Query the XMLTABLE view with relational predicates


Back to top

Number the rows produced for a document

In some cases, you may want to generate a column that numbers the rows which XMLTABLE produces for any given document.
This can help your application to remember the order in which the values appeared in each document.
You can achieve this with the “for ordinality” clause that is supported in DB2 9.5.
Query 11
demonstrates this functionality. Note that the numbering starts with 1
for each document that is input to the XMLTABLE function.

Query 11. An XMLTABLE query with a sequence number produced by “for ordinality”

Running this query in DB2 returns the following result:


Back to top

List field-value pairs

Another real-world XML application requirement involves listing all field/value pairs that exist in a given
XML document — without making assumptions about its structure or the attribute and element names.
This can be achieved with the XMLTABLE function.

To produce a relational table that contains two columns (tagname, value), you need
to traverse all elements and attributes in your
XML documents and extract the name and value of each element and attribute.
Query 12 shows a generic way to return all attribute/value and element/value pairs from the XML data.
This generic query can be modified to meet more specific needs.

Note the row-generating expression $d//(*, @*) in Query 12.
The double-slash ( //) recursively traverses the XML document to every level.
Expressions such as $d//* or $d//@* would return either all elements or all attributes, respectively, but not a mixed sequence of both.
The expression $d//(*, @*) returns all elements and attributes.

Query 12. List all attributes and elements with their values

For each node in your XML data, Query 12 returns a relational row with the name and value of the node.
The XML data model defines that the value of a non-leaf node is the concatenation of all descendant text nodes.
This is visible in the result of Query 12:

Since the concatenated values are not very useful and redundant with other entries,
you can write smarter expressions for the COLUMNS clause to produce a more organized result set.

Query 13. List all triplets of name-type-value
for all XML nodes in your documents

The difference between Query 12 and Query
13
is a new column-generating
expression for the column value and the new column named type.
Just like the row-generating expression can be any
XQuery expression, the same is true for the column-generating
expressions as long as they each return a scalar value that is castable
to assigned SQL data type.

In the value column, you now only return each element’s immediate text node children without concatenation of its descendant text nodes.
This is achieved with the function text().
For attribute nodes, use the function data() instead of text, since
attribute values don’t live in separate text nodes.
This article doesn’t use the function data() for element nodes because it would
also return the element’s descendant text nodes.
The predicate ( .[self::element()]/text(),.[self::attribute()]/data(.)) is used to achieve your needs.
If the current node is an element, its text node is returned using the
text() function. If the current node is an attribute, the
data() function is used to return its value.

The other major difference from Query 12 introduced in Query 13 is the new column type.
In this column, you return the type of the current node (meaning the node kind, not the data type).
Consider three types: element, leaf element, and attribute.
The distinction between these three types is done based on the following rules:

  • If the node is of type element() and has child nodes ( ./* evaluates to true), then it is of type ELEMENT.
  • If the node is of type element() and has no child nodes ( ./* evaluates to false), then it is a LEAF-ELEMENT.
  • In the remaining cases, the node is of type ATTRIBUTE.

Note that these rules are only correct because your XML documents contain no other
node types.
If our XML data contained comments or processing instructions, then the above rules would have to be extended.

Executing Query 13, you get as a result a relational table containing the triplets name-type-value for all XML nodes in your documents:

The type column in the result from Query 13 also allows you
to count how many nodes of each kind are in your XML documents.
This is possible because the result of XMLTABLE is a relational table and can be input to any SQL operations.
Adding a count() and a group by clause to Query 13,
you get the following query:

Query 14. Count the total number of XML nodes of each type

Query 14 will generate the following summary table:

This makes it easy to get detailed statistics about the XML documents stored in your database.
Just like you used the SQL count() and group by clause on top of XMLTABLE, you can apply any SQL capabilities to the rowset that XMLTABLE produces.
For example, you use the SQL order by clause to sort the output of the XMLTABLE function.


Back to top

Summary

The XMLTABLE function is a very versatile tool for processing XML data in SELECT and INSERT statements.
In this article, you have seen how XMLTABLE can be deployed to split big XML documents into small ones,
to perform simple XML shredding, or to produce a hybrid representation of your XML data.
This two-part series on XMLTABLE contains a wide range of SQL/XML statement samples that you may
be able to use as templates for some of your own application development.


Back to top

Acknowledgements

Thanks to Cindy Saracco for her review and helpful comments on this article.

Resources

Learn

Get products and technologies

  • Now you can use DB2 for
    free. Download DB2 Express-C,
    a no-charge version of DB2 Express Edition for the community that
    offers the same core data features as DB2 Express Edtion and provides a
    solid base to build and deploy applications.

  • Build your next development project with
    IBM
    trial software
    , available for download directly from developerWorks.

  • Download a free trial version
    of DB2
    Enterprise 9
    .

Discuss

About the authors

photo2

Vitor
Rodrigues is a software developer at the IBM Silicon Valley Lab. He
graduated from University of Minho, Portugal, in Computer Science and
Systems Engineering. He joined IBM in 2005 as an intern working on DB2
Everyplace and DB2 9 pureXML. Vitor was a part of the DB2 9 QA team for
pureXML, where he acquired deep knowledge of the XML features in DB2 9.
After his internship, Vitor became a regular employee, now working for
the DB2 9 XML Enablement team.

Author photo: Matthias Nicola

Dr.
Nicola is the technical lead for XML database performance at IBM’s
Silicon Valley Lab. His work focuses on all aspects of XML performance
in DB2, including XQuery, SQL/XML, and all native XML features in DB2.
Dr. Nicola works closely with the DB2 XML development teams as well as
with customers and business partners who are using XML, assisting them
in the design, implementation, and optimization of XML solutions. Prior
to joining IBM, Dr. Nicola worked on data warehousing performance for
Informix Software. He also worked for four years in research and
industry projects on distributed and replicated databases. He received
his doctorate in computer science in 1999 from the Technical University
of Aachen, Germany.

Leave a Reply

电子邮件地址不会被公开。 必填项已用*标注