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

Question

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?

Community · Accepted Answer · 2017-05-23 11:46:05Z

4

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.

edited May 23, 2017 at 11:46

CommunityBot

11 silver badge

answered Sep 3, 2015 at 16:40

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Gottfried Lesigang Over a year ago

@user445, if this solves your problem, you should mark this as accepted. Everybody will know, that this issue is done and Alex is happy with some reputation points. Thx!

Nik Over a year ago

@Shnugo. Accepted! Thank you :)

smnbbrv Over a year ago

@Shnugo this is a bit unfair to apply pressure on the people by pushing your opinion. You should have posted this as a comment on a question and not on an answer

Alex Poole Over a year ago

You don't need to publicly justify your choice; but follow the help center guidance and choose the best solution to your problem (or wait for other answers; there may still be other ways to do this). 'Best' is subjective of course, but that's partly the point, it's up to you - don't be pressured to accept or even upvote an answer (even mine!)

Gottfried Lesigang Over a year ago

@smnbbrv, you are right. Actually when I wrote this I hadn't seen, that user445 commented your answer with the same "solves my problem". I'm one of your upvoters anyway :-)

|

smnbbrv · Accepted Answer · 2015-09-03 16:17:20Z

3

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.

answered Sep 3, 2015 at 16:17

smnbbrv

24.7k9 gold badges85 silver badges116 bronze badges

Comments

lmocsi · Accepted Answer · 2015-11-13 20:40:14Z

0

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

answered Nov 13, 2015 at 20:40

lmocsi

1,1841 gold badge14 silver badges29 bronze badges

Comments

Christopher Leuer · Accepted Answer · 2015-11-17 04:10:39Z

0

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; /

answered Nov 17, 2015 at 4:10

Christopher Leuer

815 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related