将表中的数据存储为xml文档（Sql 2000）
drop procedure [dbo].[p_savexml]
GO
/*--将表中的数据存储为xml文档
--邹建 2005.04(引用请保留此信息)--*/
/*--调用示例
exec p_savexml 'sysobjects'
--*/
go
create proc p_savexml
@tbname sysname
as
declare @s nvarchar(4000)
select @s='',@tbname=quotename(@tbname)
select @s=@s+N'
'+quotename(name,'<>')
+N'''+rtrim(cast('+quotename(name)+' as varchar(8000)))+'''
+quotename('/'+name,'<>')
from syscolumns
where id=object_id(@tbname)
order by colid
print @s
exec(N'
select id=identity(int,1,1),value='''+@s+'''
into ##t from [email='+@tbname
set @s=@@rowcount
set identity_insert ##t on
insert ##t(id,value)
--select -3,N'<?xml version=1.0 ?>' union all
select -2,N'<data>' union all
select -1,N'<detail>' union all
select 0,N'<count>'+@s+'</count>' union all
select @s+1,N'</detail>' union all
select @s+2,N'</data>'
set @s='bcp "select case when id between 1 and '
[email=+@s
+' then N''<row''+rtrim(id)+N''>''+value+N''</row''+rtrim(id)+N''>'' else value end from ##t order by id'
+'" queryout "c:\a.xml" /T /c'
select * from ##t
exec master..xp_cmdshell @s
drop table ##t
go
--测试
--测试数据
create table tx(a int primary key,b varchar(200),c varchar(200))
insert into tx(a,b,c)values(1,'aaa','bbb')
insert into tx(a,b,c)values(2,'ccc','ddd')
insert into tx(a,b,c)values(3,'eee','fff')
insert into tx(a,b,c)values(4,'ggg','hhh')
insert into tx(a,b,c)values(5,'iii','jjj')
go
--导出XML文件处理
create table ##t(re nvarchar(4000))
insert ##t
select re='<?xml version="1.0" encoding="gb2312"?>'
union all select '<table name="tx">'
union all
select space(4)+'<record a="'+cast(a as varchar)+'">
'+space(8)+'<b>'+isnull(b,'')+'</b>
'+space(8)+'<c>'+isnull(c,'')+'</c>
'+space(4)+'</record>'
from tx
union all select '</table>'
--导出XML(文件名:c:\a.xml),注意参数大小写
exec master..xp_cmdshell 'bcp ##t out "c:\a.xml" /P"" /c'
go
--删除测试
drop table tx
/*--生成的xml文件内容
<?xml version="1.0" encoding="gb2312"?>
<table name="tx">
<record a="1">
<b>aaa</b>
<c>bbb</c>
</record>
<record a="2">
<b>ccc</b>
<c>ddd</c>
</record>
<record a="3">
<b>eee</b>
<c>fff</c>
</record>
<record a="4">
<b>ggg</b>
<c>hhh</c>
</record>
<record a="5">
<b>iii</b>
<c>jjj</c>
</record>
</table>
--*/
利用XMLHTTP 从其他页面获取数据
我们在编写ASP代码的时候，大家都知道可以通过post或者get获得form表单的数据，那么我们如何直接获得其他页面上的数据呢？这就要借助xmlhttp协议了。xmlhttp是xmldom技术的一部分。
下面的代码就是一个很简单的例子，我们利用xmlhttp技术，把http://www.codetoad.com/
<%
Dim objXMLHTTP, xml
Set xml = Server.CreateObject("Microsoft.XMLHTTP")
xml.Open "GET", "http://www.dnxh.org/
' Pull the data from the web page
xml.Send
Response.write "Here's the html we now have in our xml object"
Response.write "<BR><BR><BR>"
Response.Write "<xmp>"
Response.Write xml.responseText
Response.Write "</xmp>"
Response.write "<BR><BR><BR>"
Response.write " Now here's how the page looks:<BR><BR>"
Response.Write xml.responseText
Set xml = Nothing
%>
XMLHTTP下载远程数据输出到浏览器
--------------------------------------------------------------------------------
利用xmlhttp对象获取远程的数据，然后用二进制输出到客户浏览器，让客户下载数据,此例从某一远程服务器获取一个压缩包，并且输出到浏览器提供客户下载.
<%
Response.Buffer = True
Dim objXMLHTTP, xml
Set xml = Server.CreateObject("Microsoft.XMLHTTP") '创建对象
xml.Open "GET","http://www.4guysfromrolla.com/webtech/code/mitchell-pres.zip",False
xml.Send '发送请求
Response.AddHeader "Content-Disposition", "attachment;filename=mitchell-pres.zip" '添加头给这个文件
Response.ContentType = "application/zip" '设置输出类型
Response.BinaryWrite xml.responseBody ’输出二进制到浏览器
Set xml = Nothing
%>
