Oracle Xml Generate node null
My Procedures
SELECT dbms_xmlgen.getxml('select * from '||i_tablename) into xml_data from dual;
doc := DBMS_XMLDOM.NewDOMDocument(xml_data);
DBMS_XMLDOM.WRITETOFILE(doc, 'XML_DIR/'||v_tablename);
My Table
ID Name SAL HIREDATE DEPTNO E_MAIL
1 xyz 23 01-JAN-00 2 bla@bla.com
2 - - - 23 - - - - - - - - - - asd@blabla.com
xml output
<?xml version="1.0" ?>
<ROWSET>
<ROW>
<ID>1</ID>
<NAME>xyz</NAME>
<SAL>23</SAL>
<HIREDATE>01-JAN-00</HIREDATE>
<DEPTNO>2</DEPTNO>
<E_MAIL>bla@bla.com</E_MAIL>
</ROW>
<ROW>
<ID>1开发者_高级运维</ID>
<SAL>23</SAL>
<E_MAIL>bla@bla.com</E_MAIL>
</ROW>
</ROWSET>
I want to do it
<?xml version="1.0" ?>
<ROWSET>
<ROW>
<ID>1</ID>
<NAME>xyz</NAME>
<SAL>23</SAL>
<HIREDATE>01-JAN-00</HIREDATE>
<DEPTNO>2</DEPTNO>
<E_MAIL>bla@bla.com</E_MAIL>
<ROW>
<ID>2</ID>
<NAME></NAME>
<SAL>23</SAL>
<HIREDATE></HIREDATE>
<DEPTNO></DEPTNO>
<E_MAIL>asd@blabla.com</E_MAIL>
</ROW>
</ROWSET>
We can use DBMS_XMLGEN.SETNULLHANDLING()
to control the way the XQuery handles NULLs. Setting it to 2
generates an empty tag. Unfortunately, manipulating the format of the resultset requires us to specify a context and so leads to a slightly more verbose syntax.
SQL> set long 5000
SQL> var c clob
SQL>
SQL> declare
2 ctx dbms_xmlgen.ctxhandle;
3 begin
4 ctx := dbms_xmlgen.newcontext('select * from emp where deptno = 10');
5 -- set EMPTY_TAG flag
6 dbms_xmlgen.SETNULLHANDLING (ctx, 2);
7 :c := dbms_xmlgen.getxml(ctx);
8 end;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL> print c
C
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPNO>7782</EMPNO>
<ENAME>BOEHMER</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>09-JUN-81</HIREDATE>
<SAL>2450</SAL>
<COMM/>
<DEPTNO>10</DEPTNO>
<LEAVE_DATE/>
</ROW>
<ROW>
<EMPNO>7839</EMPNO>
<ENAME>SCHNEIDER</ENAME>
<JOB>PRESIDENT</JOB>
<MGR/>
<HIREDATE>17-NOV-81</HIREDATE>
<SAL>5000</SAL>
<COMM/>
<DEPTNO>10</DEPTNO>
<LEAVE_DATE/>
</ROW>
<ROW>
<EMPNO>8070</EMPNO>
<ENAME>DUGGAN</ENAME>
<JOB>SALES</JOB>
<MGR>7782</MGR>
<HIREDATE>19-NOV-09</HIREDATE>
<SAL>2500</SAL>
<COMM/>
<DEPTNO>10</DEPTNO>
<LEAVE_DATE/>
</ROW>
</ROWSET>
SQL>
精彩评论