CODEWITHSUNDEEP
sqlxmldatabaseoracle-database
talaa123
talaa123

Reputation: 123

dbms_xmlgen.getxmltype(context) returns null when the sql table is empty

I have this following function to convert table data to XML. However, whenever the table data is null the resulting XML is null. I want it to add the column names in case of empty table data. What changes should I make in this function. 

function XmlToBlob(p_query varchar2) return blob is 
  ctx         dbms_xmlgen.ctxhandle;
  xml         xmltype;
  resultBlob  blob; 
begin
  ctx := dbms_xmlgen.newcontext(p_query);
  dbms_xmlgen.setNullHandling(ctx, dbms_xmlgen.empty_tag);
  xml := dbms_xmlgen.getxmltype(ctx);
  if (xml is not null) then
    resultBlob := xml.getBlobVal(NLS_CHARSET_ID('AL32UTF8'));
  else
    resultBlob := null;
  end if;
  return resultBlob;
end;

Upvotes: 0

Views: 1088

Answers (1)

Jens Krogsboell
Jens Krogsboell

Reputation: 1123

You should change your query so that it will return a single row of null values if no other data can be retrieved.

Try this:

FOR ORACLE:

select emp.*
from   dual
left   join emp on ( 1 = 1 )

FOR MSSQL SERVER:

select t.*
from (select 1 as adummy) a
left join (select * from emp ) t on 1=1

Upvotes: 1

Related Questions