Db2 xml to table

Db2 xml to table Version: DB2 Version 9.1 1. Create a test table and initialize data

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. Use the xmltable method to convert xml format data to a temporary 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

 

Result chart: