I need to generate one xml document which contains in my example two sql query.

Below example return:

SELECT
    XMLElement("EMPLOYEES",
    XMLAGG(XMLELEMENT(
        "USER", XMLFOREST(empno AS "ID", ename AS "NAME")
    )))
FROM
    emp where rownum < 3



<EMPLOYEES>
    <USER>
        <ID>7839</ID>
        <NAME>KING</NAME>
    </USER>
    <USER>
        <ID>7698</ID>
        <NAME>BLAKE</NAME>
    </USER>
</EMPLOYEES>

is it possible in one query before EMPLOYEES tag get DEPARTMENT tag below query

SELECT
    XMLElement("DEPARTMENT",
    XMLAGG(XMLELEMENT(
        "DEPT", XMLFOREST(deptno AS "ID", dname AS "NAME")
    )))
FROM
    dept where rownum < 3


<DEPARTMENT>
    <DEPT>
        <ID>10</ID>
        <NAME>ACCOUNTING</NAME>
    </DEPT>
    <DEPT>
        <ID>20</ID>
        <NAME>RESEARCH</NAME>
    </DEPT>
</DEPARTMENT>

Finally need this:

Or some other way to fix this issue?

CodePudding user response：

You could use XMLCONCAT:

SELECT
  XMLCONCAT(
    (
      SELECT
        XMLELEMENT("DEPARTMENT",
          XMLAGG(
            XMLELEMENT("DEPT",
              XMLFOREST(deptno AS "ID", dname AS "NAME")
            )
          )
        )
      FROM
        dept
      WHERE
        rownum < 3
    ),
    (
      SELECT
        XMLELEMENT("EMPLOYEES",
          XMLAGG(
            XMLELEMENT("USER",
              XMLFOREST(empno AS "ID", ename AS "NAME")
            )
          )
        )
      FROM
        emp
      WHERE
        rownum < 3
    )
  )
FROM
  dual

<DEPARTMENT>
  <DEPT>
    <ID>10</ID>
    <NAME>ACCOUNTING</NAME>
  </DEPT>
  <DEPT>
    <ID>20</ID>
    <NAME>RESEARCH</NAME>
  </DEPT>
</DEPARTMENT>
<EMPLOYEES>
  <USER>
    <ID>7839</ID>
    <NAME>KING</NAME>
  </USER>
  <USER>
    <ID>7698</ID>
    <NAME>BLAKE</NAME>
  </USER>
</EMPLOYEES>

to get an XML fragment, or perhaps more usefully turn that into a root element (called whatever you like; I've arbitrarily picked DATA) to produce an XML document:

SELECT
  XMLELEMENT("DATA",
    XMLCONCAT(
      (
        SELECT
          XMLELEMENT("DEPARTMENT",
            XMLAGG(
              XMLELEMENT("DEPT",
                XMLFOREST(deptno AS "ID", dname AS "NAME")
              )
            )
          )
        FROM
          dept
        WHERE
          rownum < 3
      ),
      (
        SELECT
          XMLELEMENT("EMPLOYEES",
            XMLAGG(
              XMLELEMENT("USER",
                XMLFOREST(empno AS "ID", ename AS "NAME")
              )
            )
          )
        FROM
          emp
        WHERE
          rownum < 3
      )
    )
  )
FROM
  dual

<DATA>
  <DEPARTMENT>
    <DEPT>
      <ID>10</ID>
      <NAME>ACCOUNTING</NAME>
    </DEPT>
    <DEPT>
      <ID>20</ID>
      <NAME>RESEARCH</NAME>
    </DEPT>
  </DEPARTMENT>
  <EMPLOYEES>
    <USER>
      <ID>7839</ID>
      <NAME>KING</NAME>
    </USER>
    <USER>
      <ID>7698</ID>
      <NAME>BLAKE</NAME>
    </USER>
  </EMPLOYEES>
</DATA>

db<>fiddle