?
Solved

DB2: xmlparse/cursor no result set

Posted on 2012-09-10
10
Medium Priority
?
1,007 Views
Last Modified: 2012-09-20
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

0
Comment
Question by:Paula DiTallo
  • 5
  • 4
9 Comments
 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 38383459
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
 

Author Comment

by:Paula DiTallo
ID: 38383844
Tomas,
There appears to be no change.
0
 
LVL 26

Assisted Solution

by:Tomas Helgi Johannsson
Tomas Helgi Johannsson earned 1000 total points
ID: 38384311
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:Paula DiTallo
ID: 38384873
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
 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 38385948
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
 
LVL 26

Assisted Solution

by:Tomas Helgi Johannsson
Tomas Helgi Johannsson earned 1000 total points
ID: 38386174
0
 

Accepted Solution

by:
Paula DiTallo earned 0 total points
ID: 38390635
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
 

Author Comment

by:Paula DiTallo
ID: 38390742
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
 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 38390743
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question