converting MySQL queries to xml

There frequently arises a need to return mySQL query results in xml. Maybe you need to send the xml data to the browser or you want to use it as a xml request to a web service; whatever the application, the following function will return the result of a sql query in xml format.

The function accepts 3 parameters. The first is the mysql query result resource, the second is the name of the root element and the third the name of the first child of the root. All the fields from the mySQL table will be sub elements of this child.

The function is shown below:

/**
 * @param mysql_resource - $queryResult - mysql query result
 * @param string - $rootElementName - root element name
 * @param string - $childElementName - child element name
 */
function sqlToXml($queryResult, $rootElementName, $childElementName)
{ 
    $xmlData = "<?xml version=\"1.0\" encoding=\"ISO-8859-1\" ?>\n"; 
    $xmlData .= "<" . $rootElementName . ">";
 
    while($record = mysql_fetch_object($queryResult))
    { 
        /* Create the first child element */
        $xmlData .= "<" . $childElementName . ">";
 
        for ($i = 0; $i < mysql_num_fields($queryResult); $i++)
        { 
            $fieldName = mysql_field_name($queryResult, $i); 
 
            /* The child will take the name of the table column */
            $xmlData .= "<" . $fieldName . ">";
 
            /* We set empty columns with NULL, or you could set 
                it to '0' or a blank. */
            if(!empty($record->$fieldName))
                $xmlData .= $record->$fieldName; 
            else
                $xmlData .= "null"; 
 
            $xmlData .= "</" . $fieldName . ">"; 
        } 
        $xmlData .= "</" . $childElementName . ">"; 
    } 
    $xmlData .= "</" . $rootElementName . ">"; 
 
    return $xmlData; 
}

How to use?
For example if we have a ‘company’ table with the following three fields:
id, companyid, name.

You can use the above function in the following manner to convert the table rows into xml;

.
.
/* Sql query */
$result = mysql_query("SELECT * from company");
 
/* If you want to process the returned xml rather than send it
    to the browser, remove the below line.
*/
header("Content-Type: application/xml");
echo sqlToXml($result, "companies", "company");

which returns the following xml.

<?xml version="1.0" encoding="ISO-8859-1" ?>
<companies>
	<company>
		<id>1</id>
		<companyid>1</companyid>
		<name>microsoft</name>
        </company>
.
.
.
</companies>

8 thoughts on “converting MySQL queries to xml

  1. This is great – thank you.

    I was wondering where the best place to sanitize the data would be? by that I mean replace any XML special characters like & with & before outputting them in XML.

    Oh – and the example works perfectly with MS SQL as well – as long as you change the function names:

    mysql_fetch_object to mssql_fetch_object
    mysql_num_fields to mssql_num_fields
    mysql_field_name to mssql_field_name

  2. Nice code, I also made my version of sql2xml, which is in production for several years … In combination with APC, it gives a good platform to build a fast Web site. Function accepts two parameters: first parameter is SQL and the second is hierarchy (optional). Hierarchy describes XML structure … Hope you will find interesting informations.
    Bye!

  3. Sanitization was the first thing to my mind, too. I was expecting a built-in, compiled function. Doh.

    This function looks clever and useful but it’s not for me at all.

  4. Thank you for such a lovely tutorial.
    I have problem. I want output in this XML format.

    What will I need to change to get the output in this forrmat??

  5. Hello, if you are using mysqli instead of mysql statements, you have to change the method to the following:

    /**
    * @param mysql_resource – $queryResult – mysql query result
    * @param string – $rootElementName – root element name
    * @param string – $childElementName – child element name
    */
    function sqlToXml($queryResult, $rootElementName, $childElementName)
    {
    $xmlData = “\n”;
    $xmlData .= “”;

    while($record = mysqli_fetch_object($queryResult))
    {
    /* Create the first child element */
    $xmlData .= “”;

    for ($i = 0; $i name;

    /* The child will take the name of the table column */
    $xmlData .= “”;

    /* We set empty columns with NULL, or you could set
    it to ‘0’ or a blank. */
    if(!empty($record->$fieldName))
    $xmlData .= $record->$fieldName;
    else
    $xmlData .= “null”;

    $xmlData .= “”;
    }
    $xmlData .= “”;
    }
    $xmlData .= “”;

    return $xmlData;
    }

Comments are closed.