bcp表数据存为xml.sql

sqlserver 数据库编程的绝好脚本

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

/*--将表中的数据存储为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 '+@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 '
	+@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