------------------------------------------------------------------------------
--
-- Akadia Create XML docs
--
-- (c) Akadia AG 2002
--
-- Requires Oracle 8i or 9i
-- For larger amounts of nodes use 8.1.7.3 with bug fix 2104071 or 9.2
--
-- Create XML docs using XMLDOM package.
------------------------------------------------------------------------------
CONNECT sys/manager@sid
--
GRANT javauserpriv to scott;
GRANT javasyspriv to scott;
GRANT EXECUTE ON xmldom TO scott;
--
CREATE SYNONYM scott.xmldom FOR SYS.xmldom;
--
CONNECT scott/tiger@sid
--
DECLARE
  doc xmldom.DOMDocument;
  main_node xmldom.DOMNode;
  root_node xmldom.DOMNode;
  user_node xmldom.DOMNode;
  item_node xmldom.DOMNode;
  root_elmt xmldom.DOMElement;
  item_elmt xmldom.DOMElement;
  item_text xmldom.DOMText;
  CURSOR get_users(p_deptno NUMBER) IS
    SELECT empno
         , ename
         , deptno
         , rownum
      FROM emp
     WHERE deptno = p_deptno;
BEGIN
  -- get document
  doc := xmldom.newDOMDocument;
  -- create root element
  main_node := xmldom.makeNode(doc);
  root_elmt := xmldom.createElement(
      doc
    , 'EMPSET'
  );
  -- xmldom.setAttribute(root_elmt, 'xmlns', 'https://www.akadia.com/xml/soug/xmldom');
  -- xmldom.setAttribute(root_elmt, 'xmlns:xsi', 'http://www.w3.org/2001/XMLSchema-instance');
  -- xmldom.setAttribute(root_elmt, 'xsi:schemaLocation', 'https://www.akadia.com/xml/soug/xmldom/emp.xsd');
  root_node := xmldom.appendChild(
      main_node
    , xmldom.makeNode(root_elmt)
  );
  FOR get_users_rec IN get_users(10) LOOP
    -- create user element with rownum as attribute
    item_elmt := xmldom.createElement(
        doc
      , 'EMP'
    );
    xmldom.setAttribute(
        item_elmt
      , 'num'
      , get_users_rec.rownum
    );
    user_node := xmldom.appendChild(
        root_node
      , xmldom.makeNode(item_elmt)
    );
    -- create user element: EMP_NO
    item_elmt := xmldom.createElement(
        doc
      , 'EMP_NO'
    );
    item_node := xmldom.appendChild(
        user_node
      , xmldom.makeNode(item_elmt)
    );
    item_text := xmldom.createTextNode(
        doc
      , get_users_rec.empno
    );
    item_node := xmldom.appendChild(
        item_node
      , xmldom.makeNode(item_text)
    );
    -- create user element: NAME
    item_elmt := xmldom.createElement(
        doc
      , 'NAME'
    );
    item_node := xmldom.appendChild(
        user_node
      , xmldom.makeNode(item_elmt)
    );
    item_text := xmldom.createTextNode(
        doc
      , get_users_rec.ename
    );
    item_node := xmldom.appendChild(
        item_node
      , xmldom.makeNode(item_text)
    );
    -- create user element: DEPT_NO
    item_elmt := xmldom.createElement(
        doc
      , 'DEPT_NO');
    item_node := xmldom.appendChild(
        user_node
      , xmldom.makeNode(item_elmt)
    );
    item_text := xmldom.createTextNode(
        doc
      , get_users_rec.deptno
    );
    item_node := xmldom.appendChild(
        item_node
      , xmldom.makeNode(item_text)
    );
  END LOOP;
  -- write document to file using default character set from database
  xmldom.writeToFile(
      doc
    , '/tmp/xml/docSample.xml'
  );
  -- free resources
  xmldom.freeDocument(doc);
END;
/


-- deal with exceptions
EXCEPTION

WHEN xmldom.INDEX_SIZE_ERR THEN
  raise_application_error(-20120, 'Index Size error');
WHEN xmldom.DOMSTRING_SIZE_ERR THEN
  raise_application_error(-20120, 'String Size error');
WHEN xmldom.HIERARCHY_REQUEST_ERR THEN
  raise_application_error(-20120, 'Hierarchy request error');
WHEN xmldom.WRONG_DOCUMENT_ERR THEN
  raise_application_error(-20120, 'Wrong doc error');
WHEN xmldom.INVALID_CHARACTER_ERR THEN
  raise_application_error(-20120, 'Invalid Char error');
WHEN xmldom.NO_DATA_ALLOWED_ERR THEN
  raise_application_error(-20120, 'Nod data allowed error');
WHEN xmldom.NO_MODIFICATION_ALLOWED_ERR THEN
  raise_application_error(-20120, 'No mod allowed error');
WHEN xmldom.NOT_FOUND_ERR THEN
  raise_application_error(-20120, 'Not found error');
WHEN xmldom.NOT_SUPPORTED_ERR THEN
  raise_application_error(-20120, 'Not supported error');
WHEN xmldom.INUSE_ATTRIBUTE_ERR THEN
  raise_application_error(-20120, 'In use attr error');
END;
/