create table emp (doc XML);
INSERT INTO EMP VALUES ('
<dept bldg="101"> <employee id="901"> <name> <first>John</first> <last>Doe</last> </name> <office>344</office> <salary currency="USD">55000</salary> </employee> <employee id="902"> <name> <first>Peter</first> <last>Pan</last> </name> <office>216</office> <phone>905-416-5004</phone> </employee></dept>'
);
2.利用xmltable方法，将xml格式数据转换为临时table
?SELECT X.* FROM emp, XMLTABLE ('
$d/dept/employee'
passing doc as "d" COLUMNS empID INTEGER PATH '
@id'
, firstname VARCHAR(20) PATH '
name/first'
, lastname VARCHAR(25) PATH '
name/last'
) AS X
结果图：
