Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Howto split XML to relational table

Stanislav StudenýAug 20 2008 — edited Aug 26 2008

Hi,

i have this example XML ..

  <SB>
    <Sq BN="805101XXX">
      <ID1>Alex</ID1>
      <ID2>Dolby</ID2>
      <ST>
        <STU X="14330" Y="2001-07-10">
          <E>
            <E1 FT="2001-08-10">xx1<E1>
            <E2 FT="2001-08-11">xx2<E2>
          <E>
        </STU>
        <STU X="14332" Y="2001-07-11">
          <E>
            <E1 FT="2001-08-12">yy1<E1>
            <E2 FT="2001-08-13">yy2<E2>
          <E>
        </STU>
      </ST>
    </Sq>
    <Sq BN="805101YYY">
      <ID1>Alex</ID1>
      <ID2></ID2>
      <ST>
        <STU X="14334" Y="2001-07-10">
          <E>
            <E1 FT="2001-08-14">xx3<E1>
            <E2 FT="2001-08-15">xx4<E2>
          <E>
        </STU>
        <STU X="14332" Y="2001-07-10">
          <E>
            <E1 FT="2001-08-16">yy3<E1>
            <E2 FT="2001-08-17">yy4<E2>
          <E>
        </STU>
      </ST>
    </Sq>
  </SB>

and I need to get this table :

   BN     | ID1  |  ID2  |   X   |     Y      |     FT     | E1  | E2 
-----------------------------------------------------------------------
805101XXX | Alex | Dolby | 14330 | 2001-07-10 | 2001-08-10 | xx1 | xx2
805101XXX | Alex | Dolby | 14330 | 2001-07-10 | 2001-08-11 | xx1 | xx2
805101XXX | Alex | Dolby | 14332 | 2001-07-11 | 2001-08-12 | yy1 | yy2
805101XXX | Alex | Dolby | 14332 | 2001-07-11 | 2001-08-13 | yy1 | yy2

805101YYY | Alex |       | 14334 | 2001-07-10 | 2001-08-14 | xx3 | xx4
805101YYY | Alex |       | 14334 | 2001-07-10 | 2001-08-15 | xx3 | xx4
805101YYY | Alex |       | 14332 | 2001-07-10 | 2001-08-16 | yy3 | yy4
805101YYY | Alex |       | 14332 | 2001-07-10 | 2001-08-17 | yy3 | yy4

how can I do that?

Thanks.

Best Regards,
Stanislav

Message was edited by:
stanislav_studeny

Locked Post

New comments cannot be posted to this locked post.

Locked on Sep 23 2008

Added on Aug 20 2008

#performance-availability, #xml, #xml-db

3 comments

1,672 views