CODEWITHSUNDEEP
sqlxmloracle-database
Nik
Nik

Reputation: 140

How to generate XML data in Oracle which has Empty string as an attribute

I want to generate an XML element using Oracle's XML documentation generation support features that looks like this

<Example Attr=""></Example>

Attr is an attribute of element Example and has a value of empty string "".

When I tried to generate an XML Element using Oracle's XML functions, I couldn't generate an XML element which has an attribute whose value is an empty string.

select XMLELEMENT("hello", xmlattributes('' as  "Max"))  from  dual

The result of the above query is 

<hello></hello>

Note: there is no space between the single quotes for Max attribute.

However my requirement is 

<hello Max=""></hello>     -- there is no space between the double quotes.

Is there a way to do this?

Upvotes: 3

Views: 4841

Answers (4)

Christopher Leuer
Christopher Leuer

Reputation: 81

I used updatexml() similar to A. Poole's example to create an empty string

set serveroutput on
 DECLARE
   xa xmltype;
   xb xmltype;
 BEGIN
   xa  := xmltype('<surfbreaks>'||
     '<break lineNum="0" recordtype="empty">d street </break>'||
     '<break lineNum="0" recordtype="empty">recordtype="empty" </break>'||
     '</surfbreaks>');

  dbms_output.put_line ('Before:');
  dbms_output.put_line (xa.getclobval);

  select UPDATEXML(xa, '//@recordtype', '') into xb from dual;
  dbms_output.put_line ('After:');
  dbms_output.put_line (xb.getclobval);
END; /

Upvotes: 0

lmocsi
lmocsi

Reputation: 1096

I'm using this approach (you write the source attribute instead of the "null"). This way, you don't have to search for some overall impossible value, it's enough to be impossible for that attribute.

select replace(XMLELEMENT("hello", xmlattributes(nvl(null,' ') as "Max")).getstringval(), 'Max=" "', 'Max=""') from dual

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191560

As you're aware, for XMLAtttribute "if the value_expr is null, then no attribute is created for that value expression".

You can work around this with InsertChildXML but it isn't terribly pretty:

select insertchildxml(xmlelement("hello"), '/hello', '@Max', null) from dual;

INSERTCHILDXML(XMLELEMENT("HELLO"),'/HELLO','@MAX',NULL)                       
--------------------------------------------------------------------------------
<hello Max=""/>

... and as you can see it collapses an empty node, but that's only a potentially issue if you want this to look exactly as you showed - it's valid XML still. There is an even uglier way around that if you really need to.

That also suggests an alternative to @smnbbrv's replace:

select updatexml(xmlelement("hello", xmlattributes('$$IMPOSSIBLE-VALUE$$' as  "Max")),
  '/hello[@Max="$$IMPOSSIBLE-VALUE$$"]/@Max', null) from dual;

UPDATEXML(XMLELEMENT("HELLO",XMLATTRIBUTES('$$IMPOSSIBLE-VALUE$$'AS"MAX")),'/HEL
--------------------------------------------------------------------------------
<hello Max=""/>

which might be easier if your max attribute value is coming from data as you can NVL it to the impossible value. I'm not a fan of using magic values though really.

Upvotes: 4

smnbbrv
smnbbrv

Reputation: 24581

What about setting the property value to some impossible value and then replace it with the value you need (so, empty string in your case)?

select replace(
  XMLELEMENT("hello", xmlattributes('$$IMPOSSIBLE-VALUE$$' as  "Max")).getStringVal(),
  '$$IMPOSSIBLE-VALUE$$'
)
from  dual;

I assume you anyway in the end need the string value, so even if this XMLELEMENT is just an example of the problem and you have a biiiig XML generated, you still can generate it first and then, finally, replace all the values with one command as shown above.

Upvotes: 3

Related Questions