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".

Please edit your post and add the query that produced this result. — Jim Garrison, Sep 13 '10 at 17:08
Had the same question but posted on Adobe forums. Here's the link http://forums.adobe.com/thread/721645?tstart=0. Never really got a good answer other than speculation that the behaviour might change with JDBC/Datastore combo. Basically, the XML just returns the value in the field, period. I'd say the best bet is to XSLT the nulls into the correct XML xsi:nil="true", before it returns out of the workflow. — Fireworks, Oct 28 '10 at 14:36
@Jim Garrison - LC has 'foundation' components that you configure through the IDE. One of the 'foundation' components is a JDBC one, that contains what is called a 'Query Multiple Rows as XML'. It calls the datastore and then formats the results into an XML result. A good example of how it works, with a solution for transforming the results is here http://www.adobe.com/devnet/livecycle/articles/transforming_jdbc.html. — Fireworks, Oct 28 '10 at 14:54

score 1 · Accepted Answer · answered Sep 13 '10 at 17:52

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.

answered Sep 13 '10 at 17:52

Jim Garrison

85,615
20
155
190

Yea unfortunately you have to wrap the columns with either coalesce or isnull in SQL. In Livecycle 9 if a column is null and you are using the Select single row component it will actually throw a SQL Exception. – Brad H. Oct 21 '10 at 19:52

score 0 · Answer 2 · answered Sep 23 '10 at 15:56

0

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

answered Sep 23 '10 at 15:56

azathoth

573
2
6
18

receiving xml with value null when it's actually null

2 Answers2