版本：DB2 Version 9.1
1.创建测试表，初始化数据
create table emp (doc XML); INSERT INTO EMP VALUES (' John Doe 344 55000 Peter Pan 216 905-416-5004 ');
table emp (doc XML); INSERT INTO EMP VALUES (' John Doe 344 55000 Peter Pan 216 905-416-5004 ');
emp (doc XML); INSERT INTO EMP VALUES (' John Doe 344 55000 Peter Pan 216 905-416-5004 ');
INSERT INTO EMP VALUES (' John Doe 344 55000 Peter Pan 216 905-416-5004 ');
INTO EMP VALUES (' John Doe 344 55000 Peter Pan 216 905-416-5004 ');
EMP VALUES (' John Doe 344 55000 Peter Pan 216 905-416-5004 ');
VALUES (' John Doe 344 55000 Peter Pan 216 905-416-5004 ');
(' John Doe 344 55000 Peter Pan 216 905-416-5004 ');
' John Doe 344 55000 Peter Pan 216 905-416-5004 ');
John Doe 344 55000 Peter Pan 216 905-416-5004
John Doe 344 55000
John Doe
Doe
344 55000
55000
Peter Pan 216 905-416-5004
Peter Pan
Pan
216 905-416-5004
905-416-5004
);
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
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
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
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
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
'$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
passing doc as "d" COLUMNS empID INTEGER PATH '@id', firstname VARCHAR(20) PATH 'name/first', lastname VARCHAR(25) PATH 'name/last') AS X
as "d" COLUMNS empID INTEGER PATH '@id', firstname VARCHAR(20) PATH 'name/first', lastname VARCHAR(25) PATH 'name/last') AS X
"d" COLUMNS empID INTEGER PATH '@id', firstname VARCHAR(20) PATH 'name/first', lastname VARCHAR(25) PATH 'name/last') AS X
COLUMNS empID INTEGER PATH '@id', firstname VARCHAR(20) PATH 'name/first', lastname VARCHAR(25) PATH 'name/last') AS X
empID INTEGER PATH '@id', firstname VARCHAR(20) PATH 'name/first', lastname VARCHAR(25) PATH 'name/last') AS X
INTEGER PATH '@id', firstname VARCHAR(20) PATH 'name/first', lastname VARCHAR(25) PATH 'name/last') AS X
PATH '@id', firstname VARCHAR(20) PATH 'name/first', lastname VARCHAR(25) PATH 'name/last') AS X
'@id', firstname VARCHAR(20) PATH 'name/first', lastname VARCHAR(25) PATH 'name/last') AS X
, firstname VARCHAR(20) PATH 'name/first', lastname VARCHAR(25) PATH 'name/last') AS X
firstname VARCHAR(20) PATH 'name/first', lastname VARCHAR(25) PATH 'name/last') AS X
VARCHAR(20) PATH 'name/first', lastname VARCHAR(25) PATH 'name/last') AS X
(20) PATH 'name/first', lastname VARCHAR(25) PATH 'name/last') AS X
'name/first', lastname VARCHAR(25) PATH 'name/last') AS X
, lastname VARCHAR(25) PATH 'name/last') AS X
lastname VARCHAR(25) PATH 'name/last') AS X
VARCHAR(25) PATH 'name/last') AS X
(25) PATH 'name/last') AS X
'name/last') AS X
) AS X
AS X
X
结果图：
