generating xml documnets by using plsql xml procedures.

sample xml procedure:
========================================================

DECLARE

V_DOC XMLDOM.DOMDocument;
V_MAIN_NODE XMLDOM.DOMNode;
V_ROOT_NODE XMLDOM.DOMNode;
V_NODE XMLDOM.DOMNode;
v_user_node xmldom.DOMNode;
v_user_root_node xmldom.DOMNode;
V_ELE XMLDOM.DOMElement;
V_TEXT XMLDOM.DOMText;

V_XML XMLTYPE;


BEGIN

--nex xml document generation
V_DOC:=XMLDOM.newDOMDocument;

--setting version number for generated document
xmldom.setVersion(v_doc,'1.0');

--making generated document as main node then will append all the nodes to main node
V_MAIN_NODE:=XMLDOM.makeNode(V_DOC);

--creating root element name for generated document
V_ELE:=XMLDOM.createElement(V_DOC,'SampleXmlFileGeneration');

--making generated element as root node for main node by appending it
v_user_root_node:=XMLDOM.appendChild(V_MAIN_NODE,xmldom.makeNode(v_ele));


FOR EMP_REC IN (SELECT empno,ename,sal,job,hiredate,deptno FROM SCOTT.emp
where deptno=10)
LOOP

--creating child element for root element
v_ele:=xmldom.createElement(v_doc,'EmployeeDetails');

--setting dynamic values for child node(EmployeeDetails)
xmldom.setAttribute(v_ele,'empno',emp_rec.empno);
xmldom.setAttribute(v_ele,'ename',emp_rec.ename);
xmldom.setAttribute(v_ele,'job',emp_rec.job);
xmldom.setAttribute(v_ele,'sal',emp_rec.sal);
xmldom.setAttribute(v_ele,'hiredate',emp_rec.hiredate);
xmldom.setAttribute(v_ele,'deptno',emp_rec.deptno);

--appending chld node to root node
v_user_node:=xmldom.appendChild(v_user_root_node,xmldom.makeNode(v_ele));


END LOOP;

V_XML:=DBMS_XMLDOM.getxmltype(V_DOC);
delete from app.temp_xml;
commit;
INSERT INTO APP.TEMP_XML (XML_DATA) VALUES(V_XML);
COMMIT;
END;


text element creating for xml :
====================================================

DECLARE

V_DOC XMLDOM.DOMDocument;
V_MAIN_NODE XMLDOM.DOMNode;
V_ROOT_NODE XMLDOM.DOMNode;
V_NODE XMLDOM.DOMNode;
v_user_node xmldom.DOMNode;
v_user_root_node xmldom.DOMNode;
V_ELE XMLDOM.DOMElement;
V_TEXT XMLDOM.DOMText;

V_XML XMLTYPE;


BEGIN

--nex xml document generation
V_DOC:=XMLDOM.newDOMDocument;

--setting version number for generated document
xmldom.setVersion(v_doc,'1.0');

--making generated document as main node then will append all the nodes to main node
V_MAIN_NODE:=XMLDOM.makeNode(V_DOC);

--creating root element name for generated document
V_ELE:=XMLDOM.createElement(V_DOC,'SampleXmlFileGeneration');

--making generated element as root node for main node by appending it
v_user_root_node:=XMLDOM.appendChild(V_MAIN_NODE,xmldom.makeNode(v_ele));


FOR EMP_REC IN (SELECT empno,ename,sal,job,hiredate,deptno FROM SCOTT.emp
where deptno=10)
LOOP

--creating child element for root element
v_ele:=xmldom.createElement(v_doc,'EmployeeDetails');

--setting dynamic values for child node(EmployeeDetails)
xmldom.setAttribute(v_ele,'empno',emp_rec.empno);
xmldom.setAttribute(v_ele,'ename',emp_rec.ename);
xmldom.setAttribute(v_ele,'job',emp_rec.job);
xmldom.setAttribute(v_ele,'sal',emp_rec.sal);
xmldom.setAttribute(v_ele,'hiredate',emp_rec.hiredate);
xmldom.setAttribute(v_ele,'deptno',emp_rec.deptno);

--appending chld node to root node
v_user_node:=xmldom.appendChild(v_user_root_node,xmldom.makeNode(v_ele));

--creating text node and dynamic element under the child node
v_ele:=xmldom.createElement(v_doc,'empno');
v_node:=xmldom.appendChild(v_user_node,xmldom.makeNode(v_ele));
v_text:=xmldom.createTextNode(v_doc,emp_rec.empno);
v_node:=xmldom.appendChild(v_node,xmldom.makeNode(v_text));

v_ele:=xmldom.createElement(v_doc,'ename');
v_node:=xmldom.appendChild(v_user_node,xmldom.makeNode(v_ele));
v_text:=xmldom.createTextNode(v_doc,emp_rec.ename);
v_node:=xmldom.appendChild(v_node,xmldom.makeNode(v_text));

v_ele:=xmldom.createElement(v_doc,'job');
v_node:=xmldom.appendChild(v_user_node,xmldom.makeNode(v_ele));
v_text:=xmldom.createTextNode(v_doc,emp_rec.job);
v_node:=xmldom.appendChild(v_node,xmldom.makeNode(v_text));


v_ele:=xmldom.createElement(v_doc,'sal');
v_node:=xmldom.appendChild(v_user_node,xmldom.makeNode(v_ele));
v_text:=xmldom.createTextNode(v_doc,emp_rec.sal);
v_node:=xmldom.appendChild(v_node,xmldom.makeNode(v_text));

v_ele:=xmldom.createElement(v_doc,'hiredate');
v_node:=xmldom.appendChild(v_user_node,xmldom.makeNode(v_ele));
v_text:=xmldom.createTextNode(v_doc,emp_rec.hiredate);
v_node:=xmldom.appendChild(v_node,xmldom.makeNode(v_text));

v_ele:=xmldom.createElement(v_doc,'deptno');
v_node:=xmldom.appendChild(v_user_node,xmldom.makeNode(v_ele));
v_text:=xmldom.createTextNode(v_doc,emp_rec.deptno);
v_node:=xmldom.appendChild(v_node,xmldom.makeNode(v_text));

END LOOP;

V_XML:=DBMS_XMLDOM.getxmltype(V_DOC);
delete from app.temp_xml;
commit;
INSERT INTO APP.TEMP_XML (XML_DATA) VALUES(V_XML);
COMMIT;
END;




Sample xml file generation Procedure :
========================================

DECLARE

V_DOC XMLDOM.DOMDocument;
V_MAIN_NODE XMLDOM.DOMNode;
V_ROOT_NODE XMLDOM.DOMNode;
V_NODE XMLDOM.DOMNode;
v_user_node xmldom.DOMNode;
v_user_root_node xmldom.DOMNode;
V_ELE XMLDOM.DOMElement;
V_TEXT XMLDOM.DOMText;

V_XML XMLTYPE;


cursor dept_cur is select deptno,dname,loc from scott.dept
where deptno=10;

dept_rec dept_cur%rowtype;

BEGIN

open dept_cur;
fetch dept_cur into dept_rec;
close dept_cur;

V_DOC:=XMLDOM.newDOMDocument;
xmldom.setVersion(v_doc,'1.0');
V_MAIN_NODE:=XMLDOM.makeNode(V_DOC);
V_ELE:=XMLDOM.createElement(V_DOC,'SampleXmlFileGeneration');
v_user_root_node:=XMLDOM.appendChild(V_MAIN_NODE,xmldom.makeNode(v_ele));

v_ele:=xmldom.createElement(v_doc,'DeptDetails');
xmldom.setAttribute(v_ele,'deptno',dept_rec.deptno);
xmldom.setAttribute(v_ele,'dname',dept_rec.dname);
xmldom.setAttribute(v_ele,'location',dept_rec.loc);
v_root_node:=xmldom.appendChild(v_user_root_node,xmldom.makeNode(v_ele));


FOR EMP_REC IN (SELECT empno,ename,sal,job,hiredate,deptno FROM SCOTT.emp
where deptno=dept_rec.deptno)
LOOP

v_ele:=xmldom.createElement(v_doc,'EmployeeDetails');
xmldom.setAttribute(v_ele,'empno',emp_rec.empno);
xmldom.setAttribute(v_ele,'ename',emp_rec.ename);
xmldom.setAttribute(v_ele,'job',emp_rec.job);
xmldom.setAttribute(v_ele,'sal',emp_rec.sal);
xmldom.setAttribute(v_ele,'hiredate',emp_rec.hiredate);
xmldom.setAttribute(v_ele,'deptno',emp_rec.deptno);
v_user_node:=xmldom.appendChild(v_root_node,xmldom.makeNode(v_ele));



/*v_ele:=xmldom.createElement(v_doc,'empno');
v_node:=xmldom.appendChild(v_user_node,xmldom.makeNode(v_ele));
v_text:=xmldom.createTextNode(v_doc,emp_rec.empno);
v_node:=xmldom.appendChild(v_node,xmldom.makeNode(v_text));

v_ele:=xmldom.createElement(v_doc,'ename');
v_node:=xmldom.appendChild(v_user_node,xmldom.makeNode(v_ele));
v_text:=xmldom.createTextNode(v_doc,emp_rec.ename);
v_node:=xmldom.appendChild(v_node,xmldom.makeNode(v_text));

v_ele:=xmldom.createElement(v_doc,'job');
v_node:=xmldom.appendChild(v_user_node,xmldom.makeNode(v_ele));
v_text:=xmldom.createTextNode(v_doc,emp_rec.job);
v_node:=xmldom.appendChild(v_node,xmldom.makeNode(v_text));


v_ele:=xmldom.createElement(v_doc,'sal');
v_node:=xmldom.appendChild(v_user_node,xmldom.makeNode(v_ele));
v_text:=xmldom.createTextNode(v_doc,emp_rec.sal);
v_node:=xmldom.appendChild(v_node,xmldom.makeNode(v_text));

v_ele:=xmldom.createElement(v_doc,'hiredate');
v_node:=xmldom.appendChild(v_user_node,xmldom.makeNode(v_ele));
v_text:=xmldom.createTextNode(v_doc,emp_rec.hiredate);
v_node:=xmldom.appendChild(v_node,xmldom.makeNode(v_text));

v_ele:=xmldom.createElement(v_doc,'deptno');
v_node:=xmldom.appendChild(v_user_node,xmldom.makeNode(v_ele));
v_text:=xmldom.createTextNode(v_doc,emp_rec.deptno);
v_node:=xmldom.appendChild(v_node,xmldom.makeNode(v_text)); */


END LOOP;

V_XML:=DBMS_XMLDOM.getxmltype(V_DOC);
delete from app.temp_xml;
commit;
INSERT INTO APP.TEMP_XML (XML_DATA) VALUES(V_XML);
COMMIT;
END;








xml sample procedure:
======================================================

DECLARE

V_DOC XMLDOM.DOMDocument;
V_MAIN_NODE XMLDOM.DOMNode;
V_ROOT_NODE XMLDOM.DOMNode;
V_NODE XMLDOM.DOMNode;
v_user_node xmldom.DOMNode;
V_ELE XMLDOM.DOMElement;
V_TEXT XMLDOM.DOMText;

V_XML XMLTYPE;

BEGIN

V_DOC:=XMLDOM.newDOMDocument;
xmldom.setVersion(v_doc,'1.0');
V_MAIN_NODE:=XMLDOM.makeNode(V_DOC);
V_ELE:=XMLDOM.createElement(V_DOC,'INFO');
V_ROOT_NODE:=XMLDOM.appendChild(V_MAIN_NODE,xmldom.makeNode(v_ele));

FOR EMP_REC IN (SELECT DEPTNO,DNAME,LOC FROM SCOTT.DEPT)
LOOP

v_ele:=xmldom.createElement(v_doc,'Details');
xmldom.setAttribute(v_ele,'Deptno',emp_rec.deptno);
xmldom.setAttribute(v_ele,'DeptName',emp_rec.dname);
xmldom.setAttribute(v_ele,'Location',emp_rec.loc);
v_user_node:=xmldom.appendChild(v_root_node,xmldom.makeNode(v_ele));



v_ele:=xmldom.createElement(v_doc,'Deptno');
v_node:=xmldom.appendChild(v_user_node,xmldom.makeNode(v_ele));

v_text:=xmldom.createTextNode(v_doc,emp_rec.deptno);
v_node:=xmldom.appendChild(v_node,xmldom.makeNode(v_text));

v_ele:=xmldom.createElement(v_doc,'DeptName');
v_node:=xmldom.appendChild(v_user_node,xmldom.makeNode(v_ele));
v_text:=xmldom.createTextNode(v_doc,emp_rec.dname);
v_node:=xmldom.appendChild(v_node,xmldom.makeNode(v_text));

v_ele:=xmldom.createElement(v_doc,'Location');
v_node:=xmldom.appendChild(v_user_node,xmldom.makeNode(v_ele));
v_text:=xmldom.createTextNode(v_doc,emp_rec.loc);
v_node:=xmldom.appendChild(v_node,xmldom.makeNode(v_text));



END LOOP;

V_XML:=DBMS_XMLDOM.getxmltype(V_DOC);
delete from app.temp_xml;
commit;
INSERT INTO APP.TEMP_XML (XML_DATA) VALUES(V_XML);
COMMIT;
END;


===================================================================

declare
doc xmldom.DOMDocument;
main_node xmldom.DOMNode;
root_node xmldom.DOMNode;
user_node xmldom.DOMNode;
node xmldom.DOMNode;
ele xmldom.DOMElement;
text xmldom.DOMText;
xml_data xmltype;

cursor dept_cur is select a.deptno,a.dname,a.loc,
(select count(1) from scott.emp b where deptno=a.deptno) emp_count from scott.dept a;

begin

doc:=xmldom.newDOMDocument;
xmldom.setVersion(doc,'1.0');
main_node:=xmldom.makeNode(doc);
ele:=xmldom.createElement(doc,'DeptDetails');
main_node:=xmldom.appendChild(main_node,xmldom.makeNode(ele));

for dept_rec in dept_cur loop

ele:=xmldom.createElement(doc,'DeptInfo');
xmldom.setAttribute(ele,'deptno',dept_rec.deptno);
xmldom.setAttribute(ele,'dname',dept_rec.dname);
xmldom.setAttribute(ele,'location',dept_rec.loc);
xmldom.setAttribute(ele,'Employee_count',dept_rec.emp_count);

root_node:=xmldom.appendChild(main_node,xmldom.makeNode(ele));

for emp_rec in (select * from scott.emp where deptno=dept_rec.deptno) loop

ele:=xmldom.createElement(doc,'EmployeeInfo');
xmldom.setAttribute(ele,'empno',emp_rec.empno);
xmldom.setAttribute(ele,'ename',emp_rec.ename);
xmldom.setAttribute(ele,'job',emp_rec.job);
xmldom.setAttribute(ele,'sal',emp_rec.sal);
xmldom.setAttribute(ele,'comm',nvl(emp_rec.comm,0));
xmldom.setAttribute(ele,'hiredate',emp_rec.hiredate);
xmldom.setAttribute(ele,'deptno',emp_rec.deptno);
node:=xmldom.appendChild(root_node,xmldom.makeNode(ele));


/* ele:=xmldom.createElement(doc,'deptno');
user_node:=xmldom.appendChild(root_node,xmldom.makeNode(ele));
text:=xmldom.createTextNode(doc,dept_rec.deptno);
node:=xmldom.appendChild(user_node,xmldom.makeNode(text));

ele:=xmldom.createElement(doc,'dname');
user_node:=xmldom.appendChild(root_node,xmldom.makeNode(ele));
text:=xmldom.createTextNode(doc,dept_rec.dname);
node:=xmldom.appendChild(user_node,xmldom.makeNode(text));

ele:=xmldom.createElement(doc,'location');
user_node:=xmldom.appendChild(root_node,xmldom.makeNode(ele));
text:=xmldom.createTextNode(doc,dept_rec.loc);
node:=xmldom.appendChild(user_node,xmldom.makeNode(text));
*/
end loop;
end loop;
xml_data:=xmldom.getxmltype(doc);

delete from app.temp_xml; commit;
insert into app.temp_xml(xml_data) values (xml_data);
commit;
end;