Using XML as data sources presumes the existence of XML. Often, it is easier to have the server create the XML from a database on the fly. Below are some scripts for common server models that do such a thing.
These are starting points. They will need to be customized for your particular scenario.
All these scripts will export the data from a database table with this structure:
The output of the manual scripts will look like:
<?xml version="1.0" encoding="utf-8" ?> <images> <image> <ID>1</ID> <album><![CDATA[ Family ]]></album> <path><![CDATA[ /family/us.jpg ]]></path> <description><![CDATA[ here goes the description ]]></description> <date><![CDATA[ 2006-11-20 10:20:00 ]]></date> </image> <image> <ID>2</ID> <album><![CDATA[ Work ]]></album> <path><![CDATA[ /work/coleagues.jpg ]]></path> <description><![CDATA[ here goes the description ]]></description> <date><![CDATA[ 2006-11-21 12:34:00 ]]></date> </image> </images>
These are all wrapped in CDATA because it is will work with all data types. They can be removed if you know you don't want them.
Note: If using the column auto-generating versions, ensure that all the column types are text. Some data bases have data type options like 'binary', that can't be converted to text. This will cause the script to fail.
ColdFusion
Manual: This version loops over a query. Edit the Query and XML node names to match your needs.
<cfsetting enablecfoutputonly="yes"> <cfsetting showdebugoutput="no"> <!--- Query the database and get all the records from the Images table ---> <cfquery name="rsImages" datasource="dsImages"> SELECT ID, AlbumName, ImagePath, ImageDescription, UploadDate FROM Images </cfquery> <!--- Send the headers ---> <cfheader name="Content-type" value="text/xml"> <cfheader name="Pragma" value="public"> <cfheader name="Cache-control" value="private"> <cfheader name="Expires" value="-1"> <cfsetting enablecfoutputonly="no"><?xml version="1.0" encoding="utf-8"?> <images> <cfoutput query="rsImages"> <image> <ID>#ID#</ID> <album><![CDATA[#AlbumName#]]></album> <path><![CDATA[#ImagePath#]]></path> <description><![CDATA[#ImageDescription#]]</description> <date><![CDATA[#UploadDate#]]></date> </image> </cfoutput> </images>
Automatic: This version evaluates the query and automatically builds the nodes from the column names
<cfsetting enablecfoutputonly="yes"> <cfsetting showdebugoutput="no"> <!--- Query the database and get all the records ---> <cfquery name="rsAll" datasource="dsImages"> SELECT * FROM images </cfquery> <cfset ColumnNames = ListToArray(rsAll.ColumnList)> <!--- Send the headers ---> <cfheader name="Content-type" value="text/xml"> <cfheader name="Pragma" value="public"> <cfheader name="Cache-control" value="private"> <cfheader name="Expires" value="-1"> <cfsetting enablecfoutputonly="no"><?xml version="1.0" encoding="utf-8"?> <root> <cfoutput query="rsAll"> <row> <cfloop from="1" to="#ArrayLen(ColumnNames)#" index="index"> <cfset column = LCase(ColumnNames[index])> <cfset value = rsAll[column][rsAll.CurrentRow]> <#column#><![CDATA[#value#]]></#column#> </cfloop> </row> </cfoutput> </root>
There is also a CFTag written to do this very thing: http://www.cflib.org/udf.cfm?ID=648. Massimo Foti also wrote a similar tag specifically for Spry: http://www.olimpo.ch/tmt/tag/spryxml/.
PHP
Manual: This version loops over a query. Edit the Query and XML node names to match your needs.
<?php $hostname_conn = "localhost"; $database_conn = "image_gallery"; $username_conn = "root"; $password_conn = "password"; $conn = mysql_pconnect($hostname_conn, $username_conn, $password_conn) or trigger_error(mysql_error(),E_USER_ERROR); ?> <?php // Query the database and get all the records from the Images table mysql_select_db($database_conn, $conn); $query_rsImages = "SELECT ID, AlbumName, ImagePath, ImageDescription, UploadDate FROM images"; $rsImages = mysql_query($query_rsImages, $conn) or die(mysql_error()); $row_rsImages = mysql_fetch_assoc($rsImages); $totalRows_rsImages = mysql_num_rows($rsImages); // Send the headers header('Content-type: text/xml'); header('Pragma: public'); header('Cache-control: private'); header('Expires: -1'); ?><?php echo('<?xml version="1.0" encoding="utf-8"?>'); ?> <images> <?php if ($totalRows_rsImages > 0) { // Show if recordset not empty ?> <?php do { ?> <image> <ID><?php echo $row_rsImages['ID']; ?></ID> <album><![CDATA[<?php echo $row_rsImages['AlbumName']; ?>]]></album> <path><![CDATA[<?php echo $row_rsImages['ImagePath']; ?>]]></path> <description><![CDATA[<?php echo $row_rsImages['ImageDescription']; ?>]]></description> <date><![CDATA[<?php echo $row_rsImages['UploadDate']; ?>]]></date> </image> <?php } while ($row_rsImages = mysql_fetch_assoc($rsImages)); ?> <?php } // Show if recordset not empty ?> </images> <?php mysql_free_result($rsImages); ?>
Automatic: This version evaluates the query and automatically builds the nodes from the column names.
<?php $hostname_conn = "localhost"; $database_conn = "image_gallery"; $username_conn = "root"; $password_conn = "password"; $conn = mysql_pconnect($hostname_conn, $username_conn, $password_conn) or trigger_error(mysql_error(),E_USER_ERROR); ?> <?php // Query the database and get all the records from the Images table mysql_select_db($database_conn, $conn); $query_rsAll = "SELECT * FROM images"; $rsAll = mysql_query($query_rsAll, $conn) or die(mysql_error()); $row_rsAll = mysql_fetch_assoc($rsAll); $totalRows_rsAll = mysql_num_rows($rsAll); // Send the headers header('Content-type: text/xml'); header('Pragma: public'); header('Cache-control: private'); header('Expires: -1'); ?><?php echo('<?xml version="1.0" encoding="utf-8"?>'); ?> <root> <?php if ($totalRows_rsAll > 0) { // Show if recordset not empty ?> <?php do { ?> <row> <?php foreach ($row_rsAll as $column=>$value) { ?> <<?php echo $column; ?>><![CDATA[<?php echo $row_rsAll[$column]; ?>]]></<?php echo $column; ?>> <?php } ?> </row> <?php } while ($row_rsAll = mysql_fetch_assoc($rsAll)); ?> <?php } // Show if recordset not empty ?> </root> <?php mysql_free_result($rsAll); ?>
ASP
Manual: This version loops over a query. Edit the Query and XML node names to match your needs.
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%> <% Dim MM_conn_STRING MM_conn_STRING = "dsn=image_gallery;uid=xxxx;pwd=xxxx" %> <% Dim rsImages Dim rsImages_cmd Dim rsImages_numRows ' Query the database and get all the records from the Images table Set rsImages_cmd = Server.CreateObject ("ADODB.Command") rsImages_cmd.ActiveConnection = MM_conn_STRING rsImages_cmd.CommandText = "SELECT ID, AlbumName, ImagePath, ImageDescription, UploadDate FROM images" rsImages_cmd.Prepared = true Set rsImages = rsImages_cmd.Execute ' Send the headers Response.ContentType = "text/xml" Response.AddHeader "Pragma", "public" Response.AddHeader "Cache-control", "private" Response.AddHeader "Expires", "-1" %><?xml version="1.0" encoding="utf-8"?> <images> <% While (NOT rsImages.EOF) %> <image> <ID><%=(rsImages.Fields.Item("ID").Value)%></ID> <album><![CDATA[<%=(rsImages.Fields.Item("AlbumName").Value)%>]]></album> <path><![CDATA[<%=(rsImages.Fields.Item("ImagePath").Value)%>]]></path> <description><![CDATA[<%=(rsImages.Fields.Item("ImageDescription").Value)%>]]></description> <date><![CDATA[<%=(rsImages.Fields.Item("UploadDate").Value)%>]]></date> </image> <% rsImages.MoveNext() Wend %> </images> <% rsImages.Close() Set rsImages = Nothing %>
Automatic: This version evaluates the query and automatically builds the nodes from the column names.
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%> <% Dim MM_conn_STRING MM_conn_STRING = "dsn=image_gallery;uid=xxxx;pwd=xxxx" %> <% Dim rsAll Dim rsAll_cmd Dim rsAll_numRows ' Query the database and get all the records from the Images table Set rsAll_cmd = Server.CreateObject ("ADODB.Command") rsAll_cmd.ActiveConnection = MM_conn_STRING rsAll_cmd.CommandText = "SELECT * FROM Images" rsAll_cmd.Prepared = true Set rsAll = rsAll_cmd.Execute ' Send the headers Response.ContentType = "text/xml" Response.AddHeader "Pragma", "public" Response.AddHeader "Cache-control", "private" Response.AddHeader "Expires", "-1" %><?xml version="1.0" encoding="utf-8"?> <root> <% While (NOT rsAll.EOF) %> <row> <% For each field in rsAll.Fields column = field.name %> <<%=column%>><![CDATA[<%=(rsAll.Fields.Item(column).Value)%>]]></<%=column%>> <% Next %> </row> <% rsAll.MoveNext() Wend %> </root> <% rsAll.Close() Set rsAll = Nothing %>