IBM has included support for the XMLTABLE table function in SQL. XMLTABLE is designed to convert an XML document into a relational result set (rows and columns) using popular XPath expressions. This function has been referred to as the Swiss army knife for working with XML because it can help solve a wide variety of XML related problems.
A good tutorial of what XMLTABLE is and how it works can be found in the SQL XML Reference in the Knowledge Center. The examples show how XMLTABLE can be used to perform a query over XML data in a Db2 column.
What may not be obvious from the documentation is that the XML support included in Db2 for i can be of enormous help for Web-enabled applications, even when all of the data in the database is stored in a pure relational model.
Consider this example:
Suppose I have an SQL table that I created with the following SQL statement:
create table orders( order_id bigint generated always as identity (start with 1000 increment by 1), cust_email varchar(255), order_ts timestamp, product varchar(255), primary key (order_id));
And assume that I’ve inserted the following rows:
insert into orders (cust_email, order_ts, product)
values
('ntl@us.ibm.com',
'2012-04-15 13:00:00',
'Camera'),
('ntl@us.ibm.com',
'2012-04-16 12:00:00',
'lens'),
('ntl@us.ibm.com',
'2012-04-01 11:00:00',
'Book'),
('george@nowhere.com',
'2012-04-15 13:05:00',
'Book') ;
My application will receive a request from the Web (in XML format) that contains a customer email and a date range. The application is expected to query the table and return matching orders in an XML format.
For simplicity, let’s suppose I’ve retrieved my request XML document from the Web and stored it in a global variable:
create or replace variable order_request xml; set order_example.order_request = xmlparse(document ' ntl@us.ibm.com 2012-04-14T00:00:00 2012-04-30T23:59:59 ' );
For my sample data and the request above, let’s assume a valid response needs to be structured like this:
1000
ntl@us.ibm.com
2012-04-15T13:00:00.000000
Camera
1001
ntl@us.ibm.com
2012-04-16T12:00:00.000000
lens
Without the SQL/XML support in 7.1, this could be a complex task that would involve both an SQL query and some external code to deal with the XML aspects. However, this can now be accomplished with a single SQL query.
The first thing to understand is that we can join the SQL table to a result set generated by an XMLTABLE invocation.
The SQL query that follows returns only the rows that match the specifications within the provided XML OrderInfoRequest document:
select ORDER_ID,
CUST_EMAIL,
ORDER_TS,
PRODUCT
from
orders,
xmltable('OrderInfoRequest'
passing order_example.order_request
columns "CustEmail" varchar(255),
"MinTs" Timestamp,
"MaxTs" Timestamp
) info_request
where
orders.cust_email = info_request."CustEmail" and
orders.order_ts >= info_request."MinTs" and
orders.order_ts <= info_request."MaxTs";
Creating the XML response document can be done by combining the above query with SQL publishing functions. Each of the rows generated by the above query must be converted into “MatchingOrder” elements. The rest of the document must then be constructed around those values.
This might seem complicated, but the trick is to construct the document “inside out,” using common table expressions and arrogations to create the inner values that are repeated.
The following query is all we need to satisfy the application’s requirement.
-- intermediate result set of
-- “MatchingOrder” Elements
with matching_orders as (
select
XMLELEMENT(NAME "MatchingOrder",
XMLFOREST(ORDER_ID AS "OrderId",
CUST_EMAIL AS "CustEmail",
ORDER_TS AS "OrderTs",
PRODUCT AS "Product")
) AS ORDER
from orders,
xmltable('OrderInfoRequest'
passing order_example.order_request
columns "CustEmail" varchar(255),
"MinTs" Timestamp,
“MaxTs" Timestamp
) info_request
Where
orders.cust_email = info_request."CustEmail" and
orders.order_ts >= info_request."MinTs" and
orders.order_ts <= info_request."MaxTs"
)
-- build InfoRequestResponse element
-- around matching orders
select
XMLSERIALIZE(
XMLDOCUMENT(
XMLELEMENT(NAME "InfoRequestResponse",
XMLAGG(matching_orders.ORDER)
)
) AS CLOB(1G) CCSID 1208 INCLUDING XMLDECLARATION
)
from matching_orders;
You may have noticed that the XML xs:dateTime is not a valid lexical format for an SQL timestamp (and vice versa). This is an annoying problem for hand-written solutions, but the SQL/XML functions handle the conversions between the XML types and SQL types automatically for us. Designing the code with SQL/XML ensures that the result is well-formed XML.
The learning curve for using these XML functions can be a little steep. But in the long run, using this support will make the process of modernizing an application to incorporate Web technologies easier. Avoiding the need to write special purpose code to include XML data in your SQL queries makes it easier to adapt and expand the scope of the application as the Web-facing components evolve.
Technology Updates information on XMLTABLE:
https://www.ibm.com/support/pages/node/1167424
This week’s blog article was written by Nick Lawrence. Nick works for IBM in Rochester, Minn., on DB2 for IBM i. His responsibilities include full-text search for DB2 and SQL/XML. Thanks, Nick!
This blog post was edited ti fix broken links on February 18, 2020.
This blog post was originally published on IBMSystemsMag.com and is reproduced here by permission of IBM Systems Media.