------------------------------------------------------------------------------
--
-- Akadia Create XML docs
--
-- (c) Akadia AG 2002
--
-- Requires Oracle 8i or 9i
--
-- Create XML docs using XSU (XML-SQL Utility).
--
------------------------------------------------------------------------------
CONNECT scott/tiger@sid
--
SET SERVEROUTPUT ON
--
-- Simple procedure to support printing out a CLOB to screen
CREATE OR REPLACE PROCEDURE printClobOut(result IN OUT NOCOPY CLOB) IS
  xmlstr VARCHAR2(32767);
  line VARCHAR2(2000);
BEGIN
  xmlstr := dbms_lob.substr(result,32767);
  LOOP
    EXIT WHEN xmlstr IS NULL;
    line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
    dbms_output.put_line(line);
    xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
  END LOOP;
END;
/
SHOW ERRORS;
--
-- Simple example with binding variable
DECLARE
  queryCtx dbms_xmlquery.ctxType;
  result CLOB;
BEGIN
  -- set up the query context
  queryCtx := dbms_xmlquery.newContext(
    'SELECT empno "EMP_NO"
          , ename "NAME"
          , deptno "DEPT_NO"
       FROM emp
      WHERE deptno = :DEPTNO'
  );
  -- set row tag name and row set tag name
  dbms_xmlquery.setRowTag(
      queryCtx
    , 'EMP'
  );
  dbms_xmlquery.setRowSetTag(
      queryCtx
    , 'EMPSET'
  );
  -- bind variables
  dbms_xmlquery.setBindValue(
      queryCtx
    , 'DEPTNO'
    , 10
  );
  -- run query and print out result
  result := dbms_xmlquery.getXml(queryCtx);
  printClobOut(result);
  -- free resources
  dbms_xmlquery.closeContext(queryCtx);
END;
/