ILE RPG and SQL Data Types – Part 2. XML_CLOB_FILE
Hello everyone and welcome to part 2 of SQL Data Types in ILE RPG.
In this second part of SQL Data Types in ILE RPG there is only one target: XML_CLOB_FILE. I think it is important enough to dedicate a whole post to it. Through two examples we will see two different ways to generate and save well-formed XML content in the archive in IFS.
If you remember Part 1 which can be found at ILE RPG and SQL Data Type I showed you how you could write data to an IFS file. That data could be character or binary. For character data type it could be plain text or, as seen in one of the examples in the article, an HTML file that could be opened in any browser. Perhaps, seeing that you could write in a markup language like HTML, you came up with the idea of writing XML data in the file. If that was the case you will have realized that things did not go as expected and that the file did not save well. In any case, if you want to know how to save an XML file in the IFS using the XML_CLOB_FILE data type, read on.
Large Objects Files (LOB Files) – Revisited
This is exactly the same information that was published at ILE RPG and SQL Data Type
LOB files are used to transfer data from and to IFS files.
They represent a file, they do not contain it. You can use database queries, updates and inserts to store or to retrieve LOB values without the need of routines to read and write files. LOB files are allowed in host data structures and cannot be initialized.
LOB Files variables can be CLOB, DBCLOB or BLOB data type. The precompiler will generate a data structure with subfileds relative to the file using suffixes as follow:
- _NL: Length of the name of the file. Must be specified in the program.
- _DL: Data length. Not used during input. During output is set by the application to the length of the new data that is written to the file.
- _FO: File operation. Must be specified in the program of a fixed list of operations. More on this below.
- _NAME: Name of the file in the ifs. Although you can specify a relative filename it is better if you use a complete path name.
The precompiler will also generate four constants:
- DCL-C SQFRD CONST(2); – The file will be read.
- DCL-C SQFCRT CONST(8); – A new file will be created. If the file exists an error will be sent.
- DCL-C SQFOVR CONST(16); – The file will be overwritten if exists and if not it will be created.
- DCL-C SQFAPP CONST(32); – Data will be appended to the file is exists and if not a new file will be created.
XML CLOB File
The declaration of:
dcl-s xmlClobFile sqltype(xml_clob_file);
generates this code:
DCL-DS XMLCLOBFILE;
XMLCLOBFILE_NL UNS(10);
XMLCLOBFILE_DL UNS(10);
XMLCLOBFILE_FO UNS(10);
XMLCLOBFILE_NAME CHAR(255) CCSID(*JOBRUNMIX);
END-DS XMLCLOBFILE;
At first glance it appears that there is no change from a normal CLOB FILE or non-XML but there is. Let’s go a little further.
XMLPARSE
The XMLPARSE function is used to parse an XML document (a character string containing XML data) and return an XML data type value. In essence, it takes a string representing an XML document and converts it into a structured XML data type that can then be processed or queried using other XML functions.Key Characteristics:
- Input: It expects a character string (e.g., CLOB, VARCHAR) that contains a well-formed XML document.
- Output: It returns a value of the XML data type. This internal representation allows the database to efficiently store, validate, and query the XML content.
- Error Handling: If the input string is not a well-formed XML document (i.e., it has syntax errors), XMLPARSE will typically raise an error, indicating the parsing failure.
- Whitespace Handling: You can control how whitespace is handled during parsing (e.g., preserving it or stripping it).
- Document Type Declarations (DTDs) and Schemas: While XMLPARSE primarily checks for well-formedness, it can also process DTDs. For schema validation, you’d typically use other functions or processes in conjunction with XMLPARSE (e.g., XMLELEMENT with XMLVALIDATE or specific XML schema repository features).
- Ingesting XML Data: When you receive XML data from external sources (e.g., web services, files) as a string, XMLPARSE is the first step to convert it into a manipulable XML data type within your database.
- Storing XML: It’s used before storing XML strings into columns defined with the XML data type.
- Querying XML: Once parsed into the XML data type, you can then use functions like XMLQUERY, XMLTABLE, or XMLCAST to extract information, transform data, or join XML data with relational data.
XMLPARSE(DOCUMENT argument {STRIP WHITESPACE|PRESERVE WHITESPACE})
- DOCUMENT indicates that the input string (argument) represents a complete XML document (as opposed to just a content fragment).
- STRIP WHITESPACEdeletes extra blank spaces between xml labels.
- PRESERVE WHITESPACEleft the string as it is, with all blank spaces between xml labels in case they exist.
- The input must be valid XML — otherwise, the function will raise an error.
XMLGROUP
The XMLGROUP SQL function on IBM i is used to generate an XML document from a set of rows. It’s an aggregation function, meaning it operates on a group of rows and returns a single XML value. This function is particularly useful when you need to transform relational data into a hierarchical XML structure.
Key Features and Limitations of XMLGROUP:
- Empty result set: If the SELECT query returns no rows, the XMLGROUP function returns NULL instead of an empty XML structure. This can cause issues if you expect at least the root element.
- All-NULL rows are skipped: If a row contains only NULL values, it will be completely omitted from the generated XML.
- Individual NULL fields are omitted: Any individual column that is NULL will not appear in the corresponding <row> element. This can result in inconsistent XML structures across rows.
- Unsupported data types: The following types are not allowed as expressions within XMLGROUP: ROWID, DATALINK, and XML types.
- Binary data: Data types like BLOB or VARCHAR FOR BIT DATA are automatically base64-encoded in the resulting XML.
- Element naming restrictions: You can rename elements using AS, but only within the SELECT clause or subqueries—not directly inside XMLROW or XMLATTRIBUTES when used with XMLGROUP.
The syntax is:
XMLGROUP (
expression [ AS elementName ] [, expression [ AS elementName ] ... ]
[ ORDER BY sortSpec [, sortSpec ... ] ]
[ OPTION ROW "rowElementName" ]
[ OPTION ROOT "rootElementName" ]
[ OPTION AS ATTRIBUTES ]
)
- Expression: Any SQL expression will be the value of the element. If the expression is not a simple column a name must be provided with AS elementName.
- ORDER BY: If the order of the XML rows matters, use the ORDER BY clause inside the XMLGROUP function itself—not outside the query.
- OPTION ROW: If not specified the name of each row will be <row>.
- OPTION ROOT: If not specified the name of the root element will be <rowset>.
- OPTION AS ATTRIBUTES: All the values from each row will be written as attributes inside the <row> tag instead of as nested elements.
Putting Things Together
So we have an xml_clob_file which is an sqltype in RPGLE, XMLPARSE which is an scalar SQL Function that takes a character string that contains well-formed XML and turns it into an XML document object that can be used with SQL, XMLGROUP which is an aggregate function that access a table or physical file and generate an XML with its contents and we have the IFS which is the place where we are going to write the XML file. So let me put all these things together in two different and complete examples.
Example 1: Creating and saving an XML File from the contents of a variable
In this first example I will use the XMLPARSE function together with a variable of type varchar that will contain the XML. There are many ways to achieve this but on this case I am going to use manual construction of the xml into a string, parse it to an XML document and save it to the IFS via an xml_clob_file.
XMLCLOBFIL.SQLRPGLE:
**free
ctl-opt option(*nodebugio:*srcstmt:*nounref);
dcl-s xmlBooks varchar(32000) ccsid(*utf8);
dcl-s ifsFile1 sqltype(xml_clob_file);
// The compiler will generate the following:
//
// dcl-ds ifsFile1 inz;
// ifsFile1_nl uns(10);
// ifsFile1_dl uns(10);
// ifsFile1_fo uns(10);
// ifsFile1_name char(255);
// end-ds;
//
// _name = Full path of the file name
// _nl = Length of the file name
// _fo = Operation to be performed on the file (see constants below)
// _dl = Not used
//
// The compiler also adds these constants:
// SQFRD = 2 = Read the file
// SQFCRT = 4 = Create the file
// SQFOVR = 8 = Overwrite the file
// SQFAPP = 16 = Append to the end of the file
// XML start
xmlBooks = '<?xml version="1.0" encoding="UTF-8"?>' +
'<books>' +
'<book>' +
'<title>RPG IV for the Brave</title>' +
'<author>Elliot Randall</author>' +
'<publisher>CodeForge Press</publisher>' +
'<publishedDate>2020-03-15</publishedDate>' +
'<isbn>9781111111111</isbn>' +
'<genre>Programming</genre>' +
'<pages>450</pages>' +
'<price>59.99</price>' +
'<available>Y</available>' +
'</book>' +
'<book>' +
'<title>The IBM i Journey</title>' +
'<author>Nora Blake</author>' +
'<publisher>IronCore Publishing</publisher>' +
'<publishedDate>2018-07-01</publishedDate>' +
'<isbn>9781111111112</isbn>' +
'<genre>Systems</genre>' +
'<pages>300</pages>' +
'<price>39.95</price>' +
'<available>Y</available>' +
'</book>' +
'<book>' +
'<title>SQL Spells for Db2</title>' +
'<author>Liam Carter</author>' +
'<publisher>DataScroll Books</publisher>' +
'<publishedDate>2019-11-10</publishedDate>' +
'<isbn>9781111111113</isbn>' +
'<genre>Database</genre>' +
'<pages>380</pages>' +
'<price>49.95</price>' +
'<available>Y</available>' +
'</book>' +
'</books>';
ifsFile1_name = '/home/asalcedo/xml_test/books.xml';
ifsFile1_nl = %len(%trim(ifsFile1_name));
ifsFile1_fo = SQFOVR;
exec sql set :ifsFile1 = XMLParse(Document :xmlBooks);
if (sqlcode = 0);
snd-msg 'File books.xml created in ' + ifsFile1_name %target(*self:2);
else;
snd-msg 'Error creating books.xml. SQLCODE = ' + %char(sqlcode) %target(*self:2);
endif;
*inlr = *on;
Explanation:
Line 1: Code will be fully free.
Line 3: Control options I usually use.
Line 5: xmlBooks varchar is declared with enough length to hold the XML and more but remember that the length of a varchar variable is only the length of its contents plus 2 or 4 bytes depending on the maximum declared, up to 65535 2 bytes and > 65535 4 bytes. As it will be used to hold an XML the character code identifier is set to UTF-8.
Line 6: ifsFile1 file is declared. Its type is SQL type xml_clob_file so it will hold an XML file.
Lines 7-25: The compiler will generate a data structure to hold the information for the xml_clob_file so I have written, commented and explained, what the compiler generate for us.
Lines 28-63: I am assigning a character string that represent a well-formed XML with the information of three books to xmlBooks.
Line 65: I set the name of the file using a full path in which I want to save the XML in the IFS.
Line 66: I need to set the length of the previous path.
Line 67: Finally I need to set how I want to use the file. If you see the previous comments in the code you can see that the constant SQFOVR means that the file will be created if it doesn’t exists or will be overwritten if it exists.
Line 69: This is the most important statement in the program. First, the XMLParse function will check that the content of xmlBooks is a well-formed XML and return the document. Next I will assign this document to the file using the SQL set statement. At this point the system will check for the existence of the books.xml file in the /home/asalcedo/xml_test directory. If any of these directories are missing, an error will occur. If all of them exist, the books.xml file will be created or overwritten and its content will be set to the document returned by the XMLParse function.
Lines 71-75: If everything went well a message is sent informing that the file has been created correctly and where. If there has been an error, the error code is reported. The snd-msg command uses the %target(*self:2) function to specify that the message, in addition to the joblog, will be received by the program itself but with an offset in the call stack of 2 positions resulting in it being on the message line of the screen.
Once the program runs, a message appears confirming the XML creation. The XML content is shown below
BOOKS.XML:
<?xml version="1.0" encoding="UTF-8"?>
<books>
<book>
<title>RPG IV for the Brave</title>
<author>Elliot Randall</author>
<publisher>CodeForge Press</publisher>
<publishedDate>2020-03-15</publishedDate>
<isbn>9781111111111</isbn>
<genre>Programming</genre>
<pages>450</pages>
<price>59.99</price>
<available>Y</available>
</book>
<book>
<title>The IBM i Journey</title>
<author>Nora Blake</author>
<publisher>IronCore Publishing</publisher>
<publishedDate>2018-07-01</publishedDate>
<isbn>9781111111112</isbn>
<genre>Systems</genre>
<pages>300</pages>
<price>39.95</price>
<available>Y</available>
</book>
<book>
<title>SQL Spells for Db2</title>
<author>Liam Carter</author>
<publisher>DataScroll Books</publisher>
<publishedDate>2019-11-10</publishedDate>
<isbn>9781111111113</isbn>
<genre>Database</genre>
<pages>380</pages>
<price>49.95</price>
<available>Y</available>
</book>
</books>
Example 2: Creating and saving an XML File from the contents of a table
In this second example I will use the XMLGROUP function to access a database table and process all the records in an XML file. The XMLGROUP function allows us to select only the records we need as in an SQL SELECT statement.
Book_info table
Take a look at the contents of the book_info table. When the program generates the XML file the content should be the same.
One constraint is that only books published in 2019 or later will be included in the XML file; therefore, books 2, 4, 7, 10, and 14 should not be part of the XML file.
XMLCLOBFI2.SQLRPGLE:
**free
ctl-opt option(*nodebugio:*srcstmt:*nounref);
dcl-s ifsFile1 sqltype(xml_clob_file);
// Initialize the XML_CLOB_FILE variable
ifsFile1_name = '/home/asalcedo/xml_test/books2.xml';
ifsFile1_nl = %len(%trim(ifsFile1_name));
ifsFile1_fo = SQFOVR;
// Generate the XML and save it to the XML_CLOB_FILE
exec sql
values(select xmlgroup(
book_id as "id",
title as "title",
author as "author",
publisher as "publisher",
published_date as "published_date",
isbn as "isbn",
genre as "genre",
pages as "pages",
price as "price",
available as "available"
order by book_id option row "book" root "books")
from asalcedo1.book_info
where published_date >= '2019-01-01')
into :ifsFile1;
if (sqlcode = 0);
snd-msg 'File books2.xml created in ' + ifsFile1_name %target(*self:2);
else;
snd-msg 'Error creating books2.xml. SQLCODE = ' + %char(sqlcode) %target(*self:2);
endif;
*inlr = *on;
Explanation:
Line 1: Code will be fully free.
Line 2: Control options I usually use.
Line 5: ifsFile1 file is declared. Its type is SQL type xml_clob_file so it will hold an XML file.
Line 8: I set the name of the file using a full path in which I want to save the XML in the IFS.
Line 9: I need to set the length of the previous path.
Line 10: Finally I need to set how I want to use the file. If you see the previous comments in the code you can see that the constant SQFOVR means that the file will be created if it doesn’t exists or will be overwritten if it exists.
Lines 14-28: The SQL statement uses the XMLGROUP function to generate XML elements, mapping table column values to XML tag values. The AS clause assigns specific tag names to each column—for example, the value of the book_id column becomes the value of the <id> tag, and so on.
Each row is represented as a group of XML tags, and by specifying OPTION ROW “book”, each group is wrapped in a <book> element.
To enclose all <book> elements within a single parent element named <books>, the OPTION ROOT “books” clause is used.
Finally, to ensure that the <book> elements are ordered by book_id, the ORDER BY book_id clause must be placed inside the XMLGROUP function, rather than after the WHERE clause of the outer SELECT.
The resulting XML is then stored in the IFS file ifsFile1.
Line 30-34: If everything went well a message is sent informing that the file has been created correctly and where. If there has been an error, the error code is reported. The snd-msg command uses the %target(*self:2) function to specify that the message, in addition to the joblog, will be received by the program itself but with an offset in the call stack of 2 positions resulting in it being on the message line of the screen.
Once the program runs, a message appears confirming the XML creation. The XML content is shown below
BOOKS2.XML:
<?xml version="1.0" encoding="UTF-8"?>
<books>
<book>
<id>1</id>
<title>RPG IV for the Brave</title>
<author>Elliot Randall</author>
<publisher>CodeForge Press</publisher>
<published_date>2020-03-15</published_date>
<isbn>9781111111111</isbn>
<genre>Programming</genre>
<pages>450</pages>
<price>59.99</price>
<available>Y</available>
</book>
<book>
<id>3</id>
<title>SQL Spells for Db2</title>
<author>Liam Carter</author>
<publisher>DataScroll Books</publisher>
<published_date>2019-11-10</published_date>
<isbn>9781111111113</isbn>
<genre>Database</genre>
<pages>380</pages>
<price>49.95</price>
<available>Y</available>
</book>
<book>
<id>5</id>
<title>Node & RPG Synergy</title>
<author>Derek Vaughn</author>
<publisher>FusionStack Press</publisher>
<published_date>2022-09-12</published_date>
<isbn>9781111111115</isbn>
<genre>Web Development</genre>
<pages>410</pages>
<price>55.00</price>
<available>Y</available>
</book>
<book>
<id>6</id>
<title>Microservices on the i</title>
<author>Maya Stevens</author>
<publisher>CloudCore Books</publisher>
<published_date>2023-01-30</published_date>
<isbn>9781111111116</isbn>
<genre>Architecture</genre>
<pages>290</pages>
<price>47.50</price>
<available>Y</available>
</book>
<book>
<id>8</id>
<title>ILE RPG Demystified</title>
<author>Chloe Bennett</author>
<publisher>NextByte Press</publisher>
<published_date>2021-06-01</published_date>
<isbn>9781111111118</isbn>
<genre>Programming</genre>
<pages>330</pages>
<price>42.95</price>
<available>Y</available>
</book>
<book>
<id>9</id>
<title>IBM i Security Blueprints</title>
<author>Julian Foster</author>
<publisher>SecureLayer Publishing</publisher>
<published_date>2019-02-05</published_date>
<isbn>9781111111119</isbn>
<genre>Security</genre>
<pages>310</pages>
<price>46.75</price>
<available>Y</available>
</book>
<book>
<id>11</id>
<title>REST & JSON on IBM i</title>
<author>Landon Myers</author>
<publisher>ServiceStack Press</publisher>
<published_date>2020-12-12</published_date>
<isbn>9781111111121</isbn>
<genre>Web Services</genre>
<pages>295</pages>
<price>44.00</price>
<available>Y</available>
</book>
<book>
<id>12</id>
<title>DevOps for Legacy Systems</title>
<author>Tessa Rayner</author>
<publisher>Pipeline Media</publisher>
<published_date>2021-11-11</published_date>
<isbn>9781111111122</isbn>
<genre>DevOps</genre>
<pages>220</pages>
<price>37.99</price>
<available>Y</available>
</book>
<book>
<id>13</id>
<title>The Open Source iBox</title>
<author>Isaac Tremblay</author>
<publisher>FreedomCode Books</publisher>
<published_date>2023-03-14</published_date>
<isbn>9781111111123</isbn>
<genre>Open Source</genre>
<pages>340</pages>
<price>53.25</price>
<available>Y</available>
</book>
<book>
<id>15</id>
<title>API Design with RPG</title>
<author>Nathan Cross</author>
<publisher>EndPoint Books</publisher>
<published_date>2022-06-20</published_date>
<isbn>9781111111125</isbn>
<genre>API Development</genre>
<pages>390</pages>
<price>57.50</price>
<available>Y</available>
</book>
</books>
As you can see, the XML contains only the records returned by the SQL query.
If you want to revisit part 1 of SQL Data Types you can do it here: