oracle中用dom拼写XML树

  /*
  说明：通过CP名称，模糊查询传感网络设备树状结构
  输入：CP名称
  输出：xml树状结构。节点字段如下
  */
  PROCEDURE QU_NET_DEVICE_XML(P_CP_NAME VARCHAR2, P_CUR OUT SYS_REFCURSOR) IS
 
    V_CP_MAC  VARCHAR2(20);
    V_CP_NAME VARCHAR2(40);
    V_CP_IP   VARCHAR2(20);
    /*    V_P_MAC      VARCHAR2(20);*/
    V_CP_STA     VARCHAR2(10);
    V_CP_REL_STA VARCHAR2(10);
 
    V_AP_MAC     VARCHAR2(20);
    V_AP_NAME    VARCHAR2(40);
    V_AP_IP      VARCHAR2(20);
    V_AP_STA     VARCHAR2(10);
    V_AP_REL_STA VARCHAR2(10);
    V_STATU      VARCHAR2(20); --（0确认，1未确认，2待确认，3待确认失败）
    V_AP_MAC2    VARCHAR2(20);
    V_AP_MAC3    VARCHAR2(20);
    V_AP_MAC4    VARCHAR2(20);
 
    V_ED_MAC  VARCHAR2(20);
    V_ED_NAME VARCHAR2(40);
    V_ED_STA  VARCHAR2(10);
 
    V_ED_MAC2 VARCHAR2(20);
    V_ED_MAC3 VARCHAR2(20);
    V_ED_MAC4 VARCHAR2(20);
 
    xml_clob   CLOB;
    root_node  xmldom.DOMNode;
    menu1_node xmldom.DOMNode;
    menu2_node xmldom.DOMNode;
    menu3_node xmldom.DOMNode;
    menu4_node xmldom.DOMNode;
    menu5_node xmldom.DOMNode;
    menu6_node xmldom.DOMNode;
 
    doc       xmldom.DOMDocument;
    item_elmt xmldom.DOMElement;
 
    CURSOR C_CP IS
      SELECT T.CP_MAC, T.CP_IP, T.CP_NAME, T.STATE, T.REL_STAT, T.STATUS
        FROM V_REAL_CP T
       WHERE T.CP_NAME LIKE '%' || P_CP_NAME || '%'
       ORDER BY T.CP_NAME;
 
    CURSOR C_AP1 IS
      SELECT T.AP_MAC,
             T.AP_NETWORK,
             T.AP_NAME,
             T.STATE,
             T.REL_STAT,
             T.STATUS
        FROM V_REAL_AP T
       WHERE T.BEL_ID = V_CP_MAC
         AND T.BEL_TYPE = 1
       ORDER BY T.AP_NAME;
 
    CURSOR C_AP2 IS
      SELECT T.AP_MAC,
             T.AP_NETWORK,
             T.AP_NAME,
             T.STATE,
             T.REL_STAT,
             T.STATUS
        FROM V_REAL_AP T
       WHERE T.BEL_ID = V_AP_MAC
         AND T.BEL_TYPE = 2
       ORDER BY T.AP_NAME;
    CURSOR C_AP3 IS
      SELECT T.AP_MAC,
             T.AP_NETWORK,
             T.AP_NAME,
             T.STATE,
             T.REL_STAT,
             T.STATUS
        FROM V_REAL_AP T
       WHERE T.BEL_ID = V_AP_MAC2
         AND T.BEL_TYPE = 2
       ORDER BY T.AP_NAME;
    CURSOR C_AP4 IS
      SELECT T.AP_MAC,
             T.AP_NETWORK,
             T.AP_NAME,
             T.STATE,
             T.REL_STAT,
             T.STATUS
        FROM V_REAL_AP T
       WHERE T.BEL_ID = V_AP_MAC3
         AND T.BEL_TYPE = 2
       ORDER BY T.AP_NAME;
 
    CURSOR C_ED IS
      SELECT T.ED_MAC, T.ED_NAME, T.STATE, T.STATUS
        FROM V_REAL_ED T
       WHERE T.AP_ID = V_AP_MAC
       ORDER BY T.ED_NAME;
 
    CURSOR C_ED2 IS
      SELECT T.ED_MAC, T.ED_NAME, T.STATE, T.STATUS
        FROM V_REAL_ED T
       WHERE T.AP_ID = V_AP_MAC2
       ORDER BY T.ED_NAME;
 
    CURSOR C_ED3 IS
      SELECT T.ED_MAC, T.ED_NAME, T.STATE, T.STATUS
        FROM V_REAL_ED T
       WHERE T.AP_ID = V_AP_MAC3
       ORDER BY T.ED_NAME;
 
    CURSOR C_ED4 IS
      SELECT T.ED_MAC, T.ED_NAME, T.STATE, T.STATUS
        FROM V_REAL_ED T
       WHERE T.AP_ID = V_AP_MAC4
       ORDER BY T.ED_NAME;
 
    CURSOR AP_ONLY IS
      SELECT T.AP_MAC,
             T.AP_NETWORK,
             T.AP_NAME,
             T.STATE,
             T.REL_STAT,
             T.STATUS
        FROM V_REAL_AP T
       WHERE T.EQU_RELATION = 2
         AND T.BEL_ID = V_CP_MAC
       ORDER BY T.AP_NAME;
 
    CURSOR ED_ONLY IS
      SELECT T.ED_MAC, T.ED_NAME, T.STATE, T.STATUS
        FROM V_REAL_ED T
       WHERE T.EQU_RELATION = 2
         AND T.AP_ID = V_CP_MAC
       ORDER BY T.ED_NAME;
  BEGIN
    --初始化xml
    doc       := xmldom.newDOMDocument();
    root_node := xmldom.makeNode(doc);
    item_elmt := xmldom.createElement(doc, 'Root');
    root_node := xmldom.appendChild(root_node, xmldom.makeNode(item_elmt));
 
    --循环cp---------------------------
    OPEN C_CP;
    LOOP
      FETCH C_CP
        INTO V_CP_MAC, V_CP_IP, V_CP_NAME, V_CP_STA, V_CP_REL_STA, V_STATU;
      EXIT WHEN C_CP%NOTFOUND;
   
      item_elmt := xmldom.createElement(doc, 'DEVICE');
      xmldom.setAttribute(item_elmt, 'label', V_CP_NAME);
      xmldom.setAttribute(item_elmt, 'MAC', V_CP_MAC);
      xmldom.setAttribute(item_elmt, 'IP', V_CP_IP);
      xmldom.setAttribute(item_elmt, 'TYPE', 'CP');
      xmldom.setAttribute(item_elmt, 'STAT', V_CP_STA);
      xmldom.setAttribute(item_elmt, 'REL_STAT', V_CP_REL_STA);
      xmldom.setAttribute(item_elmt, 'P_ID', '');
      xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
      xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
      xmldom.setAttribute(item_elmt, 'FLAG', 1);
      menu1_node := xmldom.appendChild(root_node,
                                       xmldom.makeNode(item_elmt));
   
      --开始循环第一层AP
      OPEN C_AP1;
      LOOP
        FETCH C_AP1
          INTO V_AP_MAC, V_AP_IP, V_AP_NAME, V_AP_STA, V_AP_REL_STA, V_STATU;
        EXIT WHEN C_AP1%NOTFOUND;
     
        item_elmt := xmldom.createElement(doc, 'DEVICE');
        xmldom.setAttribute(item_elmt, 'label', V_AP_NAME);
        xmldom.setAttribute(item_elmt, 'MAC', V_AP_MAC);
        xmldom.setAttribute(item_elmt, 'IP', V_AP_IP);
        xmldom.setAttribute(item_elmt, 'TYPE', 'AP');
        xmldom.setAttribute(item_elmt, 'STAT', V_AP_STA);
        xmldom.setAttribute(item_elmt, 'REL_STAT', V_AP_REL_STA);
        xmldom.setAttribute(item_elmt, 'P_ID', V_CP_MAC);
        xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
        xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
        xmldom.setAttribute(item_elmt, 'FLAG', 1);
        menu2_node := xmldom.appendChild(menu1_node,
                                         xmldom.makeNode(item_elmt));
        --开始循环第二层AP
        OPEN C_AP2;
        LOOP
          FETCH C_AP2
            INTO V_AP_MAC2, V_AP_IP, V_AP_NAME, V_AP_STA, V_AP_REL_STA, V_STATU;
          EXIT WHEN C_AP2%NOTFOUND;
       
          item_elmt := xmldom.createElement(doc, 'DEVICE');
          xmldom.setAttribute(item_elmt, 'label', V_AP_NAME);
          xmldom.setAttribute(item_elmt, 'MAC', V_AP_MAC2);
          xmldom.setAttribute(item_elmt, 'IP', V_AP_IP);
          xmldom.setAttribute(item_elmt, 'TYPE', 'AP');
          xmldom.setAttribute(item_elmt, 'STAT', V_AP_STA);
          xmldom.setAttribute(item_elmt, 'REL_STAT', V_AP_REL_STA);
          xmldom.setAttribute(item_elmt, 'P_ID', V_AP_MAC);
          xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
          xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
          xmldom.setAttribute(item_elmt, 'FLAG', 1);
          menu3_node := xmldom.appendChild(menu2_node,
                                           xmldom.makeNode(item_elmt));
          --开始循环第三层AP
          OPEN C_AP3;
          LOOP
            FETCH C_AP3
              INTO V_AP_MAC3, V_AP_IP, V_AP_NAME, V_AP_STA, V_AP_REL_STA, V_STATU;
            EXIT WHEN C_AP3%NOTFOUND;
         
            item_elmt := xmldom.createElement(doc, 'DEVICE');
            xmldom.setAttribute(item_elmt, 'label', V_AP_NAME);
            xmldom.setAttribute(item_elmt, 'MAC', V_AP_MAC3);
            xmldom.setAttribute(item_elmt, 'IP', V_AP_IP);
            xmldom.setAttribute(item_elmt, 'TYPE', 'AP');
            xmldom.setAttribute(item_elmt, 'STAT', V_AP_STA);
            xmldom.setAttribute(item_elmt, 'REL_STAT', V_AP_REL_STA);
            xmldom.setAttribute(item_elmt, 'P_ID', V_AP_MAC2);
            xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
            xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
            xmldom.setAttribute(item_elmt, 'FLAG', 1);
            menu4_node := xmldom.appendChild(menu3_node,
                                             xmldom.makeNode(item_elmt));
         
            --开始循环第四层AP
            OPEN C_AP4;
            LOOP
              FETCH C_AP4
                INTO V_AP_MAC4, V_AP_IP, V_AP_NAME, V_AP_STA, V_AP_REL_STA, V_STATU;
              EXIT WHEN C_AP4%NOTFOUND;
           
              item_elmt := xmldom.createElement(doc, 'DEVICE');
              xmldom.setAttribute(item_elmt, 'label', V_AP_NAME);
              xmldom.setAttribute(item_elmt, 'MAC', V_AP_MAC4);
              xmldom.setAttribute(item_elmt, 'IP', V_AP_IP);
              xmldom.setAttribute(item_elmt, 'TYPE', 'AP');
              xmldom.setAttribute(item_elmt, 'STAT', V_AP_STA);
              xmldom.setAttribute(item_elmt, 'REL_STAT', V_AP_REL_STA);
              xmldom.setAttribute(item_elmt, 'P_ID', V_AP_MAC3);
              xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
              xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
              xmldom.setAttribute(item_elmt, 'FLAG', 1);
              menu5_node := xmldom.appendChild(menu4_node,
                                               xmldom.makeNode(item_elmt));
              --开始循环地四层AP下的ED--------------    
              OPEN C_ED4;
              LOOP
                FETCH C_ED4
                  INTO V_ED_MAC4, V_ED_NAME, V_ED_STA, V_STATU;
                EXIT WHEN C_ED4%NOTFOUND;
             
                item_elmt := xmldom.createElement(doc, 'DEVICE');
                xmldom.setAttribute(item_elmt, 'label', V_ED_NAME);
                xmldom.setAttribute(item_elmt, 'MAC', V_ED_MAC4);
                --  xmldom.setAttribute(item_elmt, 'IP', V_ED_IP);
                xmldom.setAttribute(item_elmt, 'TYPE', 'ED');
                xmldom.setAttribute(item_elmt, 'STAT', V_ED_STA);
                xmldom.setAttribute(item_elmt, 'REL_STAT', '');
                xmldom.setAttribute(item_elmt, 'P_ID', V_AP_MAC4);
                xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
                xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
                xmldom.setAttribute(item_elmt, 'FLAG', 1);
                menu6_node := xmldom.appendChild(menu5_node,
                                                 xmldom.makeNode(item_elmt));
             
              END LOOP;
              CLOSE C_ED4;
           
            END LOOP;
            CLOSE C_AP4;
         
            --开始循环地三层AP下的ED--------------    
            OPEN C_ED3;
            LOOP
              FETCH C_ED3
                INTO V_ED_MAC3, V_ED_NAME, V_ED_STA, V_STATU;
              EXIT WHEN C_ED3%NOTFOUND;
           
              item_elmt := xmldom.createElement(doc, 'DEVICE');
              xmldom.setAttribute(item_elmt, 'label', V_ED_NAME);
              xmldom.setAttribute(item_elmt, 'MAC', V_ED_MAC3);
              --       xmldom.setAttribute(item_elmt, 'IP', V_ED_IP);
              xmldom.setAttribute(item_elmt, 'TYPE', 'ED');
              xmldom.setAttribute(item_elmt, 'STAT', V_ED_STA);
              xmldom.setAttribute(item_elmt, 'REL_STAT', '');
              xmldom.setAttribute(item_elmt, 'P_ID', V_AP_MAC3);
              xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
              xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
              xmldom.setAttribute(item_elmt, 'FLAG', 1);
              menu5_node := xmldom.appendChild(menu4_node,
                                               xmldom.makeNode(item_elmt));
           
            END LOOP;
            CLOSE C_ED3;
         
          END LOOP;
          CLOSE C_AP3;
          --开始循环第二层ap下的ED--------------    
          OPEN C_ED2;
          LOOP
            FETCH C_ED2
              INTO V_ED_MAC2, V_ED_NAME, V_ED_STA, V_STATU;
            EXIT WHEN C_ED2%NOTFOUND;
         
            item_elmt := xmldom.createElement(doc, 'DEVICE');
            xmldom.setAttribute(item_elmt, 'label', V_ED_NAME);
            xmldom.setAttribute(item_elmt, 'MAC', V_ED_MAC2);
            --      xmldom.setAttribute(item_elmt, 'IP', V_ED_IP);
            xmldom.setAttribute(item_elmt, 'TYPE', 'ED');
            xmldom.setAttribute(item_elmt, 'STAT', V_ED_STA);
            xmldom.setAttribute(item_elmt, 'REL_STAT', '');
            xmldom.setAttribute(item_elmt, 'P_ID', V_AP_MAC2);
            xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
            xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
            xmldom.setAttribute(item_elmt, 'FLAG', 1);
            menu4_node := xmldom.appendChild(menu3_node,
                                             xmldom.makeNode(item_elmt));
         
          END LOOP;
          CLOSE C_ED2;
       
        END LOOP;
        CLOSE C_AP2;
     
        --开始循环第一层ap下的ED--------------    
        OPEN C_ED;
        LOOP
          FETCH C_ED
            INTO V_ED_MAC, V_ED_NAME, V_ED_STA, V_STATU;
          EXIT WHEN C_ED%NOTFOUND;
       
          item_elmt := xmldom.createElement(doc, 'DEVICE');
          xmldom.setAttribute(item_elmt, 'label', V_ED_NAME);
          xmldom.setAttribute(item_elmt, 'MAC', V_ED_MAC);
          --      xmldom.setAttribute(item_elmt, 'IP', V_ED_IP);
          xmldom.setAttribute(item_elmt, 'TYPE', 'ED');
          xmldom.setAttribute(item_elmt, 'STAT', V_ED_STA);
          xmldom.setAttribute(item_elmt, 'REL_STAT', '');
          xmldom.setAttribute(item_elmt, 'P_ID', V_AP_MAC);
          xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
          xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
          xmldom.setAttribute(item_elmt, 'FLAG', 1);
          menu3_node := xmldom.appendChild(menu2_node,
                                           xmldom.makeNode(item_elmt));
       
        END LOOP;
        CLOSE C_ED;
     
      END LOOP;
      CLOSE C_AP1;
      --完成第一层AP循环
      item_elmt := xmldom.createElement(doc, 'DEVICE');
      xmldom.setAttribute(item_elmt, 'label', '无拓扑关系设备');
      xmldom.setAttribute(item_elmt, 'MAC', V_CP_MAC);
      menu2_node := xmldom.appendChild(menu1_node,
                                       xmldom.makeNode(item_elmt));
      OPEN AP_ONLY;
      LOOP
        FETCH AP_ONLY
          INTO V_AP_MAC, V_AP_IP, V_AP_NAME, V_AP_STA, V_AP_REL_STA, V_STATU;
        EXIT WHEN AP_ONLY%NOTFOUND;
     
        item_elmt := xmldom.createElement(doc, 'DEVICE');
        xmldom.setAttribute(item_elmt, 'label', V_AP_NAME);
        xmldom.setAttribute(item_elmt, 'MAC', V_AP_MAC);
        xmldom.setAttribute(item_elmt, 'IP', V_AP_IP);
        xmldom.setAttribute(item_elmt, 'TYPE', 'AP');
        xmldom.setAttribute(item_elmt, 'STAT', V_AP_STA);
        xmldom.setAttribute(item_elmt, 'REL_STAT', V_AP_REL_STA);
        xmldom.setAttribute(item_elmt, 'P_ID', '');
        xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
        xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
        xmldom.setAttribute(item_elmt, 'FLAG', 1);
        menu3_node := xmldom.appendChild(menu2_node,
                                         xmldom.makeNode(item_elmt));
      END LOOP;
      CLOSE AP_ONLY;
   
      OPEN ED_ONLY;
      LOOP
        FETCH ED_ONLY
          INTO V_ED_MAC4, V_ED_NAME, V_ED_STA, V_STATU;
        EXIT WHEN ED_ONLY%NOTFOUND;
     
        item_elmt := xmldom.createElement(doc, 'DEVICE');
        xmldom.setAttribute(item_elmt, 'label', V_ED_NAME);
        xmldom.setAttribute(item_elmt, 'MAC', V_ED_MAC4);
        --  xmldom.setAttribute(item_elmt, 'IP', V_ED_IP);
        xmldom.setAttribute(item_elmt, 'TYPE', 'ED');
        xmldom.setAttribute(item_elmt, 'STAT', V_ED_STA);
        xmldom.setAttribute(item_elmt, 'REL_STAT', '');
        xmldom.setAttribute(item_elmt, 'P_ID', '');
        xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
        xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
        xmldom.setAttribute(item_elmt, 'FLAG', 1);
        menu3_node := xmldom.appendChild(menu2_node,
                                         xmldom.makeNode(item_elmt));
     
      END LOOP;
      CLOSE ED_ONLY;
      --完成cp的循环
    END LOOP;
    CLOSE C_CP;
 
    --
 
    --
 
    dbms_lob.createtemporary(xml_clob, TRUE);
    xmldom.writeToClob(root_node, xml_clob);
    xmldom.freeDocument(doc);
 
    OPEN P_CUR FOR
      SELECT replace(xml_clob, ''', '') out_xml FROM dual;
  END QU_NET_DEVICE_XML;