Subtle changes in XML, 11g vs 12c

connor_mc_d's picture

An AskTOM reader brought this to our attention.  It is unlikely to cause you any issues, but perhaps is good to know when it comes times to upgrade from 11g to 12c.

If you are taking an user defined object type and transposing that to XML, you will see a slightly different handling of NULLs in the object attributes.  Here’s a quick example which demonstrates the difference.

11g

Nulls attributes do not appear within the XML output



SQL> create or replace type test_dummy_object as object (
  2    id number,
  3    "name"  varchar2(30),
  4    "Value" varchar2(30)
  5  )
  6  /

Type created.

SQL>
SQL> select xmlserialize(content xmltype(anydata.convertObject( test_dummy_object(1, null, '0') )) indent SHOW DEFAULTS) from dual
  2  union all
  3  select xmlserialize(content xmltype(anydata.convertObject( test_dummy_object(1, null, '0') )) indent HIDE DEFAULTS) from dual
  4  union all
  5  select xmltype(anydata.convertObject( test_dummy_object(1, null, '0') )).getclobval() from dual
  6  union all
  7  select xmltype(test_dummy_object(1, null, '0')).getclobval() from dual
  8  union all
  9  select xmltype.createxml(test_dummy_object(1, null, '0')).getclobval() from dual;

XMLSERIALIZE(CONTENTXMLTYPE(ANYDATA.CONVERTOBJECT(TEST_DUMMY_OBJECT(1,NULL,'0'))
--------------------------------------------------------------------------------

  1
  0



  1
  0


10
10
10





12c

Nulls attributes do appear within the XML output



SQL> create or replace type test_dummy_object as object (
  2    id number,
  3    "name"  varchar2(30),
  4    "Value" varchar2(30)
  5  )
  6  /

Type created.

SQL>
SQL> select xmlserialize(content xmltype(anydata.convertObject( test_dummy_object(1, null, '0') )) indent SHOW DEFAULTS) from dual
  2  union all
  3  select xmlserialize(content xmltype(anydata.convertObject( test_dummy_object(1, null, '0') )) indent HIDE DEFAULTS) from dual
  4  union all
  5  select xmltype(anydata.convertObject( test_dummy_object(1, null, '0') )).getclobval() from dual
  6  union all
  7  select xmltype(test_dummy_object(1, null, '0')).getclobval() from dual
  8  union all
  9  select xmltype.createxml(test_dummy_object(1, null, '0')).getclobval() from dual;

XMLSERIALIZE(CONTENTXMLTYPE(ANYDATA.CONVERTOBJECT(TEST_DUMMY_OBJECT(1,NULL,'0')))INDENTSHOWDEFAULTS)
---------------------------------------------------------------------------------------------------------------------------------------

  1
  
  0



  1
  
  0


1
0

1
0

1
0