### Converting Database queries to XML

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:

- ID: integer, primary key, autoincrement
- AlbumName: text(255)
- ImagePath: text(255)
- ImageDescription: text(2000)
- UploadDate: datetime

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
    %>