CODEWITHSUNDEEP
sqlxmloracle-database
Nikhil Tikhe
Nikhil Tikhe

Reputation: 38

How to fix xml tags in oracle

I have empty tag in oracle sql like

    <ID></ID>

I need to change it to

    <ID/>

in oracle sql. Anyone have the solution for it?

Expected:"<ID/>"
Actual:"<ID></ID>"

I am using this Oracle XML query to generate the XML:

Select XMLSERIALIZE(DOCUMENT XMLELEMENT("EMP",XMLATTRIBUTES('NIKHIL' "NAME" )
       ,XMLELEMENT("ID",ID)
      ,XMLELEMENT("DESIGNATION",DESIGNATION)) ) as "XML" 
from (select '1' "ID",'DEVELOPER' "DESIGNATION" 
      from dual 
      union 
      select null "ID",'DEVELOPER' "DESIGNATION" from dual )

Upvotes: 1

Views: 284

Answers (1)

damir huselja
damir huselja

Reputation: 171

If you wrap it in a xmlroot element, you get the tag as desired.

  Select XMLSERIALIZE(DOCUMENT 
      XMLROOT(
      XMLELEMENT
      (
          "EMP",XMLATTRIBUTES('NIKHIL' "NAME" )
          ,XMLELEMENT("ID",ID)
          ,XMLELEMENT("DESIGNATION",DESIGNATION) 
      ), version '1.0" encoding="Windows-1250')) as "XML" 
  from (select '1' "ID",'DEVELOPER' "DESIGNATION" 
        from dual 
        union 
        select null "ID",'DEVELOPER' "DESIGNATION" from dual )

Result is:

<?xml version="1.0" encoding="Windows-1250"?>
<EMP NAME="NIKHIL">
<ID/>
<DESIGNATION>DEVELOPER</DESIGNATION>
</EMP>

Upvotes: 1

Related Questions