Home » SQL & PL/SQL » SQL & PL/SQL » null handling DBMS_XMLGEN
null handling DBMS_XMLGEN [message #185550] Wed, 02 August 2006 05:23 Go to next message
ms_bee
Messages: 5
Registered: July 2006
Location: indonesia
Junior Member

hi all,

i have a problem with null handling in DBMS_XMLGEN

if a field value return from my query is null, i want to keep showing the tag

i've set :
-----------
DBMS_XMLGEN.setNullHandling(qryCtx, 2);

the result is :
---------------
<ApplicantName/>


can the output be like :
------------------------
<ApplicantName></ApplicantName>

can anyone help me?

thanks in advance =)
Re: null handling DBMS_XMLGEN [message #185662 is a reply to message #185550] Wed, 02 August 2006 23:18 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
AFAIK you cannot do it automatically using DBMS_XMLGEN. The options for NULL handling that this provides are:
  • DROP_NULLS CONSTANT NUMBER:= 0; (Default) Leaves out the tag for NULL elements.
  • NULL_ATTR CONSTANT NUMBER:= 1; Sets xsi:nil="true".
  • EMPTY_TAG CONSTANT NUMBER:= 2; Sets an empty tag for NULL elements in the format <foo/>. (The option you are using)
Any particular reason why you want the format changed? You are still "showing the tag": <ApplicantName/> is semantically identical to <ApplicantName></ApplicantName>. The first style is preferred as it consumes less space.

Re: null handling DBMS_XMLGEN [message #185669 is a reply to message #185662] Thu, 03 August 2006 00:28 Go to previous messageGo to next message
ms_bee
Messages: 5
Registered: July 2006
Location: indonesia
Junior Member

=))

hahaha..yes i've manage to showed the tag already and it consumes less space ( i'm really aware of that, hicks Sad )
it's just that another application will read the output xml.
and if value is null, the tag is expected to be full tag like <ApplicantName></ApplicantName>, instead of <ApplicantName/>

i was thinking about converting the value to blank space..
NVL(ApplicantName,' '), but stilll...no luck

i guess it's oracle's default then, if value is null or blank space, the tag will be like <ApplicantName/>

i think there must be another way around it, but i dont know how..
i a newbie in this oracle - xml thing hahahah T-T
or...it cannot be change by de facto

what do you think?

thanks Wink
Re: null handling DBMS_XMLGEN [message #185684 is a reply to message #185550] Thu, 03 August 2006 01:53 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
Well...you can use some SQL string manipulation functions after you have got the XML with DBMS_XMLGEN...check this out:
SQL> SELECT * FROM test;

      CNUM CSTR
---------- --------------
         1 abc
         2
         3

SQL> DECLARE
  2     qryCtx   DBMS_XMLGEN.ctxHandle;
  3     result   CLOB;
  4     mresult  CLOB;
  5  BEGIN
  6    qryCtx := dbms_xmlgen.newContext('SELECT * FROM test');
  7  
  8    -- set the row header
  9    DBMS_XMLGEN.setRowTag(qryCtx, 'TESTRUN');
 10    DBMS_XMLGEN.setNullHandling(qryCtx, dbms_xmlgen.EMPTY_TAG);
 11  
 12    -- now get the result
 13    result :=  DBMS_XMLGEN.getXML(qryCtx);
 14  
 15    dbms_output.put_line('Original XML');
 16    dbms_output.put_line('***********************************');
 17    dbms_output.put_line(result);
 18    
 19    SELECT REPLACE(result, '<CSTR/>', '<CSTR></CSTR>') INTO mresult FROM dual;
 20  
 21    dbms_output.put_line('XML With Empty Tag Adjusted');
 22    dbms_output.put_line('***********************************');
 23    dbms_output.put_line(mresult);
 24  
 25    --close context
 26    DBMS_XMLGEN.closeContext(qryCtx);
 27  END;
 28  /
Original XML
***********************************
<?xml version="1.0"?>
<ROWSET>
 <TESTRUN>
  <CNUM>1</CNUM>
  <CSTR>abc</CSTR>
</TESTRUN>
 <TESTRUN>
  <CNUM>2</CNUM>
  <CSTR/>
 </TESTRUN>
 <TESTRUN>
<CNUM>3</CNUM>
  <CSTR/>
 </TESTRUN>
</ROWSET>

XML With Empty Tag Adjusted
***********************************
<?xml version="1.0"?>
<ROWSET>
 <TESTRUN>
  <CNUM>1</CNUM>
  <CSTR>abc</CSTR>
</TESTRUN>
 <TESTRUN>
  <CNUM>2</CNUM>
  <CSTR></CSTR>
 </TESTRUN>
 <TESTRUN>
  <CNUM>3</CNUM>
  <CSTR></CSTR>
 </TESTRUN>
</ROWSET>


PL/SQL procedure successfully completed.

[Updated on: Thu, 03 August 2006 01:59]

Report message to a moderator

Re: null handling DBMS_XMLGEN [message #185698 is a reply to message #185684] Thu, 03 August 2006 03:02 Go to previous message
ms_bee
Messages: 5
Registered: July 2006
Location: indonesia
Junior Member

it woorrkkss, hobbes!! Laughing

thank u so mucchh..

i create a function to rename tags :
------------------------------------
function transform_null_tag(p_xml clob)
return clob
is
o_xml clob;
begin

SELECT REPLACE(p_xml, '<Domain/>', '<Domain></Domain>') INTO o_xml FROM dual;
SELECT REPLACE(o_xml, '<ApplicationNo/>', '<ApplicationNo></ApplicationNo>') INTO o_xml FROM dual;
SELECT REPLACE(o_xml, '<ApplicantName/>', '<ApplicantName></ApplicantName>') INTO o_xml FROM dual;

return o_xml;
end;

heheheh..
i wonder, when will i be able to come up with such ideas like you guys...(*sigh*) practice, pratice, practice....right?
thankss a lot!!! Laughing
Previous Topic: sql loader count in a variable
Next Topic: Convert Value to Percentage?
Goto Forum:
  


Current Time: Sat Dec 03 04:12:03 CST 2016

Total time taken to generate the page: 0.13139 seconds