This example shows how to create an Oracle XML object using DBMS_XMLDOM package. It includes

  • XML declaration (Version and Charset)
  • XML namespace
  • Looping through a cursor to add recursive nodes
DECLARE
   L_XMLTYPE             XMLTYPE;
   L_DOMDOC              DBMS_XMLDOM.DOMDOCUMENT;
   L_ROOT_NODE           DBMS_XMLDOM.DOMNODE;

   PARTS_ELEMENT         DBMS_XMLDOM.DOMELEMENT;
   PARTS_NODE            DBMS_XMLDOM.DOMNODE;

   PART_ELEMENT          DBMS_XMLDOM.DOMELEMENT;
   PART_NODE             DBMS_XMLDOM.DOMNODE;

   PART_NO_ELEMENT       DBMS_XMLDOM.DOMELEMENT;
   PART_NO_NODE          DBMS_XMLDOM.DOMNODE;
   
   DESC_ELEMENT        	 DBMS_XMLDOM.DOMELEMENT;
   DESC_NODE             DBMS_XMLDOM.DOMNODE;
   
   UOM_ELEMENT        	 DBMS_XMLDOM.DOMELEMENT;
   UOM_NODE           	 DBMS_XMLDOM.DOMNODE;
   
   TEXT_                 DBMS_XMLDOM.DOMTEXT;
   TEXT_NODE_            DBMS_XMLDOM.DOMNODE;   
   ATTR_                 DBMS_XMLDOM.DOMATTR;
   
   CURSOR get_parts IS
   SELECT * FROM part_catalog
   WHERE part_no LIKE 'TEST%';
BEGIN
   -- Create an empty XML document
   L_DOMDOC := DBMS_XMLDOM.NEWDOMDOCUMENT;
   --XML declaration
   dbms_xmldom.setVersion(L_DOMDOC, '1.0" encoding="UTF-8');
   dbms_xmldom.setCharset(L_DOMDOC,'UTF-8');
   
   -- Create a root node
   L_ROOT_NODE := DBMS_XMLDOM.MAKENODE (L_DOMDOC);

   --<parts&gt;
   PARTS_ELEMENT := DBMS_XMLDOM.CREATEELEMENT (L_DOMDOC, 'parts');   
   PARTS_NODE := DBMS_XMLDOM.APPENDCHILD (L_ROOT_NODE,DBMS_XMLDOM.MAKENODE (PARTS_ELEMENT));
  
   --add xmlns
   ATTR_ :=  DBMS_XMLDOM.CREATEATTRIBUTE (L_DOMDOC, 'xmlns:xsd');
             DBMS_XMLDOM.SETVALUE(ATTR_,'http://www.w3.org/2001/XMLSchema');
   ATTR_ :=  DBMS_XMLDOM.SETATTRIBUTENODE(PARTS_ELEMENT,ATTR_);
   
   ATTR_ :=  DBMS_XMLDOM.CREATEATTRIBUTE (L_DOMDOC, 'xmlns:xsi');
             DBMS_XMLDOM.SETVALUE(ATTR_,'http://www.w3.org/2001/XMLSchema-instance');
   ATTR_ :=  DBMS_XMLDOM.SETATTRIBUTENODE(PARTS_ELEMENT,ATTR_);  
   
   ATTR_ :=  DBMS_XMLDOM.CREATEATTRIBUTE (L_DOMDOC, 'xmlns:ns1');
             DBMS_XMLDOM.SETVALUE(ATTR_,'IFS.MasterPartRec');
   ATTR_ :=  DBMS_XMLDOM.SETATTRIBUTENODE(PARTS_ELEMENT,ATTR_);   
   
   FOR partRec IN get_parts LOOP
   
   --<part&gt;
   
   PART_ELEMENT := DBMS_XMLDOM.CREATEELEMENT (L_DOMDOC, 'part');
   PART_NODE := DBMS_XMLDOM.APPENDCHILD (PARTS_NODE, DBMS_XMLDOM.MAKENODE (PART_ELEMENT));
   

   --<part_no&gt;
   PART_NO_ELEMENT := DBMS_XMLDOM.CREATEELEMENT (L_DOMDOC, 'part_no');
   PART_NO_NODE := DBMS_XMLDOM.APPENDCHILD (PART_NODE, DBMS_XMLDOM.MAKENODE (PART_NO_ELEMENT));
  
   TEXT_ := DBMS_XMLDOM.CREATETEXTNODE (L_DOMDOC, partRec.part_no);
   TEXT_NODE_ := DBMS_XMLDOM.APPENDCHILD (PART_NO_NODE, DBMS_XMLDOM.MAKENODE(TEXT_));
   
   --<description&gt;
   DESC_ELEMENT := DBMS_XMLDOM.CREATEELEMENT (L_DOMDOC, 'description');
   DESC_NODE := DBMS_XMLDOM.APPENDCHILD (PART_NODE, DBMS_XMLDOM.MAKENODE (DESC_ELEMENT));
   
   TEXT_ := DBMS_XMLDOM.CREATETEXTNODE (L_DOMDOC, partRec.description);
   TEXT_NODE_ := DBMS_XMLDOM.APPENDCHILD (DESC_NODE, DBMS_XMLDOM.MAKENODE (TEXT_));
 
   --<uom&gt;
   UOM_ELEMENT := DBMS_XMLDOM.CREATEELEMENT (L_DOMDOC, 'unit_code');
   UOM_NODE := DBMS_XMLDOM.APPENDCHILD (PART_NODE, DBMS_XMLDOM.MAKENODE (UOM_ELEMENT));
   
   TEXT_ := DBMS_XMLDOM.CREATETEXTNODE (L_DOMDOC, partRec.unit_code);
   TEXT_NODE_ := DBMS_XMLDOM.APPENDCHILD (UOM_NODE, DBMS_XMLDOM.MAKENODE (TEXT_));  
    
   --uom attr
   ATTR_ :=  DBMS_XMLDOM.CREATEATTRIBUTE (L_DOMDOC, 'measured_dttm');
             DBMS_XMLDOM.SETVALUE(ATTR_,TO_CHAR(SYSDATE, 'DD-MM-YYYY hh:mi:ss'));
   ATTR_ :=  DBMS_XMLDOM.SETATTRIBUTENODE(UOM_ELEMENT,ATTR_);   
  
   NULL;
   END LOOP;
   L_XMLTYPE := DBMS_XMLDOM.GETXMLTYPE (L_DOMDOC);
   DBMS_XMLDOM.FREEDOCUMENT (L_DOMDOC);

   DBMS_OUTPUT.PUT_LINE (L_XMLTYPE.GETCLOBVAL);
END;
Advertisements