Skip to Main Content
  • Questions

  • XMLTYPE null element behavior on 11gR2 vs 12.1/12.2

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Jacek.

Asked: January 22, 2018 - 12:34 am UTC

Last updated: October 10, 2019 - 2:44 pm UTC

Version: 11G R2 XE / 12.1 / 12.2

Viewed 1000+ times

You Asked

Hi Guys,

I've just noticed inconsistency between behavior of conversion of object/collection data to XMLTYPE between 11g R2 and 12.1/12.2 database.

Testcase:

create or replace type test_dummy_object as object (
  id number,
  "name"  varchar2(30),
  "Value" varchar2(30)
)
/

select xmlserialize(content xmltype(anydata.convertObject( test_dummy_object(1, null, '0') )) indent SHOW DEFAULTS) from dual union all
select xmlserialize(content xmltype(anydata.convertObject( test_dummy_object(1, null, '0') )) indent HIDE DEFAULTS) from dual union all
select xmltype(anydata.convertObject( test_dummy_object(1, null, '0') )).getclobval() from dual union all
select xmltype(test_dummy_object(1, null, '0')).getclobval() from dual union all
select xmltype.createxml(test_dummy_object(1, null, '0')).getclobval() from dual;


On 11g R2 XE I get:
"<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>


On 12.1 and 12.2 I get however additional "<name/>" TAG that represents the NULL value of NAME attribute in type.


I like the new feature but the question is:

How can I get consistency between 11.2 and 12 databases for handling NULL Object Attribute/Column Value when using XMLTYPE as a container for object/cursor data?

I cant find a way to control how XMLTYPE is handling null Object attributes or cursor column values.




and Connor said...

I spoke to the XML product manager. There's no way to control it.

But any XML processor should be fine with either, no ?

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

use DBMS_XMLGEN

alshru, October 10, 2019 - 6:09 am UTC

I found this workaround:

-- result in 12.2 like as 11.2
DECLARE
l_obj test_dummy_object;
qryCtx DBMS_XMLGEN.ctxHandle;
l_xml XMLTYPE;
lc pk_cmn.TAbstractCur;
BEGIN
l_obj := test_dummy_object(1,NULL,0);
OPEN lc FOR 'SELECT :a_obj AS TEST_DUMMY_OBJECT FROM DUAL' USING l_obj;
qryCtx := DBMS_XMLGEN.newContext(lc);
-- DROP_NULLS - default behavior in 11.2
-- EMPTY_TAG - default behavior in 12
DBMS_XMLGEN.setNullHandling(qryCtx, dbms_xmlgen.DROP_NULLS);
l_xml := XMLTYPE(DBMS_XMLGEN.getXML(qryCtx));
CLOSE lc;
dbms_output.put_line(l_xml.extract('//TEST_DUMMY_OBJECT').getClobVal());
END;
/
Chris Saxon
October 10, 2019 - 2:44 pm UTC

Thanks for sharing.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.