/*--存为XML
将表/查询存储为标准的XML文件
--邹建 2003.12--*/
/*--调用示例
--用SQL用winows身份验证的情况
exec p_savexml @sql= '地区资料 ',@fname= 'c:/地区资料.xml '
--用指定的用户
exec p_savexml @sql= '地区资料 ',@fname= 'c:/地区资料.xml ',@userid= 'sa '
--*/
if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[p_savexml] ') and OBJECTPROPERTY(id, N 'IsProcedure ') = 1)
drop procedure [dbo].[p_savexml]
GO
create proc p_savexml
@sql varchar(8000),--要保存的表/视图/SQL查询语句
@fname varchar(1000)= 'c:/tmp.xml ',--保存的XML文件名
@userid varchar(50)= ' ',--用户名,如果为nt验证方式,则为 ' '
@password varchar(50)= ' '--密码
as
declare @err int,@src varchar(255),@desc varchar(255)
declare @obj int,@constr varchar(1000)
if isnull(@userid, ' ')= ' '
set @constr= 'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog= '
+db_name()+ ';Data Source= '+@@servername
else
set @constr= 'Provider=SQLOLEDB.1;Persist Security Info=True; '
+ 'User ID= '+@userid+ ';Password= '+isnull(@password, ' ')
+ ';Initial Catalog= '+db_name()+ ';Data Source= '+@@servername
exec @err=sp_oacreate 'adodb.recordset ',@obj out
if @err <> 0 goto lberr
exec @err=sp_oamethod @obj, 'open ',null,@sql,@constr
if @err <> 0 goto lberr
set @sql= 'del '+@fname
exec master..xp_cmdshell @sql,no_output
exec @err=sp_oamethod @obj, 'save ',null,@fname,1
if @err <> 0 goto lberr
exec @err=sp_oadestroy @obj
return
lberr:
exec sp_oageterrorinfo 0,@src out,@desc out
select cast(@err as varbinary(4)) as 错误号
,@src as 错误源,@desc as 错误描述
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,##T
原帖地址:http://topic.csdn.net/t/20060513/10/4748644.html
SQL数据转换成xml文件
最新推荐文章于 2022-08-16 22:03:31 发布