Sokrates on Oracle

Mount an xml-file as “External Table”

Posted by Matthias Rogel on 3. January 2013

“Mounting” a csv-file as external-table is very handy.

Here a quick note to show, that we can “mount” an xml-file as well ( and query it via SQL )

 

sokrates@11.2 > create directory t as '/tmp';
Directory created.
sokrates@11.2 > !wget -O /tmp/t.xml www.w3schools.com/xml/simple.xml
--17:31:25-- http://www.w3schools.com/xml/simple.xml
Resolving www.w3schools.com... 66.29.212.73
Connecting to www.w3schools.com|66.29.212.73|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1135 (1.1K) [text/xml]
Saving to: `/tmp/t.xml'
100%[============================================================================================================================>] 1,135 --.-K/s in 0s
17:31:25 (216 MB/s) - `/tmp/t.xml' saved [1135/1135]
sokrates@11.2 > !cat /tmp/t.xml
<?xml version="1.0" encoding="ISO-8859-1"?>
<!-- Edited by XMLSpy? -->
<breakfast_menu>
 <food>
 <name>Belgian Waffles</name>
 <price>$5.95</price>
 <description>two of our famous Belgian Waffles with plenty of real maple syrup</description>
 <calories>650</calories>
 </food>
 <food>
 <name>Strawberry Belgian Waffles</name>
 <price>$7.95</price>
 <description>light Belgian waffles covered with strawberries and whipped cream</description>
 <calories>900</calories>
 </food>
 <food>
 <name>Berry-Berry Belgian Waffles</name>
 <price>$8.95</price>
 <description>light Belgian waffles covered with an assortment of fresh berries and whipped cream</description>
 <calories>900</calories>
 </food>
 <food>
 <name>French Toast</name>
 <price>$4.50</price>
 <description>thick slices made from our homemade sourdough bread</description>
 <calories>600</calories>
 </food>
 <food>
 <name>Homestyle Breakfast</name>
 <price>$6.95</price>
 <description>two eggs, bacon or sausage, toast, and our ever-popular hash browns</description>
 <calories>950</calories>
 </food>
</breakfast_menu>
sokrates@11.2 > select
 2 x.*
 3 from XMLTable(
 4 '/breakfast_menu/food'
 5 passing
 6 xmltype(
 7 bfilename('T','t.xml'), nls_charset_id('WE8ISO8859P1')
 8 )
 9 columns
 10 name varchar2(50) path 'name',
 11 price varchar2(20) path 'price',
 12 description varchar2(200) path 'description',
 13 calories number path 'calories'
 14 ) x;
NAME PRICE DESCRIPTION CALORIES
------------------------------ ------- ------------------------------------------------------------------------------------------ ------------
Belgian Waffles $5.95 two of our famous Belgian Waffles with plenty of real maple syrup 650
Strawberry Belgian Waffles $7.95 light Belgian waffles covered with strawberries and whipped cream 900
Berry-Berry Belgian Waffles $8.95 light Belgian waffles covered with an assortment of fresh berries and whipped cream 900
French Toast $4.50 thick slices made from our homemade sourdough bread 600
Homestyle Breakfast $6.95 two eggs, bacon or sausage, toast, and our ever-popular hash 
About these ads

2 Responses to “Mount an xml-file as “External Table””

  1. Eddie Awad said

    Nice use of XMLTable and bfilename function. Alternatively, you can use httpuritype to fetch the XML directly from the web:

    SELECT x.*
    FROM XMLTABLE (
    ‘/breakfast_menu/food’
    PASSING httpuritype (‘http://www.w3schools.com/xml/simple.xml’).getxml ()
    COLUMNS name VARCHAR2 (50) PATH ‘name’,
    price VARCHAR2 (20) PATH ‘price’,
    description VARCHAR2 (200) PATH ‘description’,
    calories NUMBER PATH ‘calories’) x;

    Cheers!

  2. Eddie,

    thanks for that !
    I didn’t use httpuritype so far ( until today )
    ,-)

    Cheers,
    Matthias

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: