title: Oracle XMLType generation with null values from trigger to advanced queue
body: <p>Good Afternoon.</p>
  
  <p>I have been working with Oracle Advanced Queues to create a messaging system that we can use to determine when new lines are passed into the databse, when updates occur and when deletes occur.</p>
  
  <p>I am using a single consumer queue and using correlations to control what data is looked for at certain times and my payload is of xmltype.</p>
  
  <p>To generate the xml, I originally was using an xmlsequence to generate the message as below.</p>
  
  <pre><code>&lt;MESSAGE&gt;
  &lt;LOCATIONS&gt;
    &lt;LOCATION_ID&gt;9999&lt;/LOCATION_ID&gt;
    &lt;LOC_TYPE&gt;S&lt;/LOC_TYPE&gt;
    &lt;NAME&gt;Test Location&lt;/NAME&gt;
    &lt;RETAILER_UNIT_CODE&gt;T&amp;amp;L&lt;/RETAILER_UNIT_CODE&gt;
    &lt;REGION_CODE&gt;SA&lt;/REGION_CODE&gt;
    &lt;DELETE_FLAG&gt;N&lt;/DELETE_FLAG&gt;
    &lt;EXTERNAL_WHSE_FLAG&gt;N&lt;/EXTERNAL_WHSE_FLAG&gt;
    &lt;CREATED_BY&gt;SYSADMIN&lt;/CREATED_BY&gt;
    &lt;CREATED_DATE&gt;04/MAR/08&lt;/CREATED_DATE&gt;
    &lt;STATE_CODE&gt;SA&lt;/STATE_CODE&gt;
    &lt;ADDRESS&gt;223 Road Ridsonville&lt;/ADDRESS&gt;
    &lt;POSTCODE&gt;1234&lt;/POSTCODE&gt;
    &lt;PHONE_NUM&gt;08 &lt;/PHONE_NUM&gt;
    &lt;LAST_MODIFIED_BY&gt;SYSADMIN&lt;/LAST_MODIFIED_BY&gt;
    &lt;LAST_MODIFIED_DATE&gt;21/APR/09&lt;/LAST_MODIFIED_DATE&gt;
    &lt;POS_CODE&gt;TRANS&lt;/POS_CODE&gt;
    &lt;SOP_FLAG&gt;N&lt;/SOP_FLAG&gt;
  &lt;/LOCATIONS&gt;
  &lt;/MESSAGE&gt;
  </code></pre>
  
  <p>However, it has come to my attention that xmlsequence leaves out null elements, which is not ideal, as when it comes time for the messages to be picked up at the other end, they cannot be mapped on data field easily.
  To try and work around this, I have tried to employ the use of  DBMS_XMLGEN within my trigger, as it allows the handling of nulls in exactly the way I would want it handled.</p>
  
  <pre><code>ctx := dbms_xmlgen.newContext('SELECT * FROM LOCATIONS WHERE LOCATION_ID = ' || :new.LOCATION_ID);
  dbms_xmlgen.setrowsettag(ctx, 'MESSAGE');
  dbms_xmlgen.setrowtag(ctx, 'LOCATIONS');
  dbms_xmlgen.setnullhandling(ctx, dbms_xmlgen.EMPTY_TAG);
  l_xml:=dbms_xmlgen.getxmltype(ctx);
  </code></pre>
  
  <p>The problem with this approach is that it will generate an exception, as it is trying to operate on the same table that the trigger has been executed from.</p>
  
  <pre><code>ORA-04091: table RIT.LOCATIONS is mutating, trigger/function may not see it
  </code></pre>
  
  <p>So I moved on to trying to use the new and old objects to get the data out and place it in to the context, but I am running in to a few hiccups with the values that are null, as they are playing havoc with my concatenating them in to the query string.</p>
  
  <pre><code>...
  REFERENCING NEW AS NEW OLD AS OLD
  for each row 
  DECLARE
  l_xml   xmltype;
  ctx       dbms_xmlgen.ctxHandle;
  begin
  ctx := dbms_xmlgen.newContext('SELECT   '||:new.id||'as id, '||:new.nullfield||' as nullfield from dual');
  dbms_xmlgen.setrowsettag(ctx, 'MESSAGE');
  dbms_xmlgen.setrowtag(ctx, 'INT_CREDIT_CLAIMS');
  dbms_xmlgen.setnullhandling(ctx, dbms_xmlgen.EMPTY_TAG);
  l_xml:=dbms_xmlgen.getxmltype(ctx);
  ...
  end;
  </code></pre>
  
  <p>So my question is: How can I go about solving this so that I can see my empty elements for my xml type?</p>
  
  <p>I'm also open to suggestions along the way on how to solve it and will be checking back for any clarification requests.</p>