• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 976
  • 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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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