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(
'


Employee table

The table of departments

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


'));

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. Sokrates

    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

  3. Pingback: Laurent Schneider » Blog Archive » select in HTML format

Comments are closed.