Click here to Skip to main content
15,923,083 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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,
SQL
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--
XML
<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--
XML
<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.
Posted
Updated 14-Sep-14 0:02am
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900