Formation of XML directly by querying an Oracle DB

See more:

XML

SQL

Oracle

database

I am querying an Oracle DB, to make XMLs out of each row using XMLELEMENT and XMLFOREST. For Single table it is working fine. But my requirement is now to fetch data from 3 different tables.

Description:

Table Main, SecondTable, ThirdTable. In which Main and SecondTable has unique TxnIDs while in ThirdTable its repeating.

Example:

Main Table                   SecondTable                  ThirdTable

TxnId  A   B   C             D  TxnId  E  F               G  H  I  TxnId
-----  -   -   -             -  -----  -  -               -  -  -  -----
 1001  3   4   6             5   1001  6  2               2  5  5   1001
 1002  5   6   2             4   1002  5  6               6  8  9   1001
 1003  5   4   2             6   1003  5  6               8  9  8   1001

I want to represent these data in XML. So far I am able to get first row in form of XML using,

Select XMLELELEMENT(
          ("Event",XMLFOREST(m.A as 'A',m.B as 'B',m.C as 'C')||
                       XMLELEMENT("SecondTable",XMLFOREST(s.E as 'E',s.F as 'F')
                     )||
              XMLELEMENT("ThirdTable",XMLFOREST(t.G as 'G',t.H as 'H',t.I as 'I')
                      )
               )
 from Main m
        JOIN SecondTable s ON m.TxnId=s.TxnId
        JOIN ThirdTable t ON s.TxnId=t.TxnId

This Query will give me output like--

<Event>
   <Main>
      <A>3</A>
      <B>4</B>
      <C>6</C>
   </Main>
  <SecondTable>
      <E>6</E>
      <F>2</F>
  </SecondTable>
       <ThirdTable>
             <G>2</G>
             <H>5</H>
             <I>5</I>
       </ThirdTable>
 </Event>

Question: I want Repeating TxnIds of ThirdTable to form 3 blocks in a single XML.
It should be like this--

<Event>
   <Main>
      <A>3</A>
      <B>4</B>
      <C>6</C>
   </Main>
  <SecondTable>
      <E>6</E>
      <F>2</F>
  </SecondTable>
       <ThirdTable>
             <G>2</G>
             <H>5</H>
             <I>5</I>
       </ThirdTable>
        <ThirdTable>
        <G>6</G>
        <H>8</H>
        <I>9</I>
    </ThirdTable>

    <ThirdTable>
        <G>8</G>
        <H>9</H>
        <I>8</I>
    </ThirdTable>
 </Event>

I hope I am clear. Please help me. Its really important for me. Thanks in Advance.