Native XML Database

This is an add-on to my previous post on Using the XMLTABLE function in UPDATE and MERGE statements.

I had tested all the examples in that previous post in DB2 for Linux, UNIX, and Windows but overlooked that DB2 for z/OS currently has a restriction for the MERGE statement.

The MERGE statement in DB2 for z/OS expects a VALUES clause to provide the data that is to be merged, not an arbitrary sub-select. For details, see:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z9.doc.sqlref/src/tpc/db2z_sql_merge.htm

As a result, the MERGE example at the end of my previous post needs to be adjusted for DB2 z/OS. For example, you can use an SQL procedure to loop over the rows produced by the XMLTABLE query and feed these rows into the MERGE statement:

CREATE PROCEDURE XMLMERGE(IN P_CHANGE XML)
LANGUAGE SQL
BEGIN
  DECLARE ID INT;
  DECLARE X INTEGER;
  DECLARE Y VARCHAR(20);
  DECLARE SQLCODE INT;

  DECLARE C1 CURSOR FOR
    SELECT ID, X, Y
    FROM XMLTABLE('$DOC/root/mydata' PASSING P_CHANGE AS "DOC"
         COLUMNS
           id INTEGER     PATH '@id',
           x  INTEGER     PATH 'elem1',
           y  VARCHAR(20) PATH 'elem2') T;

  OPEN C1;
    LOOP1: LOOP
      FETCH C1 INTO ID, X, Y;
      IF SQLCODE <> 0 THEN LEAVE LOOP1; END IF;

      MERGE INTO RELTABLE   R
      USING (VALUES(ID, X, Y)) AS N(ID, X, Y)
      ON (R.ID = N.ID)
      WHEN MATCHED THEN UPDATE SET R.X = X, R.Y = Y
      WHEN NOT MATCHED THEN INSERT VALUES(ID, X, Y);
    END LOOP LOOP1;
  CLOSE C1;
END

Thanks to my colleague Guogen Zhang for providing this sample solution.