Sokrates on Oracle

Archive for January, 2013

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 
Advertisements

Posted in Allgemein | Tagged: | 2 Comments »