Null columns are changed to default value when exporting/importing table data as XML Format

[Valektrum]

I'm using v4.0.15-beta with Access 2016.
I'm still trying to export my table data with the XML Format. However, I noticed again that the exported data is wrong.

I have a column named "Address" with the data type "Number", default value is 0 and the property "Required" is set to "No".
In some cases, I want this column to be empty. That's why Required is set to "No".

When exporting to XML, I get the following:

<?xml version="1.0"?>
<dataroot xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <MyTable>
    <SomeField>1</SomeField>
    <SomeOtherField>2</SomeOtherField>
  </MyTable>
</dataroot>

As you can see, the column "Address" is missing.

When I then try to import the data, my column "Address" will be filled with the default value instead of being empty.

It works properly when using the "Tab Delimited" format.

[joyfullservice]

Thanks for reporting this! I will have to do some testing to see if this is happening when saving to XML, or during the sanitizing/formatting of the XML.

[bclothier]

While I agree this looks like a bug on the surface and probably need fixing, I have to ask if the column is allowed to be empty, why would there be a default for 0? Seems more natural to have it default to null by having no default instead of 0.

NB: IIRC, Access for a while changed the default behavior where new numeric columns had null defaults but then reverted back to defaulting to 0 as it originally was, and we're back to removing the silly 0 default for nullable numeric columns.

[Valektrum]

That's a good point. Changing the default value to null does fix the issue. I would have to double check if the default 0 was there for a reason.

Ending up with different values after the "Build From Source" could still be problematic. I think the consistency of the data is very important.

[bclothier]

I absolutely agree that there'll be situations where this is legitimate which is why I prefaced the comment with the need to fix the reported issue. The change in Access' behaviors (I want to say between 2007? 2010? and 2013? 2016?) could have led to the inconsistency in the default value settings especially with the numeric columns which makes it even more important.

Thanks for sharing!