DB2: xmlparse/cursor no result set

Techies--
I'm working in IBM Data Studio on DB2 9.7. I am trying to send an xml document with tagged employee values that I want to retrieve and return back. I am primarily a sql server/t-sql developer recently. It's been a while since I've worked on DB2, so I may NOT be choosing the correct way to do this. I don't get an error, just no data. If I were to issue the following statement against the data, I would get back 2 rows.

select emp.empno,emp.firstname,emp.workdept from db2inst1.employee emp where emp.empno in (select x.empno from db2inst1.employee x where x.empno in ('000020','000030'));


That is what I am expecting back. I'm trying to use the XMLData feature as the X table in the first statement.

Here's what I have:

Call:
CALL DB2INST1.EMP_MULTIPLE_XML(XMLPARSE(     DOCUMENT '<employee><empno>000020</empno><empno>000030</empno></employee>'));

Here's the stored proc:
CREATE PROCEDURE DB2INST1.EMP_MULTIPLE_XML (IN DOC XML) RESULT SETS 1 LANGUAGE SQL SPECIFIC EMP_MULTIPLE_XML  

BEGIN  
   DECLARE CSR1 CURSOR FOR    
     select emp.empno,          
                emp.firstnme,          
                emp.lastname,          
                emp.workdept    
                 from DB2INST1.EMPLOYEE emp   
                  where emp.empno in   (select x."EMPNO"   
                      from XMLTABLE('$d/employee' PASSING DOC AS "d" COLUMNS "EMPNO"      CHAR(6) PATH 'empno') AS X);    

OPEN CSR1;    
   RETURN;    
CLOSE CSR1;  
END  

Open in new window

Paula DiTalloIntegration developerAsked:
Who is Participating?
 
Paula DiTalloConnect With a Mentor Integration developerAuthor Commented:
For those following behind me, this works:

 
  CREATE OR REPLACE PROCEDURE test.INPUT_MULTIPLE_XML (IN DOC XML)    
  DYNAMIC RESULT SETS 1     
  LANGUAGE SQL SPECIFIC INPUT_MULTIPLE_XML     
  BEGIN        
  DECLARE CSR1 CURSOR WITH RETURN FOR             
     select t.type,                          
               t.tabschema,                          
               t.tabname              
       from syscat.tables t,                 
       XMLTABLE('$d/tables/tabname' PASSING DOC AS "d"            
       COLUMNS "TABNAME" VARCHAR(128) PATH '.'    ) AS X               
          WHERE X.tabname = t.tabname ;        

  OPEN CSR1;     
  END  

Open in new window

0
 
Tomas Helgi JohannssonCommented:
Hi!

Try removing the line no 15: close csr1.
You are closing the cursor before the application has a chance to read the data.
And usually a cursor passed to an application is left for it to do close either explicitly or implicitly.

Regards,
   Tomas Helgi
0
 
Paula DiTalloIntegration developerAuthor Commented:
Tomas,
There appears to be no change.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Tomas Helgi JohannssonConnect With a Mentor Commented:
Hmmm try this

CREATE PROCEDURE DB2INST1.EMP_MULTIPLE_XML (IN DOC XML)
LANGUAGE SQL
READS SQL DATA
DYNAMIC RESULT SETS 1 
LANGUAGE SQL SPECIFIC EMP_MULTIPLE_XML  

BEGIN  
   DECLARE CSR1 CURSOR WITH RETURN FOR    
     select emp.empno,          
                emp.firstnme,          
                emp.lastname,          
                emp.workdept    
                 from DB2INST1.EMPLOYEE emp   
                  where emp.empno in   (select x."EMPNO"   
                      from XMLTABLE('$d/employee' PASSING DOC AS "d" COLUMNS "EMPNO"      CHAR(6) PATH 'empno') AS X);    

OPEN CSR1;    
END 

Open in new window


Regards,
   Tomas Helgi
0
 
Paula DiTalloIntegration developerAuthor Commented:
I've made some progress--but still no data. I should have 2 rows returned. One for empno 000020 and one for 000030.

Here's the revised sproc:

  CREATE PROCEDURE DB2INST1.EMP_MULTIPLE_XML (IN DOC XML)    
        DYNAMIC RESULT SETS 1    
        READS SQL DATA   
        LANGUAGE SQL SPECIFIC EMP_MULTIPLE_XML 
  
        BEGIN

       DECLARE CSR1 CURSOR WITH RETURN FOR     
        SELECT emp.EMPNO,            
                    emp.FIRSTNME,            
                    emp.LASTNAME,            
                   emp.WORKDEPT      
          FROM DB2INST1.EMPLOYEE emp     
            WHERE emp.EMPNO IN       
             (SELECT X.EMPNO FROM  XMLTABLE('$d/EMPLOYEE/EMPNO' PASSING DOC AS "d" COLUMNS EMPNO CHAR(6) PATH 'EMPNO') AS X);   

OPEN CSR1;  
END 

Open in new window

0
 
Tomas Helgi JohannssonCommented:
Hi!
Have you tried omitting line 14 and 15 (the where clause ) just to see if you get any data back ?
Or change these lines to
WHERE emp.EMPNO IN ('000020',''000030')

If the above works then you need to look more closely on how you join to the XMLTABLE or if there are any XML data.

Regards,
    Tomas Helgi
0
 
Paula DiTalloIntegration developerAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for ditallop's comment #a38390635
Assisted answer: 150 points for TomasHelgi's comment #a38384311
Assisted answer: 100 points for TomasHelgi's comment #a38386174

for the following reason:

i finally got this to work replacing empno with '.' under path.
0
 
Tomas Helgi JohannssonCommented:
Hi!

To close question your question you need to accept either one comment as a solution
or multiple comments as shown here.

http://www.experts-exchange.com/help/viewHelpPage.jsp?helpPageID=24

Regards,
    Tomas Helgi
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.