Oracle 从XML中提取值

INPUT XML:

        
               0017700033018
               05/01/2017
               NEW
               $69.98
               $0.00
               $17.95
               $17.95
               $0.90
               $0.00
               $70.88
        
        
                        Patti
                        Kelly
                        3328 E. Farrin Avenue
                        
                        Fresno
                        CA
                        93726
                        United States
                        559-229-5567
        
        
                         Patti
                         Kelly
                         3328 E. Farrin Avenue
                         
                         Fresno
                         CA
                         93726
                         United States
                         559-229-5567
        
        
                                     
                                         _PAYMENTMETHODCONTENT__
                                         
                                           Visa
                                           Patti L.
                                           ************8504
                                           CREDIT_CARD
                                           Kelly
                                         
                      
        
        
                    
                      _ORDERITEMSCONTENT__
                                         
                                           35656
                                           $34.99
                                           1
                                           $34.99
                                           $34.99
                                           
                                           
                                           Premium No Iron Khaki Sand 33 30
                                         
                                         
                                           35726
                                           $34.99
                                           1
                                           $34.99
                                           $34.99
                                           
                                           
                                           Premium No Iron Khaki British Khaki 33 30
                                         
                     
    


OUTPUT:
35656
35726
复制

SQL> set serverout on
SQL> declare
  2    c xmltype := xmltype('
  3  
  4          
  5                 0017700033018
  6                 05/01/2017
  7                 NEW
  8                 $69.98
  9                 $0.00
 10                 $17.95
 11                 $17.95
 12                 $0.90
 13                 $0.00
 14                 $70.88
 15          
 16          
 17                          Patti
 18                          Kelly
 19                          3328 E. Farrin Avenue
 20                          
 21                          Fresno
 22                          CA
 23                          93726
 24                          United States
 25                          559-229-5567
 26          
 27          
 28                           Patti
 29                           Kelly
 30                           3328 E. Farrin Avenue
 31                           
 32                           Fresno
 33                           CA
 34                           93726
 35                           United States
 36                           559-229-5567
 37          
 38          
 39                         
 40                                           _PAYMENTMETHODCONTENT__
 41                                           
 42                                             Visa
 43                                             Patti L.
 44                                             ************8504
 45                                             CREDIT_CARD
 46                                             Kelly
 47                                           
 48                        
 49          
 50          
 51                      
 52                        _ORDERITEMSCONTENT__
 53                                           
 54                                             35656
 55                                             $34.99
 56                                             1
 57                                             $34.99
 58                                             $34.99
 59                                             
 60                                             
 61                                             Premium No Iron Khaki Sand 33 30
 62                                           
 63                                           
 64                                             35726
 65                                             $34.99
 66                                             1
 67                                             $34.99
 68                                             $34.99
 69                                             
 70                                             
 71                                             Premium No Iron Khaki British Khaki 33 30
 72                                           
 73                       
 74      
 75  ');
 76  begin
 77    for i in (
 78         SELECT xt.* FROM
 79         XMLTABLE('/column/ORDERITEMS/mv/row/col'
 80           PASSING c
 81           COLUMNS
 82             "ATTR1"     VARCHAR2(80)  PATH '/',
 83             "ATTR2"    VARCHAR2(80)  PATH '@name'
 84           ) xt
 85        where attr2 = 'ITEMNO'
 86    ) loop
 87              dbms_output.put_line(i.ATTR1);
 88    end loop;
 89  end;
 90  /
35656
35726

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
复制