ORACLE 解析xml字串-轉載的

標籤：

1.xml字串

/*

<orderlist>
        <order>
        <orderid>1</orderid>
        <ordernumber>857544544</ordernumber>
        <orderprice>54</orderprice>
        </order>
        
        <order>
        <orderid>2</orderid>
        <ordernumber>858544544</ordernumber>
        <orderprice>63</orderprice>
        </order>
        
        <order>
        <orderid>3</orderid>
        <ordernumber>454854555</ordernumber>
        <orderprice>781</orderprice>
        </order>        
</orderlist>

 

*/

 

2.oracle預存程序

 

CREATE OR REPLACE PROCEDURE p_xmlparse (p_xml   IN     CLOB,
                                                                     r_cur      OUT SYS_REFCURSOR)  
        AS  
           /***  
           xml格式<?xml version="1.0" encoding="utf-8"?>  
           <material id="101">  
               <attribute0>value0</attribute0>  
               <attribute1>value1</attribute1>  
               <attribute2>value2</attribute2>  
           </material>  
           ***/  
        -- xml解析器  
           xmlpar          xmlparser.parser := xmlparser.newparser;  
        -- dom文檔對象  
           doc             xmldom.domdocument;  
           materialnodes   xmldom.domnodelist;  
           materialid      VARCHAR2 (50);  
           chilnodes       xmldom.domnodelist;  
           tempnode        xmldom.domnode;  
           temparrmap      xmldom.domnamednodemap;  
        -- 以下變數用於擷取xml節點的值  
           v_attribute     VARCHAR2 (50);  
           v_value         VARCHAR2 (50);  
           tmp             INTEGER;  
           l_sql           VARCHAR2 (32767) := ‘select ‘;  
        BEGIN  
           xmlparser.parseclob (xmlpar, p_xml);  
           doc := xmlparser.getdocument (xmlpar);  
        -- 釋放解析器執行個體  
           xmlparser.freeparser (xmlpar);  
           materialnodes := xmldom.getelementsbytagname (doc, ‘material‘);  
           tempnode := xmldom.item (materialnodes, 0);  
        -- 擷取根項目的ID屬性值  
           materialid := xmldom.getattribute (xmldom.getdocumentelement (doc), ‘id‘);  
        -- 所有屬性  
           temparrmap := xmldom.getattributes (tempnode);  
        -- 擷取子項目的值  
           chilnodes := xmldom.getchildnodes (tempnode);  
           tmp := xmldom.getlength (chilnodes);  
           l_sql := l_sql || materialid || ‘ as materialid‘;  
        FOR i IN 0 .. tmp - 1  
           LOOP  
              v_attribute := xmldom.getnodename (xmldom.item (chilnodes, i));  
              v_value :=  
                 xmldom.getnodevalue (  
                    xmldom.getfirstchild (xmldom.item (chilnodes, i)));  
              l_sql := l_sql || ‘,‘‘‘ || v_value || ‘‘‘ as ‘ || v_attribute;  
        -- dbms_output.put_line(materialid||‘ ‘||v_attribute||‘ ‘||v_value);  
        END LOOP;  
           l_sql := l_sql || ‘ from dual‘;  
        -- DBMS_OUTPUT.put_line (l_sql);  
        OPEN r_cur FOR l_sql;  
        -- 釋放文檔對象  
           xmldom.freedocument (doc);  
        EXCEPTION  
        WHEN OTHERS  
        THEN  
              DBMS_OUTPUT.put_line (SQLERRM);  
        END p_xmlparse; 

 

 

3.執行結果

來源文件地址：http://blog.csdn.net/wzy0623/article/details/8245062

ORACLE 解析xml字串-轉載的