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.