Home » Developer & Programmer » JDeveloper, Java & XML » Concat XML data in a loop
Concat XML data in a loop [message #669455] Mon, 23 April 2018 15:37 Go to next message
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 #669456 is a reply to message #669455] Mon, 23 April 2018 16:26 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member

Welcome to this forum

Please READ and FOLLOW the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Re: Concat XML data in a loop [message #669457 is a reply to message #669455] Mon, 23 April 2018 21:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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.
Re: Concat XML data in a loop [message #669475 is a reply to message #669457] Tue, 24 April 2018 11:30 Go to previous messageGo to next message
SoujanyaSM
Messages: 3
Registered: April 2018
Junior Member
Thank you so much for the reply!

My output data is little complex to use BMS_XMLGEN.GETXML. I another way to do this.

This one worked for me -

Call the function in a loop. Concat the output into a XMLTYPE(But at the end of the loop data in XMLTYPE is not complete XML). Add XMLELEMENT to the concatinated output after the loop.


CREATE OR REPLACE PROCEDURE test_proc
(p_xml OUT XMLTYPE)
AS

xml_output XMLTYPE;
xml_output_final XMLTYPE;
BEGIN

FOR i IN (SELECT empno FROM emp WHERE deptno = 10) LOOP

xml_output := test_func(i.empno);
SELECT XMLCONCAT(xml_output_final,xml_output )
INTO xml_output_final
FROM DUAL;
END LOOP;
--Convert concatinated output to XML format
SELECT XMLELEMENT("EmployeeData", xml_output_final)
INTO p_xml FROM DUAL;


END test_proc;
/









Re: Concat XML data in a loop [message #669486 is a reply to message #669475] Wed, 25 April 2018 00:35 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Thanks for posting your solution.
Previous Topic: Using IDE Database Connections in Java code
Next Topic: XML Bursting Emial Body
Goto Forum:
  


Current Time: Thu Mar 28 12:52:32 CDT 2024