receiving xml with value null when it's actually null

Question

I'm executing a stored procedure on sql server 2005 from livecycle es 8.2

the result return something like

<Table>
<Row>
    <ID>1</ID>
    <EN_Cd>EN</EN_Cd>
    <FR_Cd>null</FR_Cd>
    <EN_Nm>English</EN_Nm>
    <FR_Nm>Anglais</FR_Nm>
    <EN_Shrt_Dscrptn>null</EN_Shrt_Dscrptn>
    <FR_Shrt_Dscrptn>null</FR_Shrt_Dscrptn>
</Row>
</Table>

I'm trying to figure out why the word "null" is put in the result.

Even with type xs:int it return the word "null"

is there something in the jdbc or livecycle that can fix this?

the stored procedure is a simple

   select id, en_cd, fr_cd, 
          en_nm, fr_nm, 
          en_shrt_dscrptn, fr_shrt_dscrptn 
   from language

fr_nm, en_shrt_dscrptn, fr_shrt_dscrptn are null in the database, they do not contain the value "null".

Answer 1

Try using the coalesce() function to convert nulls to empty strings, like this:

select id, 
      coalesce(en_cd,''), 
      coalesce(fr_cd,''), 
      coalesce(en_nm,''), 
      coalesce(fr_nm,''),
      coalesce(en_shrt_dscrptn,''), 
      coalesce(fr_shrt_dscrptn,'') 
from language

Alternatively, you could investigate how the conversion to XML is happening, and see if there's a way to specify null-handling options there.

Answer 2

you can use a XSLT transformation for removing the NULL from the nodes content. check my questions for a further explanation on that