MySQL to XML file

Question

I am trying to get MySQL database into an xml file; here is my code:

  <?php

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

    include 'dbc.php';

      $query = "SELECT * FROM airports LIMIT 50";
      $result = mysql_query($query, $link)
      or die('Error querying database.');

  $xml = new SimpleXMLElement('<xml/>');

  while($row = mysql_fetch_assoc($result)) {
   $draw = $xml->addChild('draw');
   $draw->addChild('ident',htmlentities(iconv("UTF-8", "ISO-8859-1//IGNORE",$row['ident'])));
   $draw->addChild('name',htmlentities(iconv("UTF-8", "ISO-8859-1//IGNORE",$row['name'])));
  }

  mysql_close($link);
  $fp = fopen("links2.xml","wb");
  fwrite($fp,$xml->asXML());
  fclose($fp);

Here is the error Im getting:

  XML Parsing Error: no element found
  Location: /sql2xml2.php
  Line Number 1, Column 2: 
  -^

What am I doing wrong???

For what purpose when you can use [mysqldump](http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html) — OMG Ponies, Dec 09 '12 at 06:15
Im trying to build a live search on my site. Im trying to write the code so it can read my database like an xml file — DJ Howarth, Dec 09 '12 at 06:17
You want to export the DB to XML, to search against? When you can search against the DB directly... — OMG Ponies, Dec 09 '12 at 06:18
I can, but its not a live search. I want results to appear as youre typing in the search box — DJ Howarth, Dec 09 '12 at 06:19

score 1 · Accepted Answer · edited May 23 '17 at 12:03

1

Your XML is considered invalid in your XML reader because of the thrown warning, thus the XML Parsing Error: junk after document element issue.

As for the warning itself, you need to escape special entities (namely &, < and > in your content when adding it like that (using str_replace usually works well for only those 3 when it comes to XML, htmlentities may yield undesired effects, unless you supply PHP 5.4's ENT_XML1 mode).

Refer to a related answer for more information of why this happens.

edited May 23 '17 at 12:03

Community

1
1

answered Dec 09 '12 at 06:26

soulseekah

8,770
3
53
58

So like this?: $draw->addChild(htmlentities('ident',$row['ident'])); – DJ Howarth Dec 09 '12 at 06:31
Escape the content, not the tag: `$draw->addChild('ident', htmlentities($row['ident']));` – soulseekah Dec 09 '12 at 06:32
Ok, were making progress. Now Im getting this error: Warning: fopen(xml/links2.xml) [function.fopen]: failed to open stream: No such file or directory in /home/wwwfligh/public_html/sql2xml2.php on line 20
^ I saved a blank file named links2.xml; is this not correct? – DJ Howarth Dec 09 '12 at 06:38
Does the `xml` directory exist? Is it writable by the PHP user? Probably the latter, try setting permissions for the file to `0666` or `a+rw`; the `xml` directory has also got to have `+x` traversal permissions for the PHP user. Mind you, this is a general file permissions issue and not related to any of the original XML issues. – soulseekah Dec 09 '12 at 06:41
I thought this file was creating the xml directory? Im not sure what youre asking. – DJ Howarth Dec 09 '12 at 06:43
`fopen` does not create parent directories. – soulseekah Dec 09 '12 at 06:44
What do I need to do to create the parent directories? – DJ Howarth Dec 09 '12 at 06:45
I changed it to: $fp = fopen("links2.xml","wb"); that fixed that error...Now I get this error: Warning: SimpleXMLElement::asXML() [simplexmlelement.asxml]: string is not in UTF-8 in /home/wwwfligh/public_html/sql2xml2.php on line 21
– DJ Howarth Dec 09 '12 at 06:47
Convert it to UTF-8; look around http://stackoverflow.com/questions/6013863/saving-xml-created-with-domdocument-gives-the-error-domdocumentsave-string and try [`uft8_encode`](http://de3.php.net/manual/en/function.utf8-encode.php) or `iconv`, better yet your database has to store the data in UTF8 from the very beginning. – soulseekah Dec 09 '12 at 06:50
Thanks. Now I get this error: XML Parsing Error: no element found...guess ill do some more research... – DJ Howarth Dec 09 '12 at 06:53

score 1 · Answer 2 · answered Oct 25 '14 at 20:17

1

If you want just to export MySQL database to local XML file you can use mysqldump tool:

mysqldump --xml -u username -p databasename [tablename] > filename.xml

answered Oct 25 '14 at 20:17

Miroslav Oprsteny

21
2

score 0 · Answer 3 · answered Dec 09 '12 at 09:38

Got it to work with this code:

 <?
 header("content-type:text/xml");
 function getXML($query="SELECT * FROM airports limit 50")
 {
    include 'dbc.php';

     $result = mysql_query($query, $link)
     or die('Error querying database.');

 $columns="";
 echo "<xml>\n";
 while($row=mysql_fetch_assoc($result))
 {
    $columns.="\t<airport>\n";
    foreach($row as $key => $value)
        {
        $value = htmlentities(iconv("UTF-8", "ISO-8859-1//TRANSLIT",$value));
        $value = htmlentities(iconv("UTF-8", "ISO-8859-1//IGNORE",$value));
        $columns.="\t\t<$key>$value</$key>\n";
    }
$columns.="\t</airport>\n";
 }
 echo $columns;
 echo "</xml>\n";
 }

 getXML();

 ?>

MySQL to XML file

3 Answers3