Hello All,

I need to create a procedure on oracle that retrives data in xml format (using xmlgen) and returns it to a .NET application.

I am not sure if the following code would help me get this.

Code:
CREATE OR REPLACE PROCEDURE "XMLTEST1" (p_output OUT varchar2) IS

ctx    dbms_xmlgen.ctxHandle;
xml    CLOB;
emp_no NUMBER := 7369;
xmlc VARCHAR2(4000); -- required to convert LOB to VARCHAR2
off1  PLS_INTEGER := 1;
len  PLS_INTEGER := 4000;

BEGIN
    ctx := dbms_xmlgen.newContext('SELECT * FROM scott.emp WHERE empno = '|| emp_no);
    -- Replace the default ROWSET tag with my own  
     dbms_xmlgen.SetRowSetTag(ctx, 'DocumentLevel');

     -- Remove the default ROW level tag with my own
     -- If I passed in text instead of NULL I would just change the element name
     -- I can leave it off altogther and get the default, canonical format  
     dbms_xmlgen.SetRowTag(ctx, NULL);
  xml := dbms_xmlgen.getXML(ctx);
  dbms_xmlgen.closeContext(ctx);
  dbms_lob.read(xml, len, off1, p_output); 

      -- Display null tags for empty columns 
     --dbms_xmlgen.SetNullHandling(ctx, dbms_xmlgen.EMPTY_TAG);
  
      -- Create an XML document out of the ref cursor 
      --p_output := dbms_xmlgen.getXMLType( ctx );

END;
/
Can anyone help me with this..or provide me with any link?

thanks
Sam