How to use DBMS_XMLDOM to create XML from PL SQL

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

PART_CATALOG table consists of records in below structure

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>
   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>
   
   PART_ELEMENT := DBMS_XMLDOM.CREATEELEMENT (L_DOMDOC, 'part');
   PART_NODE := DBMS_XMLDOM.APPENDCHILD (PARTS_NODE, DBMS_XMLDOM.MAKENODE (PART_ELEMENT));
   

   --<part_no>
   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>
   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>
   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;

Output XML looks like following

<?xml version="1.0" encoding="UTF-8"?>
<parts xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns1="IFS.MasterPartRec">
  <part>
    <part_no>TEST1</part_no>
    <description>test1</description>
    <unit_code measured_dttm="06-06-2020 07:49:28">pcs</unit_code>
  </part>
  <part>
    <part_no>TEST11</part_no>
    <description>Test 1</description>
    <unit_code measured_dttm="06-06-2020 07:49:28">pcs</unit_code>
  </part>
  <part>
    <part_no>TEST111</part_no>
    <description>111</description>
    <unit_code measured_dttm="06-06-2020 07:49:28">VIAL</unit_code>
  </part>
</parts>