How to return json with null value using apex_json.write(ref_cursor)

Few days ago I had to develop dynamic query json export module.

As a sandbox I prepared PLSQL block:

DECLARE
  c sys_refcursor;
  l_sql varchar2(4000) := 'select null as "col1", 100 as "col2" from dual
						   union all
						   select 200 as "col1", null as "col2" from dual';
  l_file clob;
BEGIN
  open c for l_sql;
    apex_json.initialize_clob_output;
      apex_json.open_object;
      apex_json.write('rowset', c);
      apex_json.close_object;      
    l_file := apex_json.get_clob_output;
  apex_json.free_output;

  DBMS_OUTPUT.PUT_line(l_file);
END;


{
    "rowset": [{
            "col2": 100
        }, {
            "col1": 200
        }
    ]
}

As you can see above PLSQL code doesn't return null value pairs : (
apex_json.write(p_name IN VARCHAR2, p_cursor IN OUT NOCOPY sys_refcursor) procedure Signature 14 doesn't support null. How to fix it... ? APEX API provides apex_json.write Signature 15 where you can pass 3 parameters:
p_name IN VARCHAR2 - The attribute name.
p_value IN sys.xmltype - The value to be written. The XML is converted to JSON
p_write_null IN BOOLEAN DEFAULT FALSE - If true, write NULL values. If false (the default), do not write NULLs.

According description p_value must be xmltype. So we can prepare xml object from ref_cursor with null nodes and then we can use apex_json.write Signature 15. Let's do it !

DECLARE
  c sys_refcursor;
  l_sql varchar2(4000) := 'select null as "col1", 100 as col2 from dual
                        union all
                        select null as "col1", null as col2 from dual';
   l_xml xmltype;
   l_ctx dbms_xmlgen.ctxHandle;
BEGIN
  open c for  l_sql;
	/* converts the results of a SQL query to a canonical XML format */
    l_ctx := dbms_xmlgen.newContext(c); 
	/* Sets NULL handling options */
    dbms_xmlgen.setNullHandling(l_ctx, dbms_xmlgen.EMPTY_TAG);
    l_xml := dbms_xmlgen.getxmltype(l_ctx); 
    dbms_xmlgen.closecontext(l_ctx); 
  close c; 
  
  apex_json.initialize_clob_output;      
  apex_json.open_object;
  
  /* I don't know why but 3-th parameter doesn't work for me,
     always returns null which is ok in my case */
  apex_json.write('rowset', l_xml, true);
  
  apex_json.close_all;

  DBMS_OUTPUT.PUT_line(apex_json.get_clob_output);
END;


{
    "rowset": [{
            "col1": null,
            "col2": 100
        }, {
            "col1": null,
            "col2": null
        }
    ]
}

Probably you can reach the same result using other way but if you need to use apex_json.write its one of possible solution.