1 Reply Latest reply on Sep 27, 2012 3:15 PM by odie_63

    XMLGEN: Produce XML dump of a table WITH tags for null column values

    MTM
      I am new to generating XML so bear with me....

      We have a customer who needs an XML extract of data, including tags for any column that is null.

      I created a simple test case below using DBMS_XMLGEN.getXML. The first row (A1) has no null values and thus tags for columns A, BEE and CEE are produced. The second row (A2) has null in column BEE and thus tags for only columns A and CEE are produced.

      Is there a way to force a tag for null column BEE in the second row?

      create table foo (A varchar2(10), BEE number, CEE date);

      insert into foo values ('A1',1,sysdate);
      insert into foo values ('A2',null,sysdate);

      SELECT DBMS_XMLGEN.getXML('SELECT * FROM foo') FROM dual;

      <ROWSET>
      <ROW>
      <A>A1</A>
      <BEE>1</BEE>
      <CEE>27-SEP-12</CEE>
      </ROW>
      <ROW>
      <A>A2</A>
      <CEE>27-SEP-12</CEE>
      </ROW>
      </ROWSET>
        • 1. Re: XMLGEN: Produce XML dump of a table WITH tags for null column values
          odie_63
          What's the database version? (SELECT * FROM v$version)

          Could you use this instead :
          SQL> select xmlserialize(document
            2           xmlelement("ROWSET",
            3             xmlagg(
            4               xmlelement("ROW",
            5                 xmlelement("A", a)
            6               , xmlelement("BEE", bee)
            7               , xmlelement("CEE", cee)
            8               )
            9             )
           10           )
           11         -- for display purpose only :
           12         as clob indent
           13         )
           14  from foo
           15  ;
           
          XMLSERIALIZE(DOCUMENTXMLELEMEN
          --------------------------------------------------------------------------------
          <ROWSET>
            <ROW>
              <A>A1</A>
              <BEE>1</BEE>
              <CEE>2012-09-27</CEE>
            </ROW>
            <ROW>
              <A>A2</A>
              <BEE/>
              <CEE>2012-09-27</CEE>
            </ROW>
          </ROWSET>
           
          Or,
          SQL> select xmlserialize(document
            2           xmlquery(
            3             '(#ora:view_on_null empty #)
            4             {
            5               <ROWSET>{fn:collection("oradb:/DEV/FOO")}</ROWSET>
            6             }'
            7             returning content
            8           )
            9           as clob indent
           10         )
           11  from dual;
           
          XMLSERIALIZE(DOCUMENTXMLQUERY(
          --------------------------------------------------------------------------------
          <ROWSET>
            <ROW>
              <A>A1</A>
              <BEE>1</BEE>
              <CEE>2012-09-27</CEE>
            </ROW>
            <ROW>
              <A>A2</A>
              <BEE/>
              <CEE>2012-09-27</CEE>
            </ROW>
          </ROWSET>
           
          (where "DEV" is my test schema)


          If you want to stick with DBMS_XMLGEN, you're gonna have to use PL/SQL and setNullHandling procedure.

          Edited by: odie_63 on 27 sept. 2012 17:14