1  CREATE OR REPLACE PROCEDURE getAcc(p_user IN VARCHAR2)
 2  AUTHID CURRENT_USER
 3  IS
 4    doc       xmldom.DOMDocument;
 5    main_node xmldom.DOMNode;
 6    root_node xmldom.DOMNode;
 7    user_node xmldom.DOMNode;
 8    item_node xmldom.DOMNode;
 9    root_elmt xmldom.DOMElement;
10    item_elmt xmldom.DOMElement;
11    item_text xmldom.DOMText;
12    tmp_lob   CLOB;
13    l_usr   VARCHAR2(100) := 'cn='||p_user;
14    CURSOR get_users(p_user VARCHAR2) IS
15      SELECT wrkid
16           , ename
17           , email
18           , appln
19           , rownum
20        FROM usertbl
21       WHERE wrkid = l_usr;
22  BEGIN
23    -- get document
24    doc := xmldom.newDOMDocument;
25    -- create root element
26    main_node := xmldom.makeNode(doc);
27    root_elmt := xmldom.createElement(doc , 'EMPSET');
28    root_node := xmldom.appendChild(main_node , xmldom.makeNode(root_elmt));
29    FOR get_users_rec IN get_users(10) LOOP
30      -- create user element with rownum as attribute
31      item_elmt := xmldom.createElement(doc , 'EMP');
32      xmldom.setAttribute(
33          item_elmt
34        , 'num'
35        , get_users_rec.rownum
36      );
37      user_node := xmldom.appendChild(
38          root_node
39        , xmldom.makeNode(item_elmt)
40      );
41      -- create user element: EMP_NO
42      item_elmt := xmldom.createElement(doc , 'EMP_NO');
43      item_node := xmldom.appendChild(user_node, xmldom.makeNode(item_elmt));
44      item_text := xmldom.createTextNode(doc, get_users_rec.wrkid);
45      item_node := xmldom.appendChild(item_node, xmldom.makeNode(item_text)
46      );
47      -- create user element: NAME
48      item_elmt := xmldom.createElement(doc , 'NAME');
49      item_node := xmldom.appendChild(user_node, xmldom.makeNode(item_elmt));
50      item_text := xmldom.createTextNode(doc ,get_users_rec.ename);
51      item_node := xmldom.appendChild(item_node, xmldom.makeNode(item_text));
52      -- create user element: EMAIL
53      item_elmt := xmldom.createElement(doc , 'EMAIL');
54      item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));
55      item_text := xmldom.createTextNode(doc , get_users_rec.email);
56      item_node := xmldom.appendChild(item_node, xmldom.makeNode(item_text));
57       -- create user element: APPLICATION AND ACCESS RIGHTS
58          item_elmt := xmldom.createElement(doc , 'ACCESS');
59          item_node := xmldom.appendChild(user_node , xmldom.makeNode(item_elmt));
60          item_text := xmldom.createTextNode( doc , get_users_rec.appln);
61          item_node := xmldom.appendChild(item_node, xmldom.makeNode(item_text));
62    END LOOP;
63     -- creating temporary CLOB
64         DBMS_LOB.createtemporary(tmp_lob,true);
65     -- write document to CLOB
66         xmldom.writetoCLOB(doc, tmp_lob);
67         dbms_output.put_line(tmp_lob);
68     -- free resources
69         xmldom.freeDocument(doc);
70  --    return tmp_lob;
71* END getAcc;
72  /

rocedure created.