Oracle XML support Cheat Sheet (DRAFT) by taotao

--avoid complex syntax of using XML function to create xml doc
-- create object view:simplify sql
 CREATE OR REPLACE VIEW VIEW_NAME OF OBJECT_TYPE WITH OBJECT IDENTIFIER (COLUMN(s)) as SELECT
--using dbms_xmlgen to generate file and save
--directory: pre-defined & ALL CAPS
out_file  utl_file.file_type;
out_file := utl_file.fopen ('MY_XML_FILES','file.xml','W');

QueryContext        dbms_xmlgen.ctxhandle;
QueryContext := dbms_xmlgen.newcontext ('sql_query');
            
dbms_xmlgen.setrowsettag (QueryContext, 'xxx');
dbms_xmlgen.setrowtag (QueryContext, 'xxx');
    
my_clob := dbms_xmlgen.getxml(QueryContext);

--write that XML to our external file
utl_file.put (out_file, my_clob);

utl_file.fclose (out_file);
dbms_xmlgen.closecontext(QueryContext);

VARIABLE v_job VARCHAR2(10);
EXEC :v_job := 'CLERK';

SELECT xt.*
FROM   xml_tab x,
       XMLTABLE('/employees/employee[job=$job]'
         PASSING x.xml_data, :v_job AS "job"
         COLUMNS 
           empno     VARCHAR2(4)  PATH 'empno',
           ename     VARCHAR2(10) PATH 'ename',
           job       VARCHAR2(9)  PATH 'job',
           hiredate  VARCHAR2(11) PATH 'hiredate,
           id     VARCHAR2(10) PATH '@id','
         ) xt;
-- :v_job AS "job" 
--The variable must be aliases using AS and double quoted to make sure the name and case matches that of the variable in the XPath expression.

--
SELECT x.xml_data.getClobVal()
FROM   xml_tab x;

--nested XML_table
SELECT xt.*
FROM   xml_tab x,
    XMLTABLE('/departments/department'
    PASSING x.xml_data
     COLUMNS 
         deptno     VARCHAR2(4)  PATH 'dept_no',
         XMLTABLE('/employees/employee'
         PASSING dd.employees
         COLUMNS
             empno varchar2(4) PATH 'emp_no',
             ename  varchar2(4) PATH 'emp_name'
         )
 ) xt

--form xmltype in variable
PASSING xmltype(v_varchar2)
PASSING v_xml