XMLDOM in pl/sql

This example can pull data from some AP tables such as ap_checks and ap_invoices. It can give details about invoices being paid by a particular check.

set serveroutput on size 1000000
DECLARE
 doc             xmldom.DOMDocument;

 inv_node        xmldom.DOMNode;
 inv_hdr_node    xmldom.DOMNode;
 inv_lines_node  xmldom.DOMNode;
 user_node       xmldom.DOMNode;
 chld_node       xmldom.DOMNode;
 item_node       xmldom.DOMNode;

 inv_hdr_elmt    xmldom.DOMElement;
 item_elmt       xmldom.DOMElement;
 item_text       xmldom.DOMText;
--
-- This function will create a node
--
FUNCTION create_node
( p_doc       xmldom.DOMDocument
, p_node      xmldom.DOMNode
, p_attr_nm   VARCHAR2
, p_attr_desc VARCHAR2 := NULL
, p_attr_val  VARCHAR2 := NULL
) RETURN      xmlDom.DOMNode IS
--
 item_elmt xmldom.DOMElement;
 user_node xmldom.DOMNode;
BEGIN
   item_elmt := xmldom.createElement ( p_doc , p_attr_nm);
   --
   IF p_attr_desc IS NOT NULL
   THEN
     xmldom.setAttribute ( item_elmt , p_attr_desc , p_attr_val);
   END IF;
   --
   user_node := xmldom.appendChild( p_node , xmldom.makeNode(item_elmt));
   RETURN user_node;
END create_node;
--
-- This function will add text node to a node
--
FUNCTION add_text_node
( p_doc         xmldom.DOMDocument
, p_parent_node xmldom.DOMNode
, p_node_name   VARCHAR2
, p_node_data   VARCHAR2
) RETURN xmldom.DOMNode IS
--
 item_elmt   xmldom.DOMElement;
 item_node   xmldom.DOMNode;
 item_text   xmldom.DOMText;
BEGIN
 item_elmt := xmldom.createElement ( p_doc         , p_node_name);
 item_node := xmldom.appendChild   ( p_parent_node , xmldom.makeNode(item_elmt));
 item_text := xmldom.createTextNode( p_doc         , nvl(p_node_data,'No Data'));
 item_node := xmldom.appendChild   ( item_node     , xmldom.makeNode(item_text));
 RETURN item_node;
END add_text_node;
--
-- For each logical object write a function that can query data and append
-- the data as xml child node
--
PROCEDURE  ap_invoices
( p_doc     IN OUT xmldom.DOMDocument
, p_node    IN OUT xmldom.DOMNode
, p_context IN     VARCHAR2 DEFAULT NULL
, p_value   IN     VARCHAR2 DEFAULT NULL
) IS
l_temp_node xmldom.DOMNode;
item_node   xmldom.DOMNode;
user_node   xmldom.DOMNode;
data_node   xmldom.DOMNode;
chld_node   xmldom.DOMNode;
--
l_sql       VARCHAR2(9999);
l_whr       VARCHAR2(9999);
TYPE t_cursor_type IS REF CURSOR;
c_data_rec   t_cursor_type;
CURSOR c_example_rec IS
    SELECT ai.invoice_id
         , ai.invoice_num
         , ai.invoice_amount
         , ai.description
      FROM ap_invoice_payments_all aip
         , ap_invoices_all          ai
     WHERE aip.invoice_id = ai.invoice_id
    ;

l_data_rec c_example_rec % ROWTYPE;
--
BEGIN
 l_sql := '
    SELECT ai.invoice_id
         , ai.invoice_num Inv_Num
         , ai.invoice_amount amt
         , ai.description
      FROM ap_invoice_payments_all aip
         , ap_invoices_all          ai
     WHERE aip.invoice_id = ai.invoice_id
       AND ';

 IF p_value IS NULL THEN return; END IF;

 IF p_context = 'CHECK_ID'
 THEN
    l_whr := 'check_id = ' || p_value  ;
 ELSE -- default condition
   l_whr := 'creation_date > SYSDATE - 2';
 END IF;

 dbms_output.put_line ('Where Condition for ap_invoices:' || l_whr);

 OPEN c_data_rec FOR l_sql || l_whr;
 LOOP
   FETCH c_data_rec into l_data_rec;
   EXIT WHEN c_data_rec % NOTFOUND;
   data_node := create_node( p_doc
                           , p_node
                           , 'invoiceRecord'
                           , 'invoiceId'
                           , l_data_rec.invoice_id
                           ) ;
   user_node := create_node(p_doc,data_node,'invoiceDetails');
   item_node := add_text_node(p_doc,user_node,'invoiceAmount' ,l_data_rec.invoice_amount);
   item_node := add_text_node(p_doc,user_node,'invoiceNum'    ,l_data_rec.invoice_num);
   item_node := add_text_node(p_doc,user_node,'description'   ,l_data_rec.description);
   --
 END LOOP;
 CLOSE c_data_rec;
END;
--
-- Dump data from ap checks table. In this example i have not used org view.
-- org views too could be used.
-- 
PROCEDURE  ap_checks_all
( p_doc     IN OUT xmldom.DOMDocument
, p_node    IN OUT xmldom.DOMNode
, p_context IN     VARCHAR2 DEFAULT NULL
, p_value   IN     VARCHAR2 DEFAULT NULL
) IS
l_temp_node xmldom.DOMNode;
item_node   xmldom.DOMNode;
user_node   xmldom.DOMNode;
data_node   xmldom.DOMNode;
chld_node   xmldom.DOMNode;
--
l_sql       VARCHAR2(9999);
l_whr       VARCHAR2(9999);
--
--
TYPE t_cursor_type IS REF CURSOR;
c_data_rec   t_cursor_type;
CURSOR c_check_rec IS
 SELECT check_id
      , check_number
      , check_date
      , amount
   FROM ap_checks_all
  WHERE 1 = 1;
l_data_rec c_check_rec % ROWTYPE;
--
BEGIN
 l_sql := '
    SELECT check_id
         , check_number
         , check_date
         , amount
      FROM ap_checks_all
     WHERE 1 = 1 and ';

 IF p_value IS NULL THEN return; END IF;

 -- 
 -- Make sure that query is efficient.
 -- 
 IF p_context = 'CHECKRUN_NAME'
 THEN
    l_whr := 'checkrun_name = ''' || p_value || '''' ;
 --
 -- Add some more conditions
 --
 ELSE -- default condition
   l_whr := 'creation_date > SYSDATE - 2';
 END IF;

 dbms_output.put_line ('Where Condition:' || l_whr);

 OPEN c_data_rec FOR l_sql || l_whr;
 LOOP
   FETCH c_data_rec into l_data_rec;
   EXIT WHEN c_data_rec % NOTFOUND;
   data_node := create_node( p_doc
                           , p_node
                           , 'checkRecord'
                           , 'checkId'
                           , l_data_rec.check_id
                           ) ;
   user_node := create_node(p_doc,data_node,'checkDetails');
   item_node := add_text_node(p_doc,user_node,'checkNumber',l_data_rec.check_number);
   item_node := add_text_node(p_doc,user_node,'checkDate'  ,l_data_rec.check_date);
   item_node := add_text_node(p_doc,user_node,'amount'     ,l_data_rec.amount);
   --
   -- Data from child table can be attached like this. ap_invoices can have it's own
   -- children and in this way you can get complete heirarchical data
   --
   chld_node := create_node(p_doc,data_node,'invoices');
   ap_invoices(p_doc, chld_node, 'CHECK_ID', l_data_rec.check_id);
   --
 END LOOP;
 CLOSE c_data_rec;
END;
--
--
BEGIN
 doc := xmldom.newDOMDocument;
 inv_node := xmldom.makeNode(doc);

 inv_hdr_node := create_node( doc
                            , inv_node
                            , 'myTestResults'
                            ) ;
 --
 chld_node := create_node( doc
                         , inv_hdr_node
                         ,'TestCaseGroup'
                         , 'Description'
                         , 'paymentBatch'
                         ) ;
 ap_checks_all( doc
              , chld_node
              , 'CHECKRUN_NAME'
              , 'SM CHECK 23-FEB-2009'
              ) ;

 xmldom.writeToFile
 ( doc
 , ''
 );

 xmldom.freeDocument(doc);
 --
 --
end;
/