oracle dbms_xml

DECLARE
         Ctx    DBMS_XMLGEN.ctxHandle;   -- Var's to convert SQL output to XML
         xml    clob;
         emp_no NUMBER := 7369;

         xmlc   varchar2(4000);          -- Var's required to convert lob to varchar
         off    integer := 1;
         len    integer := 4000;
 BEGIN
         Ctx := DBMS_XMLGEN.newContext('SELECT * FROM emp WHERE empno = '||emp_no);
         DBMS_XMLGen.setRowsetTag(Ctx, 'EMP_TABLE');
         DBMS_XMLGen.setRowTag(Ctx, 'EMP_ROW');
         --Dbms_Xmlgen.closeContext(Ctx);
         xml := DBMS_XMLGEN.getXML(Ctx);
         DBMS_LOB.READ(xml, len, off, xmlc);   -- Display first part on screen
         DBMS_OUTPUT.PUT_LINE(xmlc);
 END;
 
  select * FROM EMP
 
 
  create table XMLTable (doc_id number, xml_data XMLType);

 insert into XMLTable values (1,
         XMLType('<FAQ-LIST>
            <QUESTION>
                 <QUERY>Question 1</QUERY>
                 <RESPONSE>Answer goes here.</RESPONSE>
            </QUESTION>
         </FAQ-LIST>'));

 select extractValue(xml_data, '/FAQ-LIST/QUESTION/RESPONSE')  -- XPath expression
 from   XMLTable
 where  existsNode(xml_data, '/FAQ-LIST/QUESTION[QUERY="Question 1"]') = 1;
 
 
  CREATE type address_t AS OBJECT
(
   street VARCHAR2(20),
   state VARCHAR2(20),
   city VARCHAR2(20),
   zip   VARCHAR2(20)
);
CREATE TABLE employee
(
  empno NUMBER,
  ename VARCHAR2(200),
  address address_t
);
insert into employee values (100,'John',
      address_t('100, Main Street','Jacksonville','FL','32607'));
insert into employee values (200,'Jack',
      address_t('200 Front Road','San Francisco','CA','94011'));
     
declare
  ctx dbms_xmlgen.ctxhandle;
  xmlc   varchar2(4000);
  len    integer := 4000;
  result clob;
begin
  dbms_output.enable(500000);
   -- create a new context with the SQL query
   ctx := dbms_xmlgen.newContext('select * from employee');

   -- generate the CLOB as a result.
   result := dbms_xmlgen.getXML(ctx);

  -- print out the result of the CLOB
     -- Display first part on screen
 
    printClobOut(result); -- see the lob manual for examples on printing..
  -- close the context
  dbms_xmlgen.closeContext(ctx);
end