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 reperesent 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. I hope I am clear. Please help me. Its really important for me. Thanks in Advance.
No comments:
Post a Comment