Hi ,

I am new to DB2,i want to try the below sql stored procedure in DB2.

In sql,i am passing xml as text and inserting the needed element node values to temporary table like below.

For ex... xml string like

<Record>
<Employee>
<EMPNUM>
12345
</EMPNUM>
<EMPNAME>
ABC
</EMPNAME>
</Employee>
<Employee>
<EMPNUM>
67891
</EMPNUM>
<EMPNAME>
PQR
</EMPNAME>
</Employee>
</Record>

create procedure Getxmldata
(
//xml string
@xmlText ntext
)
AS

BEGIN

DECLARE @Doc int

//tempaoray table
DECLARE @temp table(
emp_num int,
name varchar(30)
);

EXEC sp_xml_preparedocument @Doc OUTPUT, @xmlText


INSERT INTO @temp

SELECT EMPNUM,EMPNAME FROM OPENXML

(@Doc, 'Record/Employee',2) WITH

(EMPNUM int,EMPNAME EMPNAME varchar(30))

SELECT * FROM @temp;

END

Expexted result should display all the employee records. for ex
-------------------
emp_num emp_name
12345 ABC
67891 PQR


Thanks in advance for your help
sj