3 Replies Latest reply: Jun 22, 2012 6:47 AM by 923195 RSS

    Script for generating XML file ... problem with null values

    923195
      Greetings everyone,

      i come here with a question that troubles me for some time now. I have a script which i run from SQLPLUS every now and then to generate an XML file.

      Problem is that data which needs to be in XML is not allways <> NULL and i need to hide those tags that are empty </tag>.

      I will post below my script and if you could help me with it it would be really great!

      Thanks for reading!
      set long 20000000
      set long 20000000
      set linesize 32000
      SET ECHO OFF
      SET TRIMSPOOL on
      SET HEADING OFF
      SET PAGESIZE 50000
      SET VERIFY OFF
      SET FEEDBACK OFF
      SET TERMOUT OFF
      spool C:\test.xml
      set serveroutput on
      begin
        dbms_output.put_line('<?xml version="1.0" encoding="utf-8" ?>');
      end;
      /
      SELECT
      XMLELEMENT("ReportRoot",XMLATTRIBUTES('http://www.w3.org/2001/XMLSchema-instance' as "xmlns:xsi", 'http://www.w3.org/2001/XMLSchema' as "xmlns:xsd" , '1.0' as "Version",sysdate as "CreationDate",to_char(sysdate,'hh:mm:ss') as "CreationTime",'1524544845' as "id"),
      XMLELEMENT("Porocila",XMLELEMENT("JOLY",(SELECT XMLAGG (XMLELEMENT("RefNrReport",replace('SON'||to_char(ref_ST,'00000'),' ',''))) from access_table_2 where ref_ST = &1),
      XMLELEMENT("ReportDate",sysdate),XMLELEMENT("Labeling",'545254450'),
       (SELECT XMLAGG     (XMLELEMENT("Reportf",
                                                                       XMLELEMENT("access",access),
                                                                       XMLELEMENT("date",date),
                                                                       XMLELEMENT("datep",datep),
                                                                       XMLELEMENT("ModificationInfo",'M'),XMLELEMENT("ModificationReason",modireason)))
                                                   from v_xml_test where id_dok = &1 and ind_print = '1'))))
        .extract('/*')
        from dual
           /     
           spool off
      /
      exit
      Now lets pretend that
      XMLELEMENT("datep",datep),
      is sometimes NULL and i do not want to display it.