Mysql to xml using php

Not sure if this is the right forum.

I am using this code to take data from the sql database and convert it into an xml file. However some of the row results from the mysql database have characters that cause errors with the xml file and it doesn’t close the elements. For example if there is an apostrophe or euro sign. I thought the using writecdata would sort it but it still failed (also is the resulting xml meant to show the element as <![CDATA[ name ]]> ?)

include ("db.php");
global $dbServer, $dbUser, $dbPass, $dbName;
$cxn = @ConnectToDb($dbServer, $dbUser, $dbPass, $dbName);
$sql_0 = "SELECT Name FROM properties";
$query0 = mysql_query($sql_0) or die("Cannot query the database.<br>" . mysql_error());

$xml = new XMLWriter();
$xml->startDocument('1.0', 'UTF-8');


while($row = mysql_fetch_array($query0)){
$name = $row["Name"];

  $xml->writeCData ($name);


header('Content-type: application/xml; charset=utf-8');
header('Content-type: text/xml');

Any help or pointers would be appreciated.


Well i’d eliminate that space between writeCData and the (.

You may need to wrap your data in utf8_encode(); Your server may not be using UTF-8 as its standard charset.

Thanks for the reply, I have removed the space but its made no difference. How do I wrap the data in utf8_encode?

quite literally.

Yay, that’s it working now, thanks very much :slight_smile:

Is there a need for Cdata? My results are coming out like this

<![CDATA[ Dunroamin ]]>

Which doesn’t seem right or is that valid XML?

This may sound like a stupid question, while I am using php to create the xml output, what should the file extension be? php or xml? What if I wanted it as xml?

Your file extension is php, its processing and running in PHP. Your header() function takes care of making sure that whoever is reading your page, knows its being sent in XML.

Did you not like SimpleXMLElement and DOM? I’ve never heard of XMLWriter


Its just an example I found and I have adapted it to work.

I have answered my own question regarding making an xml file by used fopen, fwrite and fclose. I take it to keep the xml file up to date I would need to set up a cron job to run the script?


I think you’ve got the wrong idea. You can server the xml file up dynamically, you should never have to write to a file and then read from it again UNLESS you want to prevent a db connection on every connection and update the data on an interval of your choosing, through a cron job.

If you want to serve dynamically, it should be pretty straight forward:

header('Content-type: text/xml');

$result = array('foo' => 1, 'bar' => "dfdf"); //fetch result from db to $result (get rid of deprecated mysql_* extension and use PDO) but heres a fake result

$xml = new SimpleXMLElement("<response></response>");

foreach($result as $column => $value) {
    $xml->addChild($column, $value);

print $xml->saveXML();

<?xml version="1.0"?>

Why not switch to this and see what happens with your encoding issue?

OK I will give that a go. Thanks.

Just looking at your example, surely if I wanted to see the result of that script I would go to the url with an php extension eg mysqltoxml.php but what if I needed mysqltoxml.xml though?

Apparently I exited my tab without posting my response. Let’s go roll back to what you are actually looking to accomplish. Are you trying to load a page and it gives you a physical xml file? Where are you hoping to retrieve this xml payload from? Normally, the client (where you are retrieving the xml from) will do a curl call and parse it from there.

Right, I need to provide an xml file with an .xml extension to another company so that the properties can be displayed on their website.

right, setting the header to text/xml does this, it just happens that your browser opens it up to be viewed in your browser. You can save the web page and it will automatically be an xml file. Exactly the same as when you open a PDF online. You know its a PDF, its just that the browser is handling it for you and you can save the pdf from there if you want.