Bug #28080 xml export fails to export null properly
Submitted: 24 Apr 2007 19:53 Modified: 27 Apr 2007 15:22
Reporter: Johan Ekbäck Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Query Browser Severity:S3 (Non-critical)
Version:1.2.10/1.2.11a OS:Windows
Assigned to: Alfredo Kojima CPU Architecture:Any
Tags: dtd, export, null, XML

[24 Apr 2007 19:53] Johan Ekbäck
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" />
[24 Apr 2007 22:41] MySQL Verification Team
Thank you for the bug report.
[27 Apr 2007 15:22] Alfredo Kojima
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html