热爱学习，更热爱生活！ 欲无后悔先修己，各有来因莫羡人
全部博文（5）
2013年（5）
分类： 其他UNIX
2013-02-18 13:42:09
Summary: This article presents several examples of using RPG to leverage the SQL/XML function delivered as part of IBM? DB2? for i 7.1. It shows how to embed SQL statements into an RPG program to perform relational queries that involve XML data and produce XML results. The SQL/XML publishing functions and the recently announced XMLTABLE function are demonstrated in this article.
Date: 26 Sep 2012
Level: Introductory
PDF: A4 and Letter (284 KB | 16 pages)
Activity: 3104 views
Comments: 2 (View | Add comment - Sign in)
Using RPG to exploit DB2 XML support
Modernizing and web-enabling applications are some of the important goals for any business, and a challenge for enterprises that have been around for a while. These companies usually want to focus their IT efforts on improving the infrastructure that has brought them success in the past, rather than writing new applications. On the IBM i platform, modernizing frequently means web enabling a database application that is written in Integrated Language Environment (ILE) RPG. Due to the large number of XML standards that exist for transmitting data over the web, having XML capabilities available in RPG is very important. What an RPG programmer needs to realize is that when DB2 for i introduced a native XML data type, every language that supports embedded SQL (including RPG) received a whole set of new options for dealing with XML alongside traditional database data.
Using data from an XML document in a relational query
Let us suppose that I have an application that tracks customer orders in a relational database table. To accomplish this task, I need to create the following ORDERS table in my schema (RPG_ORDERS), using SQL and insert some records into it:
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.ibm.com',
'2012-04-15 13:00:00',
'Camera',
999.50
),
('ntl@us.ibm.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) ;
|
My web-based application provides an XML document to my program requesting that a report be generated for orders that have a matching email address and were made within a specific time period.
An example request document is shown in Listing 2.
|
My first RPG program retrieves an XML information request from a file and uses it to produce a report in a spool file. We could imagine that we are reading this data from standard input, or from a socket — but for a simple example, a stream file is enough to get the idea.
Implementing the query in SQL allows the DB2 optimizer to determine the best way to retrieve the relational data, and revise that determination if another access plan that can perform better becomes available later on. This makes it highly desirable to use SQL as much as possible to retrieve data from the database.
A less-than-satisfying solution that has been done before is to extract the values from the XML document into host variables using RPG opcodes, convert the values into an SQL appropriate type, and finally use an SQL query to retrieve the relational records. Special consideration of type conversions is usually necessary with this approach, as XML data types often times do not share the same lexical representation as their SQL equivalents. For example, the data type for xs:dateTime does not have the same lexical format as the SQL timestamp data type, nor does it match a native RPG timestamp type.
In this example, the XML-INTO or XML-SAX opcode could be used to extract the email address and timestamp range into host variables, followed by some data-type conversion processing, and (finally) an SQL query to retrieve the matching records from the database table. This solution involves significant processing outside of the DB2 awareness, and introduces a greater risk of error.
Another option that has been used in the past is to use a transformation function to transform an XML document into an SQL query string. The query string is later prepared and run. This is often a complex process that has the same problem as the previous solution. It also has the additional concern that a malicious user might be able to construct an XML document that causes an unintended SQL statement to run.
With the new XML support built into DB2, the XML handling can be included in an embedded SQL query that returns the requested relational data. The embedded query handles the data type conversions and ensures that the data obtained from the XML document is only used as values in the query.
SELECT
ORDER_ID,
ORDER_TS,
TRIM(PRODUCT),
PRICE,
TRIM("CustEmail")
FROM RPG_ORDERS.ORDERS,
XMLTABLE('OrderInfoRequest'
PASSING XMLPARSE(DOCUMENT GET_XML_FILE(:file_name))
COLUMNS "CustEmail" VARCHAR(255),
"MinTs" Timestamp,
"MaxTs" Timestamp
) info_req
WHERE
ORDERS.CUST_EMAIL = INFO_REQ."CustEmail" AND
ORDERS.ORDER_TS >= INFO_REQ."MinTs" AND
ORDERS.ORDER_TS <= INFO_REQ."MaxTs";
|
The GET_XML_FILE function returns a BLOB from the stream file at the specified path. The XML data in the BLOB has been converted to UTF-8 encoding. The function is a handy way to get XML data into the database from an IFS path and at the same time, avoids the need to copy the XML data from the file into a host variable.
The XMLPARSE function is used to convert the BLOB data into an instance of the XML data.
In the query in Listing 3, XMLTABLE returns a result set with one row for each OrderInfoRequest, having the columns CustEmail, MinTs, and MaxTs from the XML document that has been retrieved from the file. Because I have joined the result set from XMLTABLE with my database table, I can use this query to fetch the records that I am interested in.
The complete listing of the program is included in Listing 4.
HALWNULL(*USRCTL) DEBUG(*YES) MAIN(PRINTRPT) DFTACTGRP(*NO) ACTGRP(*NEW)
* Output File, we don't need an input file
FQPRINT O F 107 PRINTER
*********************************************************************
* These Variables are used to hold the values for each column of the
* Result Rows
*********************************************************************
D ORDERID S 9 0
D ORDER_TS S Z
D PRICE S 9 2
D PRODUCT S 25A
D CUST_Email S 25A
********************************************************************
* The XML Document is read from this file on disk
********************************************************************
D file_name S 27A INZ('/home/ntl/xml_input_doc.xml')
********************************************************************
* These variables are used to hold information about an SQL error if
* an error should occur.
********************************************************************
D SQLMESSAGE S 3200A varying
D SQLMESSAGEL S 107A
D SQLMESSAGEI S 10I 0 INZ(0)
D RETSCODE S 5P 0
D*
DPRINTRPT PR EXTPGM('PRINTRPT')
*
*
**********************************************************************
* Page Header and Column Headings
********************************************************************
OQPRINT E Headings 1 2
O 8 'PAGE'
O PAGE 13
O 63 'MATCHING ORDER REPORT'
O 96 'DATE'
O *DATE Y 107
O E Headings 1
O 15 'ORDER ID'
O 21 'EMAIL'
O 52 'TIMESTAMP'
O 79 'PRICE'
O 89 'PRODUCT'
O E Headings 1
O 15 '--------'
O 21 '-----'
O 52 '---------'
O 79 '-----'
O 89 '-------'
**************************************************************
* Order Details
**************************************************************
O E Detail 1
O ORDERID N 15
O Cust_Email 41
O ORDER_TS 69
O PRICE N 81 '$'
O PRODUCT 107
*
**************************************************************
* These lines are output if an SQL Error Occurs.
* It includes the SQL code and message text
**************************************************************
O E RPTERR 2
O 20 'An Error Occurred!'
O E RPTERR 2
O 10 'SQL CODE: '
O RETSCODE P 38
O E RPTERR_Msg 2
O SQLMESSAGEL 107
***************************************************************
***************************************************************
* PROCEDURE IMPLEMENTATION
***************************************************************
***************************************************************
PPRINTRPT B
D PI
*
*
* Need commitment control for working with LOB locators
*
C/Exec SQL
C+ Set Option commit=*CHG
C/End-Exec
/FREE
// declare the cursor
// This cursor is the key for everything, it handles both the parsing
// of the input XML document, the extraction of the data into columns,
// and the conversion of the columns to the correct SQL data types.
exec sql DECLARE C1 CURSOR FOR
SELECT ORDER_ID, ORDER_TS, TRIM(PRODUCT), PRICE,
TRIM("CustEmail")
FROM RPG_ORDERS.ORDERS,
XMLTABLE('OrderInfoRequest'
PASSING XMLPARSE(DOCUMENT GET_XML_FILE(:file_name))
COLUMNS "CustEmail" VARCHAR(255),
"MinTs" Timestamp,
"MaxTs" Timestamp
) info_req
WHERE
ORDERS.CUST_EMAIL = INFO_REQ."CustEmail" AND
ORDERS.ORDER_TS >= INFO_REQ."MinTs" AND
ORDERS.ORDER_TS <= INFO_REQ."MaxTs";
// open cursor
exec sql OPEN C1;
// output spool file headings
EXCEPT Headings;
// fetch first row
exec sql FETCH C1 INTO :ORDERID, :ORDER_TS, :PRODUCT, :PRICE,
:Cust_Email;
// for each successful fetch
DOW SQLCOD = 0;
// output details
EXCEPT Detail;
// fetch next row
exec sql
FETCH C1 INTO :ORDERID, :ORDER_TS, :PRODUCT, :PRICE,
:Cust_Email;
ENDDO;
////////////////////////////////////////////////////////////////////
// check for error
////////////////////////////////////////////////////////////////////
IF SQLCOD <> 100 ; // 100 is no more records, which is what we expect
exec sql GET DIAGNOSTICS CONDITION 1
:SQLMESSAGE = MESSAGE_TEXT,
:RETSCODE = DB2_RETURNED_SQLCODE;
// dump sqlcode and message into report
EXCEPT RPTERR;
// Print the SQL Message text in lines of 100 bytes
DOW SQLMESSAGEI < %len(SQLMESSAGE);
IF %len(SQLMESSAGE) - SQLMESSAGEI > %len(SQLMESSAGEL);
SQLMESSAGEL = %subst(SQLMESSAGE:SQLMESSAGEI+1:%len(SQLMESSAGEL));
ELSE;
SQLMESSAGEL = %subst(SQLMESSAGE:SQLMESSAGEI+1);
ENDIF;
SQLMESSAGEI = SQLMESSAGEI + %len(SQLMESSAGEL);
EXCEPT RPTERR_Msg;
ENDDO;
endif;
/////////////////////////////////////////////////////////////////////
// close cursor and spool file
/////////////////////////////////////////////////////////////////////
exec sql close C1;
CLOSE(E) *ALL;
// program complete
EVAL *INLR = *ON;
RETURN;
/END-FREE
P E
|
The spooled file that results from the program and the sample data is shown in Figure 1.
Creating an XML response from relational data
A second scenario needs to be considered. Suppose I have the same type of information request, but this time I want to send the requestor an XML response, instead of generating a spool file report from the query.
The trick here is to use our XML publishing functions to construct the XML response document. The simplest method is to use a common table expression to build the inner parts of the XML document, and then construct the outer layers of the document by arrogating the inner pieces.
The SQL query used in this second program is shown in Listing 5.
WITH matching_orders as (
SELECT
XMLELEMENT(NAME "MatchingOrder",
XMLFOREST(ORDER_ID AS "OrderId",
CUST_EMAIL AS "CustEmail",
ORDER_TS AS "OrderTs",
PRODUCT AS "Product",
PRICE AS "Price")
) AS ORDER
FROM RPG_ORDERS.ORDERS,
XMLTABLE('OrderInfoRequest'
PASSING XMLPARSE(DOCUMENT GET_XML_FILE(:file_name))
COLUMNS "CustEmail" VARCHAR(255),
"MinTs" Timestamp,
"MaxTs" Timestamp
) info_req
WHERE
ORDERS.CUST_EMAIL = INFO_REQ."CustEmail" AND
ORDERS.ORDER_TS >= INFO_REQ."MinTs" AND
ORDERS.ORDER_TS <= INFO_REQ."MaxTs"
)
SELECT
XMLSERIALIZE(
XMLDOCUMENT(
XMLELEMENT(NAME "InfoRequestResponse",
XMLAGG(matching_orders.ORDER)
)
) AS VARCHAR(15000) CCSID 37 INCLUDING XMLDECLARATION
) AS RESPONSE
INTO :ResultXML
FROM matching_orders ;
|
For the request document in listing 2, the resulting document will look like listing 6.
|
Using the publishing functions to construct an XML document makes it a lot easier to create a well-formed XML document from relational data. The data types are automatically converted from SQL to XML. The constructor functions always ensure that each XML tag is well formed. DB2 manages the encoding of the XML document and makes sure that the encoding declaration is correct when the document is serialized.
Listing 7 contains the complete RPG example. It includes the code to load the information request from a stream file, perform the query, and write out an XML response to an output stream file. We can easily imagine how the example could be extended to perform the input and output using sockets or HTTP connections instead of files.
You should be aware that Listing 6 has been formatted in this article for readability. As the indenting and formatting of an XML document is not required by the standard, nor is it required by a consumer, DB2 will not add line breaks or indentation to the document during serialization. If you want to see an XML file formatted for display, you can easily open it in a web browser or an XML editor. When no style sheet is available, most web browsers will display the XML as a document tree.
This particular program encodes the XML response in CCSID 37 (EBCDIC). An actual web application is more likely to use UTF-8, but 37 is easier to work with from green-screen interfaces, and it is a trivial change to alter the program to work with CCSID 1208 instead.
A limitation in this example program is that the result document cannot be greater than about 15 K in size. If truncation or any other SQL problem occurs, the sample program constructs an XML document that includes the SQL error information and uses that as a response. A real program could of course have some better handling routines.
The method used to create a response for errors is interesting. Some developers might find the approach of using embedded SQL to create an XML document from host variables to be simpler to write than a traditional string concatenation solution, as it is easier to ensure that the XML document remains well formed.
Listing 7 contains the completed program.
HALWNULL(*USRCTL) DEBUG(*YES) MAIN(SENDRPTX) DFTACTGRP(*NO) ACTGRP(*NEW)
********************************************************************
* The XML Document is read from this file on disk
********************************************************************
D file_name S 27A INZ('/home/ntl/xml_input_doc.xml')
********************************************************************
* The XML Response is written to this file on disk
********************************************************************
D resp_file S 28A INZ('/home/ntl/xml_output_doc.xml')
********************************************************************
* Result XML Value
* This has the limitation of only supporting 15000 bytes.
* We could use an SQL BLOB to support up to 2G, but for this example
* 15K is more than enough.
********************************************************************
D ResultXML S 15000A varying
********************************************************************
* These variables are used to hold information about an SQL error if
* an error should occur.
********************************************************************
D SQLMESSAGE S 3200A varying
D RETSCODE S 5P 0
*
D fd S 10I 0
D rc S 10I 0
*
DSENDRPTX PR EXTPGM('SENDRPTX')
*
* Definition of IFS Open Procedure
D open PR 10I 0 extproc('open')
D path * value options(*string)
D oflag 10I 0 value
D mode 10U 0 value options(*nopass)
D codepage 10U 0 value options(*nopass)
* Definition of IFS Write Procedure
D write PR 10I 0 extproc('write')
D filedes 10I 0 value
D buf * value
D nbyte 10U 0 value
* Definition of IFS Close Procedure
D close PR 10I 0 extproc('close')
D filedes 10I 0 value
*
***************************************************************
***************************************************************
* PROCEDURE IMPLEMENTATION
***************************************************************
***************************************************************
PSENDRPTX B
D PI
*
*
* Need commitment control for working with LOB locators
*
C/Exec SQL
C+ Set Option commit=*CHG
C/End-Exec
/FREE
exec sql declare :ResultXml VARIABLE CCSID 37;
exec sql declare :SQLMESSAGE VARIABLE CCSID 37;
// This query is the key for everything, it handles both the parsing
// of the input XML document, the extraction of the data into columns,
// the conversion of the columns to the correct SQL data types for
// the join, in addition, this query generates the XML result
exec sql
WITH matching_orders as (
SELECT
XMLELEMENT(NAME "MatchingOrder",
XMLFOREST(ORDER_ID AS "OrderId",
CUST_EMAIL AS "CustEmail",
ORDER_TS AS "OrderTs",
PRODUCT AS "Product",
PRICE AS "Price")
) AS ORDER
FROM RPG_ORDERS.ORDERS,
XMLTABLE('OrderInfoRequest'
PASSING XMLPARSE(DOCUMENT
GET_XML_FILE(:file_name)
)
COLUMNS "CustEmail" VARCHAR(255),
"MinTs" Timestamp,
"MaxTs" Timestamp
) info_req
WHERE
ORDERS.CUST_EMAIL = INFO_REQ."CustEmail" AND
ORDERS.ORDER_TS >= INFO_REQ."MinTs" AND
ORDERS.ORDER_TS <= INFO_REQ."MaxTs"
)
SELECT
XMLSERIALIZE(
XMLDOCUMENT(
XMLELEMENT(NAME "InfoRequestResponse",
XMLAGG(matching_orders.ORDER)
)
) AS VARCHAR(15000) CCSID 37 INCLUDING XMLDECLARATION
) AS RESPONSE
INTO :ResultXML
FROM matching_orders;
////////////////////////////////////////////////////////////////////////
// check for error
// If error, build invalid request response
////////////////////////////////////////////////////////////////////////
IF SQLCOD <> 0 ;
exec sql GET DIAGNOSTICS CONDITION 1 :SQLMESSAGE = MESSAGE_TEXT,
:RETSCODE = DB2_RETURNED_SQLCODE;
exec sql VALUES
XMLSERIALIZE(
XMLDOCUMENT(
XMLELEMENT(NAME "SQLError",
XMLELEMENT(NAME "SQLCODE",
:RETSCODE),
XMLELEMENT(NAME "MESSAGE",
:SQLMESSAGE)
)
)
AS VARCHAR(15000) CCSID 37
) INTO :ResultXML;
ENDIF;
/////////////////////////////////////////////////////////////////////
// Write response into a stream file
/////////////////////////////////////////////////////////////////////
EVAL fd = open(resp_file: 74 : 511);
EVAL rc = write(fd:%addr(ResultXML)+2:%len(ResultXML));
EVAL rc = close(fd);
// program complete
EVAL *INLR = *ON;
RETURN;
/END-FREE
P E
|
Performing a query over an XML column
There is one more important case to consider when dealing with XML. Sometimes, XML documents might be stored in a DB2 column that is using the XML data type. The reason for this is that when there is a need to preserve every relationship defined by the document, it can be very difficult to define a relational model that keeps a complete representation of each document. If we keep the data in the XML model, then XMLTABLE can be used to perform relational queries over the XML data as needed. Using XMLTABLE is very useful for converting the XML data to the result set needed for a particular SQL query, assuming the XML data cannot be shredded to a relational model in advance. (The performance of using XMLTABLE is not as good as a relational query over relational data.)
Complex queries over XML data are possible in these scenarios. For more complicated queries, XMLTABLE supports many built-in functions and operators. These allow for advanced queries, rather than the simple extraction we have seen so far.
For the sake of discussion, let us assume that I will store the information requests received in a database table's XML column, and I currently have created a table and inserted the records described in Listing 8.
CREATE TABLE order_info_requests(
request_id BIGINT GENERATED ALWAYS AS IDENTITY
(START WITH 1 INCREMENT BY 1) NOT NULL,
request XML NOT NULL,
request_ts TIMESTAMP DEFAULT CURRENT TIMESTAMP,
PRIMARY KEY (request_id) );
INSERT INTO order_info_requests(request)
VALUES
('
|
I would now like a spool file report that shows, for order information requests on each user, the average number of days between the minimum and maximum timestamps. This information might give me some insights into how customers are using my application and what kind of improvements can be made.
Without XMLTABLE, this might take a lot of code. Fortunately, the supported XPath syntax has a rich set of built-in functions for making these calculations.
I can do this calculation with the SQL query in listing 9.
SELECT AVG(X.DAYS) AS AVG_DAY_RANGE, X.CUSTOMER
FROM RPG_ORDERS.ORDER_INFO_REQUESTS,
XMLTABLE('OrderInfoRequest'
PASSING ORDER_INFO_REQUESTS.REQUEST
COLUMNS
CUSTOMER VARCHAR(255) PATH 'CustEmail',
DAYS INTEGER PATH
'fn:days-from-duration(xs:dateTime(MaxTs) - xs:dateTime(MinTs))'
) X
GROUP BY X.CUSTOMER ORDER BY AVG_DAY_RANGE DESC;
|
The built-in operators for the dateTime arithmetic and the built-in function
fn:days-from-duration
are used in this example to calculate the number of days between the
MaxTs and MinTs dateTime values in the XML document. The integer result
is returned in the Days Column.
The completed program is shown in Listing 10.
HALWNULL(*USRCTL) DEBUG(*YES) MAIN(PRINTAVG) DFTACTGRP(*NO) ACTGRP(*NEW)
* Output File, we don't need an input file
FQPRINT O F 107 PRINTER
*********************************************************************
* These Variables are used to hold the values for each column of the
* Result Rows
*********************************************************************
D CUST_Email S 25A
D Average S 10I 0
********************************************************************
* These variables are used to hold information about an SQL error if
* an error should occur.
********************************************************************
D SQLMESSAGE S 3200A varying
D SQLMESSAGEL S 107A
D SQLMESSAGEI S 10I 0 INZ(0)
D RETSCODE S 5P 0
*
DPRINTAVG PR EXTPGM('PRINTAVG')
*
*
**********************************************************************
* Page Header and Column Headings
**********************************************************************
OQPRINT E Headings 1 2
O 8 'PAGE'
O PAGE 13
O 64 'AVERAGE NUMBER OF DAYS'
O 96 'DATE'
O *DATE Y 107
O E Headings 1
O 15 'AVG DAYS'
O 21 'EMAIL'
O E Headings 1
O 15 '--------'
O 24 '--------'
**************************************************************
* Order Details
**************************************************************
O E Detail 1
O Average N 15
O Cust_Email 41
*
**************************************************************
* These lines are output if an SQL Error Occurs.
* It includes the SQL code and message text
**************************************************************
O E RPTERR 2
O 20 'An Error Occurred!'
O E RPTERR 2
O 10 'SQL CODE: '
O RETSCODE P 38
O E RPTERR_Msg 2
O SQLMESSAGEL 107
***************************************************************
***************************************************************
* PROCEDURE IMPLEMENTATION
***************************************************************
***************************************************************
PPRINTAVG B
D PI
*
*
* Need commitment control for working with LOB locators
*
C/Exec SQL
C+ Set Option commit=*CHG
C/End-Exec
/FREE
// declare the cursor
// This cursor is the key for everything, it handles both the parsing
// of the input XML document, the extraction of the data into columns,
// and the conversion of the columns to the correct SQL data types.
//
exec sql DECLARE C1 CURSOR FOR
SELECT AVG(X.DAYS) AS AVG_DAY_RANGE, X.CUSTOMER
FROM RPG_ORDERS.ORDER_INFO_REQUESTS,
XMLTABLE('OrderInfoRequest'
PASSING ORDER_INFO_REQUESTS.REQUEST
COLUMNS
CUSTOMER VARCHAR(255) PATH 'CustEmail' ,
DAYS INTEGER PATH
'fn:days-from-duration(xs:dateTime(MaxTs) - xs:dateTime(MinTs))'
) X
GROUP BY X.CUSTOMER ORDER BY AVG_DAY_RANGE DESC;
// open cursor
exec sql OPEN C1;
// output spool file headings
EXCEPT Headings;
// fetch first row
exec sql FETCH C1 INTO :AVERAGE, :CUST_EMAIL;
// for each successful fetch
DOW SQLCOD = 0;
// output details
EXCEPT Detail;
// fetch next row
exec sql FETCH C1 INTO :AVERAGE, :CUST_EMAIL;
ENDDO;
////////////////////////////////////////////////////////////////////////
// check for error
////////////////////////////////////////////////////////////////////////
IF SQLCOD <> 100 ; // 100 is no more records, which is what we expect
exec sql GET DIAGNOSTICS CONDITION 1 :SQLMESSAGE = MESSAGE_TEXT,
:RETSCODE = DB2_RETURNED_SQLCODE;
// dump sqlcode and message into report
EXCEPT RPTERR;
// Print the SQL Message text in lines of 100 bytes
DOW SQLMESSAGEI < %len(SQLMESSAGE);
IF %len(SQLMESSAGE) - SQLMESSAGEI > %len(SQLMESSAGEL);
SQLMESSAGEL = %subst(SQLMESSAGE:SQLMESSAGEI+1:%len(SQLMESSAGEL));
ELSE;
SQLMESSAGEL = %subst(SQLMESSAGE:SQLMESSAGEI+1);
ENDIF;
SQLMESSAGEI = SQLMESSAGEI + %len(SQLMESSAGEL);
EXCEPT RPTERR_Msg;
ENDDO;
endif;
/////////////////////////////////////////////////////////////////////
// close cursor and spool file
/////////////////////////////////////////////////////////////////////
exec sql close C1;
CLOSE(E) *ALL;
// program complete
EVAL *INLR = *ON;
RETURN;
/END-FREE
P E
|
Summary
We have seen three examples where embedded SQL has been included into an RPG program to perform database activities that involve both the relational and XML data models. We have seen how to include XML data in an SQL query, and how to produce an XML document from an SQL query. Also, this article demonstrated the idea that XMLTABLE is not limited to simple extraction and can be used for more complex queries of the XML data.
Hopefully, you are convinced that there is a great deal of value in the new SQL/XML support in DB2 for i 7.1, it really does make it much easier to integrate XML and relational data together into an application. This article is only the beginning, and the SQL/XML support in DB2 for i is substantial enough to receive its own book in the IBM i information center, and it can take some time and effort to master.
Finding ways to use the new technology is worth the effort and resources. A good option that IBM i customers have is the . Lab Services can help with training and consulting, which for some customers may be the best path to a modernized application.
Resources
There is also very detailed information on SQL/XML for IBM i on the web. You can use the following online references to get more information.
Articles on XML data model for IBM i:
XML Meets DB2 for i: Getting Started With the XML Data Type Using DB2 for IBM i
Using XML With DB2 for i: How to Use XML Data Type With DB2 for IBM i
About the author
Nick Lawrence is an Advisory Software Engineer at IBM in Rochester, Minnesota. His responsibilities include IBM DB2 for i development, including SQL/XML, XPath, and Full Text Search.You can reach him at .
Average rating (8 votes)