Using the XMLTABLE function with MERGE statements in DB2 for z/OS
August 10, 2012
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:
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.