Oracle Xml Generate node null Help

Question

Hi;

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 [email protected]
2 - - - 23 - - - - - - - - - - [email protected]

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>[email protected]</E_MAIL>
</ROW>
<ROW>
<ID>1</ID>
<SAL>23</SAL>
<E_MAIL>[email protected]</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>[email protected]</E_MAIL>
<ROW>
<ID>2</ID>
<NAME></NAME>
<SAL>23</SAL>
<HIREDATE></HIREDATE>
<DEPTNO></DEPTNO>
<E_MAIL>[email protected]</E_MAIL>
</ROW>
</ROWSET>

Please Help Me...

Answer 1

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>