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.
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:
XMLPARSE( DOCUMENT '<employee><empno>000020</
Here's the stored proc:
CREATE PROCEDURE DB2INST1.EMP_MULTIPLE_XML (IN DOC XML) RESULT SETS 1 LANGUAGE SQL SPECIFIC EMP_MULTIPLE_XML
DECLARE CSR1 CURSOR FOR
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);