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" />
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" />