select from xml

My wife is so happy that an African president is ruling the world for the next four years !

Ok, here is a simple exercice :

How do you select from an HTML file ?

Take an example

The table of departments

10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON


create table T of XMLTYPE;

insert into T values(
  XMLTYPE(
'<?xml version="1.0" encoding="ISO-8859-1"?>
<html>
  <head>
    <title>Employee table</title>
  </head>
  <body>
    <p>The table of departments</p>
    <table border="1">
      <tr>
        <td>10</td>
        <td>ACCOUNTING</td>
        <td>NEW YORK</td>
      </tr>
      <tr>
        <td>20</td>
        <td>RESEARCH</td>
        <td>DALLAS</td>
      </tr>
      <tr>
        <td>30</td>
        <td>SALES</td>
        <td>CHICAGO</td>
      </tr>
      <tr>
        <td>40</td>
        <td>OPERATIONS</td>
        <td>BOSTON</td>
      </tr>
    </table>
  </body>
</html>'));

select deptno, dname, loc
from T t,
xmltable(‘/html/body/table/tr’
passing value(t) columns
deptno number path ‘/tr/td[1]’,
dname varchar2(10) path ‘/tr/td[2]’,
loc varchar2(10) path ‘/tr/td[3]’
);


    DEPTNO DNAME      LOC
---------- ---------- ----------
        10 ACCOUNTING NEW YORK
        20 RESEARCH   DALLAS
        30 SALES      CHICAGO
        40 OPERATIONS BOSTON

xmltable is a 10gR2 features. For those with 9i, use TABLE(XMLSEQUENCE(EXTRACT(VALUE(t),’/html/body/table/tr’))) instead.

Note the html file must be in XHTML format, some rewritting may imply. SQLPLUS for example does not generate html files that are valid XHTML documents, for instance because of the <p> tags that to not have a </p> tag. Note that xml tags are case sensitive.

5 thoughts on “select from xml”

  1. Very nice, Laurent, thx for this !

    By the way:
    when someone is strong and is also a smoker, this does not imply that he is a strong smoker !
    when someone is African (half) and a president, this does not imply that he is an African president !
    and:
    American presidents don’t rule the world, just the USA

    Congrats to Barack Obama

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>