Oracle 9i Missing oppening xml tag when field has no data 1

[Daniel0228]

Hi guys! I have a question for you. Below is code for a procedure that selects from a table and converts data to xml and returns a CLOB to the calling procedure. The problem is that when my select clause doesn't return a value for one or more columns the xml produced is missing an opening tag.
Below is the code that produces the clob and the output:

procedure convert_to_xml(i_date in date, out_xml OUT CLOB) as


TYPE typ_ref IS REF CURSOR; --Declare cursor to get data from thot.apokyn_outbound_data table
v_ref typ_ref;

qryctx dbms_xmlgen.ctxHandle; -- Declare XML handle
--v_log_message THOT.RXC_MI_LOGS.log_message%TYPE; VARCHAR2(256);
BEGIN

  BEGIN

-- Cursor with NVL for columns that must appear regardless if they are null.
  OPEN v_ref FOR
--          SELECT ap.*
--          FROM thot.apokyn_outbound_data ap
--          WHERE trunc(ap.REPORTING_DATE) = i_date;
          
          SELECT 
          nvl(to_char(ap.SP_IDENTIFIER), ' ') as "SPIdentifier",
          nvl(ap.SPOR_PT_ID, ' ')             as "CoCPatient",
          ap.PATIENT_ID                       as "SPPatientID",
          nvl(ap.REFERRAL_DATE, ' ')          as "ReferralDate",
          nvl(ap.PT_STATUS_CODE, ' ')         as "PatientStatusCode",
          ap.STATUS_DATE                      as "PatientStatusDate",
          nvl(ap.SHIP_TO_LOC, ' ')            as "ShipToLocation",
          nvl(ap.CARRIER, ' ')                as "Carrier" ,
          nvl(to_char(ap.TRACKING_NUM),' ')   as "TrackingNumber",
          nvl(to_char(ap.prescription_id), ' ') as "RxNumber"           
            FROM thot.apokyn_outbound_data ap
            WHERE trunc(ap.REPORTING_DATE) = i_date;
          
        

--     charts table for the passed in p_ charts_seq_id to get the chart note text

-- Assigning new context to variable
      qryctx := dbms_xmlgen.newContext(v_ref);

-- Setting table header
      dbms_xmlgen.setRowSetTag(qryctx, 'MMTable');

-- Setting Row header
      dbms_xmlgen.setRowTag(qryctx, 'MMRow');
-- Storing XML code to CLOB variable
      out_xml := dbms_xmlgen.getXML(qryctx);
      
   
-- Deleting XML header 
   --   out_xml := REPLACE(out_xml,'<?xml version="1.0"?>','');

  CLOSE v_ref;

-- Closing context
  dbms_xmlgen.closeContext(qryctx); 
  
  

  EXCEPTION
    WHEN OTHERS THEN
      -- v_log_message:=substr('Exception Occurred At THOT.MI_XML_PROCESSOR_PKG.CREATE_CCF_REQUEST_PRC with SQL error message-'||SQLERRM,1,4000); -- CQ10231 Added substr( <string>,1,4000)
       -- Logging error
     --  THOT.MI_XML_PROCESSOR_UTIL_PKG.RXC_MI_LOG_ROWTYPE_PRC( p_req_header_id,'ERROR','CCF',v_log_message);
     
     dbms_output.put_line('Unhandled exception: '||substr(sqlerrm,1,200)); 
       RAISE;
  END;

end convert_to_xml;

And this is the output that I have a problem with:

<?xml version="1.0" ?>
- <MMTable>
- <MMRow>
<SPIdentifier>abcd</SPIdentifier>
<CoCPatient />
<SPPatientID>11111</SPPatientID>
<ReferralDate>08262005</ReferralDate>
<PatientStatusCode>A01</PatientStatusCode>
<PatientStatusDate>08062008</PatientStatusDate>
<ShipToLocation>2</ShipToLocation>
<Carrier /> <---------This needs to have openning tag.
<TrackingNumber />
<RxNumber>123456</RxNumber>

How would I resolve this problem? Thanks much!

 

[taupirho]

You need to set the nullhandling flag

Try putting this line before your query

DBMS_XMLGEN.setNullHandling(qryctx,1);


In order to understand recursion, you must first understand recursion.

 

[taupirho]

My mistake, obviously you should set the nullhandling flag after you get the qryctx returned, not before the query itself.





In order to understand recursion, you must first understand recursion.

 

[Daniel0228]

I'll try it. Thank you very much for you suggestion!

 

[sem]

<TrackingNumber/> is not the same as </TrackingNumber> , this is not closing tag but rather a shorthand for

<TrackingNumber></TrackingNumber>

Read XML spec.

Regards, Dima

http://seminihin.narod.ru

 

[Daniel0228]

Thanks Dima!