当前位置:编程学习 > XML/UML >>

db2 xml转table

db2 xml转table
 
版本:DB2 Version 9.1 
 
1.创建测试表,初始化数据
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

 

结果图:
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,