Hi there,

I have a stored procedure on DB2 version 8.2 that uses XML Extender procedures to decompose an XML document into a table. When the destination table is an existing table, the decomposition works fine. However, when I try to shred the document into a temporary table (using DECLARE GLOBAL TEMPORARY TABLE) within the stored procedure, the temporary table isn't populated.

I am qualifying the temporary table with SESSION. in the stored procedure and in the DAD file, but nothing happens.

The XML Administration and Programming Guide states that temporary tables can be used. Is there anything special that needs to be done, or am I doing something wrong?

Thanks,

Bruce,

Did you declare the GTT with the ON COMMIT PRESERVE ROWS option?

Define "nothing happens".

Yes, I have preserve rows on. This is the procedure I'm trying to get working for proof-of-concept. All I'm tryiong to do is populate the table and do a select on it.

************************************************** ********
CREATE PROCEDURE SelectTest
(
v_OrderList CLOB(32784)
)

LANGUAGE SQL

BEGIN

DECLARE DadDoc clob(100K);
DECLARE retCode integer;
DECLARE retMsg varchar(1024);
DECLARE Doc int;


DECLARE GLOBAL TEMPORARY TABLE OPENXML
(WkstnOidSh smallint not null,
WkstnOidLng int not null,
PfWkstnOidSh smallint not null,
PfWkstnOidLng int not null,
Type varchar(15)) ON COMMIT PRESERVE ROWS WITH REPLACE;

--| Get DAD information from XMLDAD
SELECT DadTxt
INTO DadDoc
FROM XMLDAD
WHERE DadId = 3;

call DB2XML.DXXSHREDXML100MB(DadDoc, v_OrderList, retCode, retMsg);

COMMIT;

BEGIN
DECLARE temp_cursor CURSOR WITH HOLD WITH RETURN TO CLIENT
FOR select * from SESSION.OPENXML;

OPEN temp_cursor;
END;

END
@
************************************************** *****


When I say "nothing works", the procedure executes but the table doesn't get populated (apparently), since the select returns 0 rows.


Like I mentioned before, if I create the OPENXML table outside of the stored proedure and remove the SESSION. from the tablename in the stored procedure and DAD file, it the table gets populated and the select returns the data from the XML file. So I think I've got datbase set up right and the XML and DAD files correct, it's just the difference between using an existing table and a temporary table that I'm not getting right.