Search code examples
oracle-databaseplsqlxmltype

XMLType Concat multiple instances of the same xml tag


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.


Solution

  • 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);