Create XML Using DBMS_XMLDOM

DBMS_XMLDOM

The DBMS_XMLDOM package is used to access XMLType objects, and implements the Document Object Model (DOM), an application programming interface for HTML and XML documents. The Document Object Model (DOM) is an application programming interface (API) for HTML and XML documents. It defines the logical structure of documents, and the manner in which they are accessed and manipulated. To learn more about DBMS_XMLDOM package, please refer the documentation from Oracle.

We will be more specifically working with the following functions and procedures:

• newDomDocument
• makeNode
• appendChild
• createElement
• createTextNode
• getXmlType
• freeDocument

In this post, we will take a look at how to create XML using DBMS_XMLDOM package. We will be using the DBMS_XMLDOM package in a Oracle PL/SQL procedure to generate the XML file.

Generate Supplier Details

In our first example, we will look at how we can create supplier details in XML using PLSQL. The sample XML output is given below:

<SUPPLIER_DETAILS>
   <SUPPLIER>
      <SUPPLIER_NUMBER> </SUPPLIER_NUMBER>
      <SUPPLIER_NAME> </SUPPLIER_NAME>
   </SUPPLIER>
   <SUPPLIER>
      <SUPPLIER_NUMBER> </SUPPLIER_NUMBER>
      <SUPPLIER_NAME> </SUPPLIER_NAME>
   </SUPPLIER>
</SUPPLIER_DETAILS>

Based on the above sample XML file, you can see that for each supplier the supplier name and number details are are repeated.

Now lets take a look at the PL/SQL code to generate the same xml file.

DECLARE
   l_xmltype XMLTYPE;
   l_domdoc dbms_xmldom.DOMDocument;
   l_root_node dbms_xmldom.DOMNode;

   l_supp_num_element    dbms_xmldom.DOMElement;
   l_supp_name_element   dbms_xmldom.DOMElement;

   l_supp_num_node       dbms_xmldom.DOMNode;
   l_supp_name_node      dbms_xmldom.DOMNode;

   l_supp_num_tnode      dbms_xmldom.DOMNode;
   l_supp_name_tnode     dbms_xmldom.DOMNode;

   l_supp_num_text       dbms_xmldom.DOMText;
   l_supp_name_text      dbms_xmldom.DOMText;

   l_supplier_element    dbms_xmldom.DOMElement;
   l_supplier_node       dbms_xmldom.DOMNode;
   l_sup_node            dbms_xmldom.DOMNode;
   l_sup_element         dbms_xmldom.DOMElement;

BEGIN

   -- Create an empty XML document
   l_domdoc := dbms_xmldom.newDomDocument;

   -- Create a root node
   l_root_node := dbms_xmldom.makeNode(l_domdoc);

   -- Create a new Supplier Node and add it to the root node
   l_sup_node := dbms_xmldom.appendChild( l_root_node
                                        , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'SUPPLIER_ADDRESS'))
                                         );

FOR sup_rec IN (SELECT aps.vendor_name,
                       aps.segment1,
                       assa.address_line1,
                       assa.city,
                       assa.state,
                       assa.zip
                  FROM ap_suppliers aps,
                       ap_supplier_sites_all assa
                 WHERE assa.vendor_id = aps.vendor_id
                   AND aps.vendor_id in (1003,1004)
                 ORDER BY 2)
  LOOP

    -- For each record, create a new Supplier element
    -- and add this new Supplier element to the Supplier Parent node
    l_supplier_element := dbms_xmldom.createElement(l_domdoc, 'SUPPLIER' );
    l_supplier_node    := dbms_xmldom.appendChild(l_sup_node,dbms_xmldom.makeNode(l_supplier_element));

    -- Each Supplier node will get a Number node which contains the Supplier Number as text
    l_supp_num_element := dbms_xmldom.createElement(l_domdoc, 'SUPPLIER_NUMBER' );
    l_supp_num_node    := dbms_xmldom.appendChild(l_supplier_node,dbms_xmldom.makeNode(l_supp_num_element));
    l_supp_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.segment1 );
    l_supp_num_tnode   := dbms_xmldom.appendChild(l_supp_num_node,dbms_xmldom.makeNode(l_supp_num_text));

    -- Each Supplier node will get a Name node which contains the Supplier Name as text
    l_supp_name_element := dbms_xmldom.createElement(l_domdoc, 'SUPPLIER_NAME' );
    l_supp_name_node    := dbms_xmldom.appendChild(l_supplier_node,dbms_xmldom.makeNode(l_supp_name_element));
    l_supp_name_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.vendor_name );
    l_supp_name_tnode   := dbms_xmldom.appendChild(l_supp_name_node,dbms_xmldom.makeNode(l_supp_name_text));

  END LOOP;

  l_xmltype := dbms_xmldom.getXmlType(l_domdoc);
  dbms_xmldom.freeDocument(l_domdoc);

  dbms_output.put_line(l_xmltype.getClobVal);

END;

Generate Supplier and Address Details (Sub Group)

In the next example, we will look at how to generate the Address details as a sub group.

<SUPPLIER_ADDRESS>
  <SUPPLIER>
    <SUPPLIER_NUMBER> </SUPPLIER_NUMBER>
    <SUPPLIER_NAME> </SUPPLIER_NAME>
    <ADDRESS>
      <ADDRESS1> </ADDRESS1>
      <CITY> </CITY>
      <STATE> </STATE>
      <ZIP> </ZIP>
    </ADDRESS>
  </SUPPLIER>
</SUPPLIER_ADDRESS>

As you can see, there is a new group / level (ADDRESS) within the “SUPPLIER” group. For each Address for a supplier, the “ADDRESS” group will be repeated as many times.
The below PLSQL package can be used to generate the same. You can see that I have used a cursor within in the first FOR Loop.

DECLARE
   l_xmltype XMLTYPE;
   l_domdoc dbms_xmldom.DOMDocument;
   l_root_node dbms_xmldom.DOMNode;

   l_supp_num_element    dbms_xmldom.DOMElement;
   l_supp_name_element   dbms_xmldom.DOMElement;
   l_address_element     dbms_xmldom.DOMElement;
   l_city_element        dbms_xmldom.DOMElement;
   l_state_element       dbms_xmldom.DOMElement;
   l_zip_element         dbms_xmldom.DOMElement;

   l_supp_num_node       dbms_xmldom.DOMNode;
   l_supp_name_node      dbms_xmldom.DOMNode;
   l_address_node        dbms_xmldom.DOMNode;
   l_city_node           dbms_xmldom.DOMNode;
   l_state_node          dbms_xmldom.DOMNode;
   l_zip_node            dbms_xmldom.DOMNode;

   l_supp_num_tnode      dbms_xmldom.DOMNode;
   l_supp_name_tnode     dbms_xmldom.DOMNode;
   l_address_tnode       dbms_xmldom.DOMNode;
   l_city_tnode          dbms_xmldom.DOMNode;
   l_state_tnode         dbms_xmldom.DOMNode;
   l_zip_tnode           dbms_xmldom.DOMNode;

   l_supp_num_text       dbms_xmldom.DOMText;
   l_supp_name_text      dbms_xmldom.DOMText;
   l_address_text        dbms_xmldom.DOMText;
   l_city_text           dbms_xmldom.DOMText;
   l_state_text          dbms_xmldom.DOMText;
   l_zip_text            dbms_xmldom.DOMText;   

   l_supplier_element    dbms_xmldom.DOMElement;
   l_supplier_node       dbms_xmldom.DOMNode;
   l_sup_node            dbms_xmldom.DOMNode;
   l_add_node            dbms_xmldom.DOMNode;
   l_sup_element         dbms_xmldom.DOMElement;
   l_add_element         dbms_xmldom.DOMElement;

BEGIN

   -- Create an empty XML document
   l_domdoc := dbms_xmldom.newDomDocument;

   -- Create a root node
   l_root_node := dbms_xmldom.makeNode(l_domdoc);

   -- Create a new Supplier Node and add it to the root node
   l_sup_node := dbms_xmldom.appendChild( l_root_node
                                          , dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'SUPPLIER_INVOICES' ))
                                         );

FOR sup_rec IN (SELECT vendor_name,
                       segment1,
                       vendor_id
                  FROM ap_suppliers
                 WHERE vendor_id in (1003,1004))
LOOP

      -- For each record, create a new Supplier element
      -- and add this new Supplier element to the Supplier Parent node
      l_supplier_element := dbms_xmldom.createElement(l_domdoc, 'SUPPLIER' );
      l_supplier_node    := dbms_xmldom.appendChild(l_sup_node,dbms_xmldom.makeNode(l_supplier_element));

      -- Each Supplier node will get a Number node which contains the Supplier Number as text
      l_supp_num_element := dbms_xmldom.createElement(l_domdoc, 'SUPPLIER_NUMBER' );
      l_supp_num_node    := dbms_xmldom.appendChild(l_supplier_node,dbms_xmldom.makeNode(l_supp_num_element));
      l_supp_num_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.segment1 );
      l_supp_num_tnode   := dbms_xmldom.appendChild(l_supp_num_node,dbms_xmldom.makeNode(l_supp_num_text));

      -- Each Supplier node will get a Name node which contains the Supplier Name as text
      l_supp_name_element := dbms_xmldom.createElement(l_domdoc, 'SUPPLIER_NAME' );
      l_supp_name_node    := dbms_xmldom.appendChild(l_supplier_node,dbms_xmldom.makeNode(l_supp_name_element));
      l_supp_name_text    := dbms_xmldom.createTextNode(l_domdoc, sup_rec.vendor_name );
      l_supp_name_tnode   := dbms_xmldom.appendChild(l_supp_name_node,dbms_xmldom.makeNode(l_supp_name_text));

      FOR add_rec IN (SELECT address_line1, city, state, zip
                        FROM ap_supplier_sites_all
                       WHERE vendor_id = sup_rec.vendor_id)
      LOOP

        l_add_element := dbms_xmldom.createElement(l_domdoc, 'ADDRESS' );
        l_add_node := dbms_xmldom.appendChild(l_supplier_node,dbms_xmldom.makeNode(l_add_element));

        -- Each Address node will get a Address node which contains the Supplier Address Line1 as text
        l_address_element := dbms_xmldom.createElement(l_domdoc, 'ADDRESSS1' );
        l_address_node    := dbms_xmldom.appendChild(l_add_node,dbms_xmldom.makeNode(l_address_element));
        l_address_text    := dbms_xmldom.createTextNode(l_domdoc, add_rec.address_line1 );
        l_address_tnode   := dbms_xmldom.appendChild(l_address_node,dbms_xmldom.makeNode(l_address_text));

        -- Each Address node will get a City node which contains the City as text
        l_city_element    := dbms_xmldom.createElement(l_domdoc, 'CITY' );
        l_city_node       := dbms_xmldom.appendChild(l_add_node,dbms_xmldom.makeNode(l_city_element));
        l_city_text       := dbms_xmldom.createTextNode(l_domdoc, add_rec.city );
        l_city_tnode      := dbms_xmldom.appendChild(l_city_node,dbms_xmldom.makeNode(l_city_text));

        -- Each Address node will get a State node which contains the State as text
        l_state_element   := dbms_xmldom.createElement(l_domdoc, 'STATE' );
        l_state_node      := dbms_xmldom.appendChild(l_add_node,dbms_xmldom.makeNode(l_state_element));
        l_state_text      := dbms_xmldom.createTextNode(l_domdoc, add_rec.state );
        l_state_tnode     := dbms_xmldom.appendChild(l_state_node,dbms_xmldom.makeNode(l_state_text));

        -- Each Address node will get a Zip node which contains the Zip Code as text
        l_zip_element     := dbms_xmldom.createElement(l_domdoc, 'ZIP' );
        l_zip_node        := dbms_xmldom.appendChild(l_add_node,dbms_xmldom.makeNode(l_zip_element));
        l_zip_text        := dbms_xmldom.createTextNode(l_domdoc, add_rec.zip );
        l_zip_tnode       := dbms_xmldom.appendChild(l_zip_node,dbms_xmldom.makeNode(l_zip_text));
      END LOOP;

END LOOP;

   l_xmltype := dbms_xmldom.getXmlType(l_domdoc);
   dbms_xmldom.freeDocument(l_domdoc);

   dbms_output.put_line(l_xmltype.getClobVal);

END;

You can use the same concepts to create XML using DBMS_XMLDOM package. You can have nested groups or n-number of sub groups etc in the XML Document.

Leave your comments and feedback and also post any questions that you may have.