db2 xml 转 table【XQuery系列】

最新推荐文章于 2020-06-24 00:32:22 发布

iteye_14608

最新推荐文章于 2020-06-24 00:32:22 发布

阅读量299

点赞数

版本：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