My starting point is this issue : Insert xmltype into xmltype in specified place [PL/SQL]
Now, I have this simple example:
DECLARE
l_head_xml XMLTYPE
:= xmltype.CREATEXML(
'<ord>
<head>
<ord_code>123</ord_code>
<ord_date>01-01-2015</ord_date>
</head>
</ord>');
l_pos_xml XMLTYPE
:= xmltype.CREATEXML(
'<pos>
<pos_code>456</pos_code>
<pos_desc>description</pos_desc>
</pos>');
l_complete_xml XMLTYPE;
BEGIN
FOR i IN 1 .. 2
LOOP
SELECT XMLELEMENT("MAIN_TAG",
XMLAGG(XMLCONCAT(l_head_xml, l_pos_xml)))
INTO l_complete_xml
FROM dual;
END LOOP;
dbms_output.put_line(l_complete_xml.getstringval());
END;
In my case, I have a loop who gets multiple instances of these two tags: l_head_xml
and l_pos_xml
. The final result my be something like this:
<MAIN_TAG>
<Item>
<ord>
<head>
<ord_code>123</ord_code>
<ord_date>01-01-2015</ord_date>
</head>
</ord>
<pos>
<pos_code>456</pos_code>
<pos_desc>description</pos_desc>
</pos>
</Item>
<Item>
<ord>
<head>
<ord_code>123</ord_code>
<ord_date>01-01-2015</ord_date>
</head>
</ord>
<pos>
<pos_code>456</pos_code>
<pos_desc>description</pos_desc>
</pos>
</Item>
</MAIN_TAG>
How can I do this, using XMLType funtions (not deprecated - I'm using Oracle 11g) and without using temporary variables to concatenate each of the XML block on each instance.
Just use subquery to multiply the records before aggregation:
SELECT
XMLELEMENT ("MAIN_TAG", XMLAGG (XMLCONCAT (l_head_xml, l_pos_xml)))
INTO l_complete_xml
FROM
(SELECT DUMMY FROM DUAL CONNECT BY LEVEL <= 2);