将数据库查询转换为XML

将数据库查询转换为XML

使用XML做数据源默认为XML存在。通常，让服务器通过数据库生成XML比较简单。下面是一些通用服务器脚本。

下面是一些起点。它们需要针对你的使用环境进行自定义。这些脚本将从数据表导出如下结构数据：

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

输出如下：

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

所有的数据使用CDATA包含，因为需要针对所有的数据类型。你如果确定不需要可以删掉。

注意: 如果使用自动生成的方式，确定所有的列值为文本型。一些数据库有类似‘二进制’数据类型，那是不可以转换为文字类的，这将导致脚本失效。

ColdFusion

说明: 这个版本遍历查询。根据你的需要编辑查询和XML数据节点名。

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

自动: 这个版本评估查询并自动根据列名建立节点。

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

还有CFTag 完成这些操作： http://www.cflib.org/udf.cfm?ID=648. Massimo Foti 为 Spry写了一个类似的标签: http://www.olimpo.ch/tmt/tag/spryxml/.

PHP

说明: 这个版本遍历查询。根据你的需要编辑查询和XML数据节点名。

<?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);
?>

 

自动: 这个版本评估查询并自动根据列名建立节点。

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

说明: 这个版本遍历查询。根据你的需要编辑查询和XML数据节点名。

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

 

自动: 这个版本评估查询并自动根据列名建立节点。

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

Copyright © 2006. Adobe Systems Incorporated. All rights reserved.Localization(Chinese Simplified) by Dorian Lee

http://www.sprycn.com/版权所有，转载请保留此信息。