Re: How to transform XML-data to a relational view?

From: Anurag Varma <avoracle_at_gmail.com>
Date: 26 Jan 2006 11:19:49 -0800
Message-ID: <1138303189.105901.268490@g43g2000cwa.googlegroups.com>

Here is an example:

ORA92> select * from t1;

1
<root>

        <level id="ABCD" type="01">
                <item no="123">


                        <url>http://url1.com</url>

                        <desc>This is link1</desc>

                </item>
                <item no="234">


                        <url>http://url2.com</url>

                        <desc>This is link2</desc>

                </item>
        </level>
        <level id="BCDE" type="01">
                <item no="123">


                        <url>http://url3.com</url>

                        <desc>This is link3</desc>

                </item>
        </level>

</root>

ORA92> select a, extract(value(d1),'/level/@id').getStringVal() id,

  2            extract(value(d1),'/level/@type').getStringVal() mytype,
  3            extract(value(d2),'/item/@no').getStringVal() mynum,
  4            extract(value(d2),'/item/url/text()').getStringVal()
url,
  5            extract(value(d2),'/item/desc/text()').getStringVal()

descr,
  6 rownum
  7 from t1 x, TABLE( xmlsequence (extract (x.b, '/root/level' ) ) ) d1,
  8 TABLE( xmlsequence (extract (value(d1), '/level/item' ) ) ) d2
  9 /

         A ID     MYTYPE MYNUM    URL                DESCR
      ROWNUM
---------- ------ ------ -------- ------------------
-------------------- ----------
         1 ABCD   01     123      http://url1.com    This is link1
           1
         1 ABCD   01     234      http://url2.com    This is link2
           2
         1 BCDE   01     123      http://url3.com    This is link3
           3

ORA92> desc t1
Name
Null? Type

-------------------------------------------- A NUMBER B XMLTYPE

Anurag Received on Thu Jan 26 2006 - 13:19:49 CST