将表/查询存储为标准的文件

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

/*--存为XML
 将表/查询存储为标准的XML文件
--*/

/*--调用示例
 --用SQL用winows身份验证的情况
 execp_savexml@sql='地区资料',@fname='c:/地区资料.xml'

 --用指定的用户
 execp_savexml@sql='地区资料',@fname='c:/地区资料.xml',@userid='sa'
--*/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[p_savexml]')andOBJECTPROPERTY(id,N'IsProcedure')=1)
dropprocedure[dbo].[p_savexml]
GO

createprocp_savexml
@sqlvarchar(8000),     --要保存的表/视图/SQL查询语句
@fnamevarchar(1000)='c:/tmp.xml', --保存的XML文件名
@useridvarchar(50)='',    --用户名,如果为nt验证方式,则为''
@passwordvarchar(50)=''   --密码
as
declare@errint,@srcvarchar(255),@descvarchar(255)
declare@objint,@constrvarchar(1000)
ifisnull(@userid,'')=''
 set@constr='Provider=SQLOLEDB.1;IntegratedSecurity=SSPI;PersistSecurityInfo=False;InitialCatalog='
  +db_name()+';DataSource='+@@servername
else
 set@constr='Provider=SQLOLEDB.1;PersistSecurityInfo=True;'
  +'UserID='+@userid+';Password='+isnull(@password,'')
  +';InitialCatalog='+db_name()+';DataSource='+@@servername

exec@err=sp_oacreate'adodb.recordset',@objout
if@err<>0gotolberr

exec@err=sp_oamethod@obj,'open',null,@sql,@constr
if@err<>0gotolberr

set@sql='del'+@fname
execmaster..xp_cmdshell@sql,no_output
exec@err=sp_oamethod@obj,'save',null,@fname,1
if@err<>0gotolberr

exec@err=sp_oadestroy@obj
return

lberr:
 execsp_oageterrorinfo0,@srcout,@descout
 selectcast(@errasvarbinary(4))as错误号
  ,@srcas错误源,@descas错误描述
go

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>