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 ]]> ?)
<?php
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->openURI("php://output");
$xml->startDocument('1.0', 'UTF-8');
$xml->setIndent(true);
$xml->startElement("Envelope");
$xml->startElement("Body");
$xml->startElement("add_adverts");
while($row = mysql_fetch_array($query0)){
$name = $row["Name"];
$xml->startElement('advert');
$xml->startElement("Name");
$xml->writeCData ($name);
$xml->endElement();
$xml->endElement();
}
$xml->endElement();
$xml->endElement();
$xml->endElement();
header('Content-type: application/xml; charset=utf-8');
header('Content-type: text/xml');
$xml->flush();
?>
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:
<?php
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"?>
<response><foo>1</foo><bar>dfdf</bar></response>
*/
Why not switch to this and see what happens with your encoding issue?
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, 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.
