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:

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:

[xml]
<SUPPLIER_DETAILS>
<SUPPLIER>
<SUPPLIER_NUMBER> </SUPPLIER_NUMBER>
<SUPPLIER_NAME> </SUPPLIER_NAME>
</SUPPLIER>
<SUPPLIER>
<SUPPLIER_NUMBER> </SUPPLIER_NUMBER>
<SUPPLIER_NAME> </SUPPLIER_NAME>
</SUPPLIER>
</SUPPLIER_DETAILS>
[/xml]

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.

[sql]
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;
[/sql]

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.

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

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.

[sql]
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;
[/sql]

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.

Tagged

Leave a Reply

Your email address will not be published. Required fields are marked *