db2 xml 转 table【XQuery系列】

版本：DB2 Version 9.1

1.创建测试表，初始化数据

create@H_301_14@ 
         table@H_301_14@ 
         emp (doc XML);@H_301_14@

 
        
           
         
        INSERT@H_301_14@ 
         INTO@H_301_14@ 
         EMP @H_301_14@ 
         VALUES@H_301_14@ 
         (@H_301_14@ 
         '<dept bldg="101">@H_301_14@ 
        
 
         
         @H_301_14@ 
         <employee id="901">@H_301_14@ 
        
 
         
         <name>@H_301_14@ 
        
 
         
         <first>John</first>@H_301_14@ 
        
 
        <last>Doe</last>@H_301_14@ 
         
         
         </name>@H_301_14@ 
        
 
        <office>344</office>@H_301_14@ 
         
         
         <salary currency="USD">55000</salary>@H_301_14@ 
        
 
        </employee>@H_301_14@ 
         
         
         <employee id="902">@H_301_14@ 
        
 
        <name>@H_301_14@ 
         
         
         <first>Peter</first>@H_301_14@ 
        
 
        <last>Pan</last>@H_301_14@ 
         
         
         </name>@H_301_14@ 
        
 
        <office>216</office>@H_301_14@ 
         
         
         <phone>905-416-5004</phone>@H_301_14@ 
        
 
        </employee>@H_301_14@ 
         
         
         </dept>'@H_301_14@ 
         );@H_301_14@

2.利用xmltable方法，将xml格式数据转换为临时table

SELECT@H_301_14@

X.* @H_301_14@ 
         
         
         FROM@H_301_14@ 
         emp,@H_301_14@ 
        
 
         
         XMLTABLE (@H_301_14@ 
         '$d/dept/employee'@H_301_14@ 
         passing doc @H_301_14@ 
         as@H_301_14@ 
         "d"@H_301_14@ 
        
 
         
         @H_301_14@ 
         COLUMNS @H_301_14@ 
        
 
        empID @H_301_14@ 
         INTEGER@H_301_14@ 
         PATH @H_301_14@ 
         '@id'@H_301_14@ 
         ,@H_301_14@ 
        
 
         
         firstname @H_301_14@ 
         VARCHAR@H_301_14@ 
         (20) PATH @H_301_14@ 
         'name/first'@H_301_14@ 
         sql plain" style="white-space:pre-wrap; margin:0px!important; padding:0px!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.8em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,@H_301_14@ 
        
 
        lastname @H_301_14@ 
         (25) PATH @H_301_14@ 
         'name/last'@H_301_14@ 
         ) @H_301_14@ 
         AS@H_301_14@ 
         X@H_301_14@

 
 结果图： 
 
 原文链接：https://www.f2er.com/xml/298749.html

db2 xml 转 table【XQuery系列】

猜你在找的XML相关文章