Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Unanswered: XML Extender and temporary tables

    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,

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by AKP33
    into a temporary table (using DECLARE GLOBAL TEMPORARY TABLE) within the stored procedure, the temporary table isn't populated.
    Did you declare the GTT with the ON COMMIT PRESERVE ROWS option?

    Quote Originally Posted by AKP33
    I am qualifying the temporary table with SESSION. in the stored procedure and in the DAD file, but nothing happens.
    Define "nothing happens".
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Apr 2009
    Posts
    2
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •