Topic
  • 2 replies
  • Latest Post - ‏2011-06-29T06:43:16Z by Kirsi
Kirsi
Kirsi
11 Posts

Pinned topic XMLPARSE a CLOB param in SQL procedure

‏2011-06-28T07:34:36Z |
In previous thread I asked about catching errors from inserting with XMLTABLE. This insert will be done in native SQL stored procedure in DB2 z/os.

I will pass the XML to the procedure in CLOB parameter, because this procedure will replace the xml-shredding procedure from old xml extender and I am trying to have as minor impact on to other interfaces as possible.

The question is, can I parse this clob to some xml variable once and then use this one parsed xml variable in all of the insert into .. select * from

Maybe it is just an impossible idea and I just have to do XMLPARSE in every select, it was just a thought that I could maybe save something if I could do only one parse.

When I tried this I got some strange error saying:
SQL20060N The key transform table function used by the index extension of index "XML" of table "VARIABLE" in "M_XML" generated duplicate rows.

This is a sample code what I am trying to do (it will fail even with only one select-cursor in it):

CREATE PROCEDURE TEST.M160_AKEXMLR ( IN P_XML CLOB(1M) CCSID UNICODE)
VERSION "1"
PARAMETER CCSID UNICODE
QUALIFIER TEST
VALIDATE BIND
DYNAMIC RESULT SETS 2
READS SQL DATA
P1: BEGIN

DECLARE M_XML XML;
-- Declare cursor1
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT * FROM
XMLTABLE('$i/material/changes/change'
-- ** ?? ** -- instead of doing PASSING XMLPARSE(P_XML) AS "i":
PASSING M_XML AS "i"
COLUMNS
ORDERNO INTEGER PATH 'orderno',
CHANGETYPE CHAR(1) PATH 'changetype',
CHANGECODE CHARACTER(3) PATH 'changecode',
CHANGETIME CHARACTER(19) PATH 'changetime'
) AS x1 ;

DECLARE cursor2 CURSOR WITH RETURN FOR
SELECT *
FROM XMLTABLE('$i/material/changes/change'
-- ** ?? ** -- instead of doing PASSING XMLPARSE(P_XML) AS "i":
PASSING M_XML AS "i"
COLUMNS
ORDERNO INTEGER PATH 'orderno',
PRODUCTGROUP CHAR(3) PATH '/productgroups/productgroup'
) AS x2 ;

-- ** ?? **
SET M_XML = XMLPARSE(P_XML);

OPEN cursor1;
OPEN cursor2;
END P1
@

Br, Kirsi
Updated on 2011-06-29T06:43:16Z at 2011-06-29T06:43:16Z by Kirsi