EmptyToNull

In This Topic

Function Name

Empty To Null

Icon

Description

If a value is null it is treated as an empty sequence (or null)

Inputs

Value

The value to be tested

Outputs

Result

The resulting value

Properties

None

Usage

In some systems a string that is empty can be considered to be missing (or null), this function allows you to formalize that assumption.

The behavior of the function is simple, if the input string value is not empty, then the input string is returned. If the input string is an empty it is ignored.

Example

Its easier to use an example to see how this can be useful.

Input Data	Copy Code
James,Paul,McCartney Liv,,Tyler William,Bradley,Pitt Björk,, George,,Clooney

Given the above transform and input data. the resulting XML would look like this

Output XML	Copy Code
<Celebrity> <Name> <Forename>James</Forename> <MiddleName>Paul</MiddleName> <Surname>McCartney</Surname> </Name> <Name> <Forename>Liv</Forename> <MiddleName></MiddleName> <Surname>Tyler</Surname> </Name> <Name> <Forename>William</Forename> <MiddleName>Bradley</MiddleName> <Surname>Pitt</Surname> </Name> <Name> <Forename>Björk</Forename> <MiddleName></MiddleName> <Surname></Surname> </Name> <Name> <Forename>George</Forename> <MiddleName></MiddleName> <Surname>Clooney</Surname> </Name> </Celebrity>

Output XML

Copy Code

<Celebrity>
 <Name>
  <Forename>James</Forename>
  <MiddleName>Paul</MiddleName>
  <Surname>McCartney</Surname>
 </Name>
 <Name>
  <Forename>Liv</Forename>
  <MiddleName></MiddleName>
  <Surname>Tyler</Surname>
 </Name>
 <Name>
  <Forename>William</Forename>
  <MiddleName>Bradley</MiddleName>
  <Surname>Pitt</Surname>
 </Name>
 <Name>
  <Forename>Björk</Forename>
  <MiddleName></MiddleName>
  <Surname></Surname>
 </Name>
 <Name>
  <Forename>George</Forename>
  <MiddleName></MiddleName>
  <Surname>Clooney</Surname>
 </Name>
</Celebrity>

As you can see we create a <MiddleName> and <Surname> element even if the source field was empty.

In this example we are getting the data from a CSV file and the CSV format does not have a concept of null so empty fields are common.

Now suppose we only want the <MiddleName> and <Surname> element to be written out if they contain a value.

The following transform would allow us to do this

This would produce the following XML

Output XML	Copy Code
<Celebrity> <Name> <Forename>James</Forename> <MiddleName>Paul</MiddleName> <Surname>McCartney</Surname> </Name> <Name> <Forename>Liv</Forename> <Surname>Tyler</Surname> </Name> <Name> <Forename>William</Forename> <MiddleName>Bradley</MiddleName> <Surname>Pitt</Surname> </Name> <Name> <Forename>Björk</Forename> </Name> <Name> <Forename>George</Forename> <Surname>Clooney</Surname> </Name> </Celebrity>