QUERY和CDATA输出使用中的MYSQL REPLACE仍会导致XML损坏

I have a PHP script that queries a MySQL database and displays that information in XML format output.

I have a troublesome column that I have no control over (I can only SELECT). This column is filled with character returns etc.

In the MySQL query, I have tried to use REPLACE on this column like this:

REPLACE(PropertyInformation, '
', '') AS PropertyInformation

In the PHP script I also wrap the exported XML in CDATA as I was told this could help, like this:

<Description><![CDATA[' . $PropertyInformation . ']]></Description>

I also form the XML like this in the script:

header("Content-Type: text/xml;charset=UTF-8");
echo '<?xml version="1.0" encoding="UTF-8"?>

The result is broken since your data is not UTF-8 even though you claim it to be (<?xml version="1.0" encoding="UTF-8"?>)

You need to convert your data to this format.

There are three ways to do that, either

1. convert your data in the database to be UTF-8, or
2. convert doing a select statement, or
3. convert in PHP the data to be UTF-8, leaving the data in the database as-is.

First option you would do by taking a dump of the database, issuing iconv conversion command to it and importing it back.

Second you would do with SELECT CONVERT(latin1column USING utf8) ...

Third you would again do with iconv, assuming your data would be ISO-8859-1: $converted = iconv("ISO-8859-1", "UTF-8", $text);