NULL Values
This section describes how MapForce handles NULL values in source and target components. To be able to use the xsi:nil="true" attribute in your XML file, you must specify the nillable="true" attribute for the relevant element(s) in your schema file. To find out more about the nillable and xsi:nil attributes, see the W3C Specification. Note that the xsi:nil attribute is not visible in a component's tree in the Mapping pane.
The subsections below describe some of the possible scenarios of mapping NULL values.
NULL values in XML components
This subsection discusses some of the possible scenarios of mapping elements with an xsi:nil="true" attribute.
Only the source element has xsi:nil="true"/Both source and target elements have xsi:nil="true"
This scenario has the following conditions:
•The connection is target-driven.
•The source element has an xsi:nil="true" attribute. The corresponding target element does not have this attribute.
•Alternatively, both source and target elements can have xsi:nil="true" attributes.
•The nillable="true" attributes must be set in the source and target schemas.
•The source and target element are of simple type.
In this case, the target element will have the xsi:nil="true" attribute in the output file, as shown in the sample output file below (highlighted in yellow).
<book id="7">
<author>Edgar Allan Poe</author>
<title>The Murders in the Rue Morgue</title>
<category xsi:nil="true"/>
<year>1841</year>
<OrderID id="213"/>
</book>
| Note: | If the nillable="true" attribute is not set in the target schema, the corresponding target element will be empty in the output. |
Only the target element has xsi:nil="true"
This scenario has the following conditions:
•The connection is target-driven.
•The source element does not have an xsi:nil="true" attribute.
•The corresponding target element has an xsi:nil="true" attribute.
•The source and target elements can be of simple or complex type.
In this case, the source element will overwrite the target element containing the xsi:nil="true" attribute. The example below shows a sample output file. The <genre> element includes the xsi:nil="true" attribute in the target element. However, this element has been overwritten at mapping runtime. Therefore, the <genre> element (highlighted in yellow) has Fiction in the output.
<publication>
<id>1</id>
<author>Mark Twain</author>
<title>The Adventures of Tom Sawyer</title>
<genre>Fiction</genre>
<year>1876</year>
<OrderID id="124"/>
</publication>
Complex-type source element/both complex-type elements have xsi:nil="true"
This scenario has the following conditions:
•The connection is target-driven.
•The source element is of complex type. In our example, the source element has an id="213" attribute and an xsi:nil="true" attribute. The corresponding target element is also of complex type and has an id="124" attribute, but does not have an xsi:nil="true" attribute.
•Alternatively, the source and target elements, both of which are of complex type, can have xsi:nil="true" attributes.
In this case, the source element will overwrite the target element (highlighted in yellow below). However, the xsi:nil="true" attribute will not be written to the output file automatically. To see the xsi:nil="true" attribute in the target element in the output file, use a copy-all connection.
<book id="7">
<author>Edgar Allan Poe</author>
<title>The Murders in the Rue Morgue</title>
<year>1841</year>
<OrderID id="213"/>
</book>
Useful functions
The following functions could help you check, replace, and assign NULL values:
•is-xsi-nil: Helps to check explicitly whether a source element has a xsi:nil attribute set to true.
•substitute-missing: Substitutes a NULL value in the source element with something specific.
•set-xsi-nil: Assigns xsi:nil="true" attribute to a target element. This works for target elements of simple and complex types.
•substitute-missing-with-xsi-nil: If there is content, it will be written to the target element; if there are any missing values, using this function will result in a target element with a xsi:nil="true" attribute in the output.
•Connecting the exists function to a source element with a NULL value returns true even though the element has no content.
Note that functions which generate xsi:nil cannot be passed through functions or components which only operate on values (such as the if-else function).
NULL values in database components
This subsection shows how NULL values are treated in database components.
Mapping NULL database fields to NULL elements
Target elements that receive NULL values from database fields are not created in the output automatically. To see such elements in the output, you need to (i) add nillable="true" attributes to the relevant target elements in the schema file and (ii) use the substitute-missing-with-xsi-nil function in the mapping. The example below shows how to handle NULL values in mappings with a source database component.
Applications table in DB Query pane
The sample mapping is located at the following path: Tutorial\DBNullToXML.mfd. For our example, we have chosen only one table (Application) from the Accounts database (see below).
To see the Application table, take the steps below:
•Open the DB Query pane.
•Select the Accounts database to see its structure in the Database Browser.
•Right-click the Application table and click Show in SQL Editor | SELECT.
•Click the 
(Execute Query) button. The Application table will appear in the Results tab.
To find out more about querying databases, see DB Query Pane.
Mapping
The Application table above shows that the second record has NULL values in the Description, Category, and URL fields. For illustration purposes, we will map almost all the columns directly to the corresponding target elements. For the URL column, we will use the substitute-missing-with-xsi-nil function so that the NULL value in the target element has an xsi:nil="true" attribute (see mapping below).
Output
The output file below shows that the first record from the table has been fully written to the output, whereas the second record has been written only partly. The NULL database values are absent from the output, except for the URL element. Since the URL element has the nillable="true" attribute in the schema file and we are using the substitute-missing-with-xsi-nil function, the URL element has now the xsi:nil="true" attribute in the output (highlighted yellow).
<Application>
<AppID>1</AppID>
<AppName>Altova MapForce</AppName>
<Description>Best data mapping tool!</Description>
<Category>IDE</Category>
<URL>https://www.altova.com/mapforce</URL>
</Application>
<Application>
<AppID>2</AppID>
<AppName>Notepad</AppName>
<URL xsi:nil="true"/>
</Application>
Mapping NULL elements to NULL database fields
When you map a NULL XML element to a database column, MapForce writes the NULL value to the corresponding database column. You can also use the set-null function if you want to set a database field to NULL. To find out more about database-related functions, see the DB library.