GO Loader Aviation 1.2 is here! Take a look at what's new.
Skip to end of metadata
Go to start of metadata

Database Null values

In many application schemas, some properties of a Feature Type may be allowed to be null. Perhaps a real world object, represented by the feature type, may not always have a value for the property. Perhaps data is missing or the value is not know. Either way, it may be useful to provide the data user with an understanding of which scenario relates to which feature type property. This page details how to use custom SQL to define a Nil Reason attribute in your xml output.

 

Null values v's empty strings

GO Publisher's default handling of NULL values:

When GO Publisher encounters a 'NULL' in a database column, the property mapped to that database column (in the GO Publisher project file) will not be published.

However, if an empty string is present in the database column it will be included in the output, as an empty element: <example></example>

 

Assigning NilReason when only some data is NULL

Where instances of a property may be NULL while others contain values, to include a NilReason attribute for the NULL values you need to use a CASE statement:

If COLUMNX IS NULL THEN 'nilReason value'
ELSE NULL
END

 

1. Create a new column call nilReason and assign the value type to OTHER

2. Create a group containing the nilReason column and the column containing NULL values

  • Map the group and columns to the relevant XML property types
  • Map the group to the relevant XML property (e.g. ps-f:beginLifespanVersion) 
  • Map the database column to dot ('.')
  • Map the nilReason column to @NilReason

3. Define the CASE Statement in the CUSTOMSQL section:

Preview the XML:

The generated Output will include a nilReason attribute value of "unknown" with a xsi:nil type of "true":

 

Further Reading

Return to Mapping Translations

  • No labels