Subtle changes in XML, 11g vs 12c

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'))
--------------------------------------------------------------------------------
<TEST_DUMMY_OBJECT>
  <ID>1</ID>
  <Value>0</Value>
</TEST_DUMMY_OBJECT>

<TEST_DUMMY_OBJECT>
  <ID>1</ID>
  <Value>0</Value>
</TEST_DUMMY_OBJECT>

<TEST_DUMMY_OBJECT><ID>1</ID><Value>0</Value></TEST_DUMMY_OBJECT>
<TEST_DUMMY_OBJECT><ID>1</ID><Value>0</Value></TEST_DUMMY_OBJECT>
<TEST_DUMMY_OBJECT><ID>1</ID><Value>0</Value></TEST_DUMMY_OBJECT>





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)
---------------------------------------------------------------------------------------------------------------------------------------
<TEST_DUMMY_OBJECT>
  <ID>1</ID>
  <name/>
  <Value>0</Value>
</TEST_DUMMY_OBJECT>

<TEST_DUMMY_OBJECT>
  <ID>1</ID>
  <name/>
  <Value>0</Value>
</TEST_DUMMY_OBJECT>

<TEST_DUMMY_OBJECT><ID>1</ID><name/>
<Value>0</Value></TEST_DUMMY_OBJECT>

<TEST_DUMMY_OBJECT><ID>1</ID><name/>
<Value>0</Value></TEST_DUMMY_OBJECT>

<TEST_DUMMY_OBJECT><ID>1</ID><name/>
<Value>0</Value></TEST_DUMMY_OBJECT>




3 thoughts on “Subtle changes in XML, 11g vs 12c

  1. Just might possibly have been a Request for Enhancement.

    Normally it shouldn’t make a difference to the consumer of the XML, but I have seen complaints, where someone would like to use XmlForest() but couldn’t because NULL attributes did *not* appear – they had to use a series of XmlElement() where a NULL attribute *does* appear.

    So at least for some it apparently makes a difference.

  2. I’ve got a distant, distant familiar feeling that this might be the reverse of a change which happened between 9i and 11gR1 which caused a customer of mine issues on that upgrade about 10 years ago.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s