How to Display XML From a Database
XML is a markup language that describes data in a standard way. Typically, data are stored in a database and can be retrieved as XML or transformed into XML, often in order to use in Web pages. Although many ways exist to display XML, its native format can be rather cryptic. Fortunately, XSL style sheets allow Web developers to display XML in ways that take advantage of the rich graphical nature of the World Wide Web.
Other People Are Reading
Things You'll Need
- Web server with PHP installed
- MySQL database with appropriate permissions
- Web browser
- Text editor
Instructions
-
-
1
Use a text editor to create a new file named "db_2_xml.php." This file will contain php code that connects to a MySQL database and retrieves the XML values to be displayed. Replace the "$host," "$user," "$pw" and "$database" variables in the code with appropriate values from your specific setup. This code assumes a MySQL database table named groceries that contains a single record with the data name=apple and type=fruit. Enter the code and save db_2_xml.php.
<?php
$host = "host_value";
$user = "user_value";
$pw = "password_value";
$database = "database_value";
$my_db = mysql_connect($host, $user, $pw);
mysql_select_db($database, $my_db);
?>
-
2
Edit db_2_xml.php and add header information that indicates that the file should display as XML rather than as php when the file is opened and the script runs.
<?php
header("Content-type: text/xml");
$host = "host_value";
$user = "user_value";
$pw = "password_value";
$database = "database_value";
$my_db = mysql_connect($host, $user, $pw);
mysql_select_db($database, $my_db);
?>
-
-
3
Continue editing db_2_xml.php. Add code that requests and retrieves the data from the groceries table, formats it as XML and writes it to a variable named $xml_output. Use the php echo command to write out the XML. Save db_2_xml.php.
<?php
header("Content-type: text/xml");
$host = "host_value";
$user = "user_value";
$pw = "password_value";
$database = "database_value";
$my_db = mysql_connect($host, $user, $pw);
mysql_select_db($database, $my_db);
$query = "SELECT * FROM groceries";
$result = mysql_query($query, $my_db) or die("Data not found.");
$xml_output = "<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>";
$xml_output .= "<groceries>\n";
for($x = 0 ; $x < mysql_num_rows($result) ; $x++){
$row = mysql_fetch_assoc($result);
$xml_output .= "\t<food>\n";
$xml_output .= "\t\t<name>" . $row['name'] . "</name>\n";
$xml_output .= "\t\t<type>" . $row['type'] . "</type>\n";
$xml_output .= "\t</food>\n";
}
$xml_output .= "</groceries>";
echo $xml_output;
?>
-
4
Create a new text file and save it as "fruit_xsl.xsl." This file is the XSL style sheet that will be used to display the XML created in db_2_xml.php. Enter some HTML tags to format the final display, such as a header and table elements. Save fruit_xsl.xsl in the same location as db_2_xml.php.
<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<html>
<body>
<h1>Grocery List:</h1>
<h2>FOOD:</h2>
<table border="1">
<tr bgcolor="yellow">
<th>name</th>
<th>type</th>
</tr>
<xsl:for-each select="groceries/food">
<tr>
<td><xsl:value-of select="name"/></td>
<td><xsl:value-of select="type"/></td>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>
-
5
Open db_2_xml.php and add the fruit_xsl.xsl style sheet to the file.
<?php
header("Content-type: text/xml");
$host = "host_value";
$user = "user_value";
$pw = "password_value";
$database = "database_value";
$my_db = mysql_connect($host, $user, $pw);
mysql_select_db($database, $my_db);
$query = "SELECT * FROM groceries";
$result = mysql_query($query, $my_db) or die("Data not found.");
$xml_output = "<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>";
$xml_output .= "<?xml-stylesheet type=\"text/xsl\" href=\"fruit_xsl.xsl\"?>";
$xml_output .= "<groceries>\n";
for($x = 0 ; $x < mysql_num_rows($result) ; $x++){
$row = mysql_fetch_assoc($result);
$xml_output .= "\t<food>\n";
$xml_output .= "\t\t<name>" . $row['name'] . "</name>\n";
$xml_output .= "\t\t<type>" . $row['type'] . "</type>\n";
$xml_output .= "\t</food>\n";
}
$xml_output .= "</groceries>";
echo $xml_output;
?>
-
6
Open db_2_xml.php from the Web server using a Web browser. The database data will be printed as XML and displayed using the styles created in the XSL style sheet.
-
1
Tips & Warnings
XML can be written directly to the Web browser, displayed using cascading style sheets or by using the XML DOM.
Many commercial databases support XQuery, which can be embedded directly into HTML.
XML files can be created and loaded into Web pages using php's DOM extensions.
Be sure to use the escape character ('\') in php code when the output includes double quotes.
Remove all leading and trailing spaces before and after the php code delimiters to avoid errors.
Note that Internet Explorer 5 is not compatible with the official W3C XSL recommendation.
References
Resources
- Photo Credit binary - o2 image by samantha grandy from Fotolia.com
