Description:
Export Resultset > Export As XML File... fails to export NULL values correctly.
The genreated XML file writes columns containing null like this: <columnname/>
According to the DTD (that is generated at the same time) this is not allowed.
How to repeat:
Run the following SQL queries:
CREATE TABLE test_null(id int AUTO_INCREMENT KEY, description text, value int);
-- Query OK, 0 rows affected (0.01 sec)
INSERT INTO test_null (description, value) VALUES ('2 values',2), ('1 value',null), (null,1), (null,null);
-- Query OK, 4 rows affected (0.00 sec)
-- Records: 4 Duplicates: 0 Warnings: 0
SELECT * FROM test_null;
-- +----+-------------+-------+
-- | id | description | value |
-- +----+-------------+-------+
-- | 1 | 2 values | 2 |
-- | 2 | 1 value | NULL |
-- | 3 | NULL | 1 |
-- | 4 | NULL | NULL |
-- +----+-------------+-------+
-- 4 rows in set (0.00 sec)
SHOW VARIABLES LIKE 'version';
-- +---------------+---------------------+
-- | Variable_name | Value |
-- +---------------+---------------------+
-- | version | 5.0.37-community-nt |
-- +---------------+---------------------+
-- 1 row in set (0.00 sec)
Run the SELECT query in MySQL Query Browser and export the resultset as XML.
You will end up with the following files:
DTD:
<!ELEMENT field (#PCDATA) >
<!ATTLIST field name CDATA #REQUIRED >
<!ELEMENT row (field)+ >
<!ELEMENT ROOT (row)+ >
XML:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE ROOT SYSTEM "test_null.dtd">
<ROOT>
<row>
<field name="id">1</field>
<field name="description">2 values</field>
<field name="value">2</field>
</row>
<row>
<field name="id">2</field>
<field name="description">1 value</field>
<value/>
</row>
<row>
<field name="id">3</field>
<description/>
<field name="value">1</field>
</row>
<row>
<field name="id">4</field>
<description/>
<value/>
</row>
</ROOT>
Suggested fix:
Either change the DTD to allow the way the xml writes null, but a better way would be to do change xml output (and the dtd) to mysqldump's xml output.
So instaid of:
<value/>
...it should be:
<field name="value" xsi:nil="true" />