Generating XML from PL/SQL

The several ways generate the XML in Oracle PL/SQL.

1) Using the PL/SQL XMLDOM api dynamically traverse or construct the nodes.

PROCEDURE sp_build_xml (

pi_s_jobid IN VARCHAR2,
pi_n_fulfill_req_id IN NUMBER,
pi_template_obj IN ff_template_data_obj, -- These object is
po_n_return_code OUT NUMBER

)
IS
doc DBMS_XMLDOM.domdocument;
main_node DBMS_XMLDOM.domnode;
root_node DBMS_XMLDOM.domnode;
datasets_node DBMS_XMLDOM.domnode;
dataset_node DBMS_XMLDOM.domnode;
item_node DBMS_XMLDOM.domnode;
elements_node DBMS_XMLDOM.domnode;
element_node DBMS_XMLDOM.domnode;
root_elmt DBMS_XMLDOM.domelement;
item_elmt DBMS_XMLDOM.domelement;
item_text DBMS_XMLDOM.domtext;
xmltypevar XMLTYPE;
xml_clob CLOB := NULL;
dataset_info ff_dataset_data_obj;
element_t ff_element_t;
coll_datasets ff_dataset_data_t;

BEGIN
coll_datasets := pi_template_obj.dataset_data;
doc := DBMS_XMLDOM.newdomdocument;
main_node := DBMS_XMLDOM.makenode (doc);
root_elmt := DBMS_XMLDOM.createelement (doc, 'DATASETS');
datasets_node := DBMS_XMLDOM.appendchild (main_node,
DBMS_XMLDOM.makenode (root_elmt));
FOR j IN coll_datasets.FIRST .. coll_datasets.LAST
LOOP
dataset_info := coll_datasets (j);
root_elmt := DBMS_XMLDOM.createelement (doc, 'DATASET');
root_node :=DBMS_XMLDOM.appendchild (datasets_node,DBMS_XMLDOM.makenode (root_elmt));
item_elmt := DBMS_XMLDOM.createelement (doc, 'DSNAME');
item_node :=DBMS_XMLDOM.appendchild (root_node,DBMS_XMLDOM.makenode (item_elmt));
item_text :=DBMS_XMLDOM.createtextnode (doc, dataset_info.dataset_name);
item_node :=DBMS_XMLDOM.appendchild (item_node,DBMS_XMLDOM.makenode (item_text));
element_t := dataset_info.element_data;

FOR i IN element_t.FIRST .. element_t.LAST
LOOP
item_elmt := DBMS_XMLDOM.createelement (doc,REPLACE (element_t (i).element_name, ' '));
item_node := DBMS_XMLDOM.appendchild (root_node,DBMS_XMLDOM.makenode (item_elmt));
item_text :=DBMS_XMLDOM.createtextnode (doc, element_t (i).element_value);
item_node :=DBMS_XMLDOM.appendchild (item_node,DBMS_XMLDOM.makenode (item_text));

END LOOP; --INNER LOOP
END LOOP;--OUTER LOOP

DBMS_XMLDOM.setversion (doc, '1.0');
DBMS_LOB.createtemporary (xml_clob, TRUE);
DBMS_XMLDOM.writetoclob (doc, xml_clob);
xmltypevar := XMLTYPE (xml_clob);

DBMS_XMLDOM.freedocument (doc);

EXCEPTION
WHEN DBMS_XMLDOM.index_size_err
THEN
raise_application_error (-20120, 'Index Size error');

WHEN DBMS_XMLDOM.domstring_size_err
THEN
raise_application_error (-20120, 'String Size error');

WHEN DBMS_XMLDOM.hierarchy_request_err
THEN
raise_application_error (-20120, 'Hierarchy request error');

WHEN DBMS_XMLDOM.wrong_document_err
THEN
raise_application_error (-20120, 'Wrong doc error');

WHEN DBMS_XMLDOM.invalid_character_err
THEN
raise_application_error (-20120, 'Invalid Char error');

WHEN DBMS_XMLDOM.no_data_allowed_err
THEN
raise_application_error (-20120, 'Nod data allowed error');

WHEN DBMS_XMLDOM.no_modification_allowed_err
THEN
raise_application_error (-20120, 'No mod allowed error');

WHEN DBMS_XMLDOM.not_found_err
THEN
raise_application_error (-20120, 'Not found error');

WHEN DBMS_XMLDOM.not_supported_err
THEN
raise_application_error (-20120, 'Not supported error');

WHEN DBMS_XMLDOM.inuse_attribute_err
THEN
raise_application_error (-20120, 'In use attr error');

END;

2) Writing with simple clob varchar string value concatination. This simple procedure gives the idea how we can generate the xml.

3) Directly from query selected results will generate the XML with the XMLTYPE variable