Use the SQL/XML functions provided by IBMDB2fori7.1 through RPG

This article shows several examples of Using SQLXML functions provided by IBMDB2fori7.1 through RPG. This document describes how to embed SQL statements into RPG programs to execute relational queries involving XML data and generate XML results. This article demonstrates the SQLXML release function and the recently released XMLTABLE function.

This article shows several examples of using the SQL/XML functions provided by IBM DB2 for I 7.1 through RPG. This document describes how to embed SQL statements into RPG programs to execute relational queries involving XML data and generate XML results. This article demonstrates the SQL/XML Release function and the recently released XMLTABLE function.

For any enterprise, application modernization and Web Support are extremely important goals, but they also bring some challenges. Typically, these enterprises want to focus their IT resources on improving the infrastructure that has been successfully used in the past, rather than writing new applications. On the IBM I platform, modernization usually means changing RPG applications to Web-based applications. Because there are a large number of XML standards for data transmission through the Web, it is vital to provide the XML function in RPG. RPG programmers must realize that the native XML data type introduced by DB2 for I is to support embedded SQL (including RPG) various programming languages provide a complete set of new options to process both XML and traditional data.

Use data from XML documents in link query

Suppose I have an application that uses it to track customer orders in relational database tables. To complete this task, I need to use SQL to create the following ORDERS table in the mode (RPG_ORDERS) and insert some records in it:

Listing 1. SQL creation and insertion statements

 CREATE TABLE rpg_orders.orders( order_id BIGINT GENERATED ALWAYS AS IDENTITY(                 START WITH 1000 INCREMENT BY 1                 NO MINVALUE NO MAXVALUE                 NO CYCLE NO ORDER CACHE 20 ),Cust_email VARCHAR(50), Order_ts   TIMESTAMP , Product    VARCHAR(50) CCSID 37, Price      DOUBLE PRECISION , PRIMARY KEY(order_id) )   ; INSERT INTO rpg_orders.orders (cust_email, order_ts, product, price)VALUES('ntl@us..com', '2012-04-15 13:00:00', 'Camera',  999.50),('ntl@us..com', '2012-04-16 12:00:00', 'lens', 500.25),('ntl@us.ibm.com', '2012-04-01 11:00:00', 'Book',  15.00),('george@nowhere.com', '2012-04-15 13:05:00', 'Book', 20) ;

For a program that requests to generate a report for an order that has a matched email address and completed within a specified period of time, this Web-based application provides an XML document.

Listing 2 provides a sample request document.

List 2. Sample Information Request Document

 
   
  
   ntl@us.ibm.com
    
  
    2012-04-14T:00:00:00 
    
  
    2012-04-30T23:59:59 
  
 

My first RPG program retrieves an XML Information Request from the file and uses it to generate a report in the off-line file. It can be assumed that we are reading this data from the standard output or socket-but to keep the example concise, the stream file is sufficient to describe related concepts.