Guru: Change XML Elements in SQL

February 18, 2019 Paul Tuohy

Over the last few years, it has become more common to store XML or JSON in a column in a table. Whereas SQL provides all the necessary functions to construct/deconstruct XML or JSON from/to relational data, it does not provide an easy means to change the contents of an element. In this article, I am going to demonstrate a technique for changing the contents of an XML element using an SQL stored procedure.

Just to provide some background, I was recently working on a project where DB2 XML Extender functionality was being replaced with the standard XML functions. The project was undertaken because DB2 XML Extender is no longer supported on IBM i. DB2 XML Extender had an UPDATE function which allowed for simple updates to XML elements or attributes, but there is no equivalent simple standard SQL function. The IBM Redbook “Replacing DB2 XML Extender with integrated IBM DB2 for i XML capabilities” indicates that the XSLTRANSFORM function can be used to achieve the same result, but this would require an additional chargeable product (XML Toolkit for IBM System i5® – 5733-XT2 options 1 and 6) and the use of an XSLT template. All of this seemed a bit complex just to make simple changes to XML elements. My solution was to make use of an RPG program.

The Challenges

There were a number of difficulties:

• Directly manipulating the contents of an XML data type, in RPG, is not viable. The XML variable must first be cast to a character variable. Since the 32K maximum size of a VARCHAR may not be large enough, we need to use a CLOB.
• Although the preference would be to have a User-Defined Function (UDF), unfortunately SQL does not allow a CLOB to be returned from a UDF calling an RPG program. The RPG program must be wrapped as a stored procedure, which makes it just a little more difficult to use. If anyone has any insights into these issues, please contact me.

Using The Stored Procedure

To demonstrate the use of the stored procedure, we need to create two global variables

CREATE OR REPLACE VARIABLE TESTXML XML CCSID 1208;
CREATE OR REPLACE VARIABLE TESTCLOB CLOB(1049586); 

Then we assign a value to the XML variable:

set testxml = xmlparse(document 'YouMe');

To convert the contents of the <from> element, we start by casting the XML to a CLOB

set testclob = xmlserialize(testxml as CLOB(1049586));

Then we call the stored procedure to replace the contents of the <from> element. The parameters are:

• The CLOB containing the XML.
• The path to the element to be changed. Elements are delimited by a ‘/’. In this example we are changing the <from> element in the <note> element. There is no need to be concerned about whether or not you start and end the path list with a ‘/’, the RPG program will place or remove the leading/trailing ‘/’ as required. If you want to change the content of an attribute, as opposed to an element, precede the name of the attribute with an ‘@’.
• The new value for the element.

call replaceXMLElement(testclob, 'note/from/', 'Paul');

Finally, we cast the CLOB back to the XML variable:

call replaceXMLElement(testclob, 'note/from/', 'Paul');

If we look at the contents of the XML variable:

select testxml from sysibm.sysdummy1;

We see the updated XML:

YouPaul

The RPG Program

This is the program that does all the work. Although the program does not contain any SQL operations, it must be defined as an SQLRPGLE member (and be compiled using CRTSQLRPGI) since it contains the definition of an SQL data type. Please refer to the callouts in the code below:

1. The definition of an SQL CLOB will result in the definition of a data structure. In this case a data structure named DATACLOB containing the subfields DATACLOB_LEN and DATACLOB_DATA.
2. Ensure that the path string ends with a slash (/) and does not begin with a slash.
3. Use the subfields in the DATACLOB data structure to copy the contents of the CLOB to a varying field. Although SQL may have a 32K limit on the size of a VARCHAR variable, the limit in RPG is 16M.
4. Find the start position (in the CLOB) and the length of the data to be changed. This is the content of the element/attribute.
5. Change the value.
6. Copy the caring field back to the subfields in the CLOB data structure.
7. Loop through each element name in the requested path. Extract the element name and search for it.
8. Calculate the start position and length of the element or attribute.

    **free
    ctl-opt option(*srcstmt: *nodebugIO) dftactgrp(*no);

(1) dcl-s dataCLOB SQLTYPE(CLOB: 1049586) ;

    dcl-pr replaceXMLElement extPgm('REPELEM');
      CLOBIn           likeDS(dataCLOB) ;
      searchForElement varChar(3000) const;
      replaceWith      varChar(1000) const;
    end-Pr;
    dcl-pi replaceXMLElement;
      CLOBIn           likeDS(dataCLOB) ;
      searchForElement varChar(3000) const;
      replaceWith      varChar(1000) const;
    end-Pi;

    dcl-s varForCLOB varChar(1049586: 4) ;
    dcl-s searchFor  like(searchForElement);
    dcl-s elemStart  int(10);
    dcl-s elemLength int(10);

    // ensure path search ends with a / and does not start with /
    searchFor = %trim(searchForElement);
    if (%len(searchFor) > 0);
(2)    if (%subst(searchFor: %len(searchFor): 1) <> '/');
         searchFor += '/';
       endIf;
       if (%subst(searchFor: 1: 1) = '/');
         searchFor = %replace('': searchFor: 1: 1);
       endIf;
    endIf;

    // return if no valid CLOB or search element
    if (CLOBIn.dataCLOB_len <= 0 or %len(searchFor) <= 0);
      return;
    endIf;

    // copy CLOB contents to varying field
(3) varForCLOB = %subSt(CLOBIn.dataCLOB_data: 1: CLOBIn.dataCLOB_len);

    // find the start position and length of data to be changed
(4) findElement(searchFor: varForClob: elemStart: elemLength);

    // replace the content of the element
    if ((elemStart > 0 and elemStart <= %len(varForCLOB) ) and
        elemLength > 0);
(5)    varForCLOB = %replace(replaceWith: varForCLOB: elemStart: elemLength);
    endIf;

    // copy the data back to the CLOB
(6) CLOBIn.dataCLOB_data = varForCLOB;
    CLOBIn.dataCLOB_len = %len(varForCLOB);
    return;

    dcl-proc findElement;
    // Find the start position and length of the content of
    // the requested element or attribute.
    // Scan through the path tree an element at a time
      dcl-Pi *n;
        searchFor  like(searchForElement) const;
        varForCLOB varChar(1049586: 4) const;
        elemStart  int(10);
        elemLength int(10);
      end-Pi;

      dcl-s i           int(10) inz(1);
      dcl-s j           int(10);
      dcl-s elementIs   varChar(1000);
      dcl-s workStart   int(10) inz(1);
      dcl-s isAttribute ind;
      dcl-s fromChar    char(1) inz('>');
      dcl-s toChar      char(1) inz('<');

(7)   doU (i = 0 or i >= %len(searchFor));
         j = %scan('/': searchFor: i);
         if ((j-i) <= 0);
           return;
         endIf;

         elementIs = %subst(searchFor: i: j - i);
         isAttribute = (%subst(elementIs: 1: 1) = '@');
         if isAttribute;
           elementIs = %replace('': elementIs: 1: 1);
           fromChar = '"';
           toChar = '"';
         endIf;
         i = j + 1;

         workStart = %scan(elementIs: varForCLOB: workStart);
         if (workStart = 0);
           return;
         endIf;
      endDo;

      monitor;
(8)     elemStart = %scan(fromChar: varForCLOB: workStart) + 1;
        if (elemStart > 0);
          j = %scan(toChar: varForCLOB: elemStart + 1);
        endIf;
        elemLength = j - elemStart;
      on-error;
        elemStart = 0;
        elemLength = 0;
      endMon;
    end-Proc; 

The Stored Procedure

With our RPG program in place, all that remains is to create the stored procedure

CREATE OR REPLACE PROCEDURE REPLACEXMLELEMENT (
                INOUT DATACLOB CLOB(1049586) ,
                IN SEARCHFOR VARCHAR(3000) ,
                IN REPLACEWITH VARCHAR(1000) )
                LANGUAGE RPGLE
                SPECIFIC REPLACEXMLELEMENT
                DETERMINISTIC
                NO SQL
                CALLED ON NULL INPUT
                COMMIT ON RETURN YES
                EXTERNAL NAME 'PTARTICLES/REPELEM'
                PARAMETER STYLE GENERAL ;

A Handy Utility

I hope you will find a use for this utility. Although, I would be much happier if I could have this as a user defined function as opposed to a stored procedure. I am open to suggestions.

Paul Tuohy, IBM Champion and author of Re-engineering RPG Legacy Applications, is a prominent consultant and trainer for application modernization and development technologies on the IBM Midrange. He is currently CEO of ComCon, a consultancy firm in Dublin, Ireland, and partner at System i Developer. He hosts the RPG & DB2 Summit twice per year with partners Susan Gantner and Jon Paris.