This discussion is archived
1 Reply Latest reply: Sep 27, 2012 8:15 AM by odie_63 RSS

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

MTM Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points