| Concat XML data in a loop [message #669455] |
Mon, 23 April 2018 15:37  |
 |
SoujanyaSM
Messages: 3
Registered: April 2018
|
Junior Member |
|
|
I ma new to XML, please help me!
I have to generate XML output from a PLSQL procedure. In my procedure I have a cursor which would call a function and get data something like below in every iteration.
<Employee>
<Name>Jonny</Name>
<Age>32</Age>
<Salary>$5000</Salary>
</Employee>
Now I have say a thousand employees. So I call the function 1000 times and I get XML response every time. I need to concatenate the data for 1000 employees and give as a response.
I tried using XML CONCAT function, but the function is not allowing me to use inside a loop.
Please help me here! thanks in advance!
-Soujanya
|
|
|
|
|
|
| Re: Concat XML data in a loop [message #669457 is a reply to message #669455] |
Mon, 23 April 2018 21:04  |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
In your procedure, you could convert the xml data received from the function into clobs, concatenate the clobs, then convert the concatenated clob to xml and output the xml. I have provided a demonstration below. Note that this may not be the most efficient way to do things. I have just provided what you have asked for as I do not know what your total problem is. It may be that you could use something like DBMS_XMLGEN.GETXML from SQL to do what you want, instead of using a procedure that calls a function.
-- function for simulation that returns xmltype for one employee:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE FUNCTION test_func
2 (p_empno IN emp.empno%TYPE)
3 RETURN XMLTYPE
4 AS
5 v_employee XMLTYPE;
6 BEGIN
7 SELECT XMLTYPE ('<Employee><Name>'
8 || ename
9 || '</Name><Age>'
10 || ROUND(MONTHS_BETWEEN(SYSDATE,hiredate)/12)
11 || '</Age><Salary>'
12 || sal
13 || '</Salary></Employee>')
14 INTO v_employee
15 FROM emp
16 WHERE empno = p_empno;
17 RETURN v_employee;
18 END test_func;
19 /
Function created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> -- demonstration of function:
SCOTT@orcl_12.1.0.2.0> SELECT test_func(7839) FROM DUAL
2 /
TEST_FUNC(7839)
--------------------------------------------------------------------------------
<Employee>
<Name>KING</Name>
<Age>36</Age>
<Salary>5000</Salary>
</Employee>
1 row selected.
-- procedure that calls function in loop,
-- converts xml returned from function to clobs,
-- concatenates the clobs,
-- then converts the concatenated clob to xml and outputs it:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE test_proc
2 (p_xml OUT XMLTYPE)
3 AS
4 v_clob CLOB;
5 BEGIN
6 v_clob := '<Table>';
7 FOR i IN (SELECT empno FROM emp WHERE deptno = 10) LOOP
8 v_clob := v_clob || test_func(i.empno).GETCLOBVAL();
9 END LOOP;
10 v_clob := v_clob || '</Table>';
11 p_xml := XMLTYPE(v_clob);
12 END test_proc;
13 /
Procedure created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
-- demonstration of procedure using a table to insert the data into and select from:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE test_tab (test_col XMLTYPE)
2 /
Table created.
SCOTT@orcl_12.1.0.2.0> DECLARE
2 v_xml XMLTYPE;
3 BEGIN
4 test_proc(v_xml);
5 INSERT INTO test_tab (test_col) VALUES (v_xml);
6 END;
7 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM test_tab
2 /
TEST_COL
--------------------------------------------------------------------------------
<Table>
<Employee>
<Name>CLARK</Name>
<Age>37</Age>
<Salary>2450</Salary>
</Employee>
<Employee>
<Name>KING</Name>
<Age>36</Age>
<Salary>5000</Salary>
</Employee>
<Employee>
<Name>MILLER</Name>
<Age>36</Age>
<Salary>1300</Salary>
</Employee>
</Table>
1 row selected.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
