• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1014
  • Last Modified:

xmltable does not work on db2 iSeries v6.1

Techies--

I am getting the message: [SQL0204] XML in *LIBL type *SQLUDT not found when I try to deploy a stored proc that I know works on DB2 v9.7 ; Linux. What is the equivalent of the stored proc below in i?

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

P1: BEGIN

 	DECLARE CSR1 CURSOR WITH RETURN FOR
 	  SELECT emp.EMPID,
 	         emp.FIRSTNAME,
 	         emp.LASTNAME,
 	         emp.DIVISION,
 	         emp.DISTRICT,
 	         emp.LOCATION,
 	         emp.OPERATIONALAREA,
 	         emp.TERMDATE,
 	         emp.REHIREDATE,
 	         emp.HIREDATE,
 	         emp.ADDRESSLINE1,
 	         emp.ADDRESSLINE2,
 	         emp.CITY,
 	         emp.STATE,
 	         emp.ZIPCODE,
 	         emp.TELEPHONE1,
 	         emp.POSITIONCODE,
 	         emp.POSITIONTITLE,
 	         emp.HIRECODE
 	      FROM HCMDEV.EMPLOYEE emp
 	      WHERE EMP.EMPID IN
 	        (SELECT X.EMPID
 	           FROM XMLTABLE('$d/EMPLOYEE/EMPID' PASSING DOC AS "d" COLUMNS EMPID CHAR(9) PATH '.') AS X);
 	              
 	         
 	OPEN CSR1;
END P1

Open in new window

0
Paula DiTallo
Asked:
Paula DiTallo
  • 2
  • 2
2 Solutions
 
mustaccioCommented:
That's not going to work -- XML support is very different between DB2 LUW and DB2 for i. Check out the XML Toolkit coverage in the DB2 i information center http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/rzamj/rzamjtoolkitcomponents.htm
0
 
tliottaCommented:
[SQL0204] XML in *LIBL type *SQLUDT not found
That error comes from this:
(IN DOC XML)

Open in new window

There are two general choices. First, upgrade to i 7.1 where XML is a native data type. Second, use CREATE TYPE to create a XML data type that you can reference; this will create the *SQLUDT that will be found and used.

Tom
0
 
Paula DiTalloIntegration developerAuthor Commented:
...and the other solution, use a combo of dynamic sql and clob to import a very large string.
0
 
tliottaCommented:
Yes, a different process can be used. I apologize for thinking you wanted to make your existing SP code to work unchanged. Glad you worked out a useful solution.

Tom
0
 
Paula DiTalloIntegration developerAuthor Commented:
this is the one that can be implemented without creating special types and without upgrading.
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.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now