MySQL Bugs: #11830: Export Result to XML: Error in DTD

Bug #11830	Export Result to XML: Error in DTD
Submitted:	9 Jul 2005 6:35	Modified:	20 Sep 2005 10:18
Reporter:	Johann Flori-Himpel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:		OS:
Assigned to:	Vladimir Kolesnikov	CPU Architecture:	Any

Description:
An Example:

The generated dtd for "USER_PRIVILEGES" looks like

<!ELEMENT ROOT (row)+>
   <!ELEMENT row (GRANTEE,TABLE_CATALOG,PRIVILEGE_TYPE,IS_GRANTABLE)>
	<!ELEMENT GRANTEE (#PCDATA)>
	<!ELEMENT TABLE_CATALOG (#PCDATA)>
	<!ELEMENT PRIVILEGE_TYPE (#PCDATA)>
	<!ELEMENT IS_GRANTABLE (#PCDATA)>

This should describe the corresponding generated XML-File content:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE ROOT SYSTEM "errtable.dtd">
<ROOT>
    <row>
      <field name="GRANTEE">'root'@'localhost'</field>
      <TABLE_CATALOG/>
      <field name="PRIVILEGE_TYPE">SELECT</field>
      <field name="IS_GRANTABLE">YES</field>
    </row>
    <row>
      <field name="GRANTEE">'root'@'localhost'</field>
      <TABLE_CATALOG/>
      <field name="PRIVILEGE_TYPE">INSERT</field>
      <field name="IS_GRANTABLE">YES</field>
    </row>
</ROOT>

But the DTD does not allow an element "field".

How to repeat:
any xml-export from a query gives the same wrong result

Suggested fix:
Solution "Simple DTD":

  Build a dtd with this content:

    <!ELEMENT ROOT (row)+>
	  <!ELEMENT row (field+)>
		  <!ELEMENT field (#PCDATA)>
		  <!ATTLIST  field  name NMTOKEN #REQUIRED >

  And generate an xml in the "old" manner. 
  Only Null-fields then have to be displayed in the form
     <field name="TABLE_CATALOG"/>             (for example!!)

Solution "Complex DTD":

  Build the dtd just like you allways did,
  and generate an XML which fits this dtd.

  This would - for example - be:

  <?xml version="1.0" encoding="UTF-8"?>
  <!DOCTYPE ROOT SYSTEM "complextable.dtd">
  <ROOT>
      <row>
        <GRANTEE>'root'@'localhost'</GRANTEE>
        <TABLE_CATALOG />
        <PRIVILEGE_TYPE>SELECT</PRIVILEGE_TYPE>
        <IS_GRANTABLE>YES</IS_GRANTABLE>
      </row>
  </ROOT>

we encounter exactly the same problem, but we would prefer the second suggestion as preferred solution because when xml-file is formed in that way the imports in Excel and Access (and maybe other software tools also) are much easier!

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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

changed dtd