/*

'bcp' 不是內部或外部命令，也不是可運行的程序？

看看在C:Program FilesMicrosoft SQL Server80ToolsBinn里面有沒有bcp.exe這個文件然后看看path里面加C:Program FilesMicrosoft SQL Server80ToolsBinn這個目錄沒有.

右鍵我的電腦--屬性--高級--環境變量--在系統變量中找到path--編輯

在變量值的最后加上:

C:Program FilesMicrosoft SQL Server80ToolsBINN

*/

/**********************************************************************存儲過程: P_SaveXML功能:;;;生成XML文件參數:;;;表名返回:;;;指定目錄的XML文件調用方法: 傳遞中間表TB_TABLECOLUMN_T存在的表名 exec P_ZehuaSavexml 'WQ_STINFO_B,WQ_PHY_D','D:xx.xml' 最后更改日期: 2005-8-17聯系方式:;[email protected]備注:; 根據中間表生成符合標準格式的XML文件www.mypchelp.cn**********************************************************************/

/*exec P_ZehuaSavexml 'WQ_STINFO_B,WQ_PHY_D','D:xx.xml'*/gocreate proc P_ZehuaSavexml@TableCodeS varchar(8000),@fname varchar(1000)='c:tmp.xml' --默認保存的XML文件地址asdeclare @sql nvarchar(200),@sql1 varchar(400)declare @cnt int,@Table_Name varchar(20)declare @i int,@TableCode varchar(20)declare @err int,@src varchar(255),@desc varchar(255)create table ##t(re nvarchar(4000))insert ##tselect Re='<?xml version='1.0' encoding='gb2312'?>'union all select '<ZehuaTableInfoXml>'set @i=charindex(',',@TableCodeS)while @i>0beginset; @TableCode=left(@TableCodeS,@i-1); set @sql=''set @sql=N'select @cnt=count(*),@Table_Name=max(Table_Name) from 'set @sql=@sql+N'('set @sql=@sql+N'select top 100 percent * from TB_TABLECOLUMN_T where 'set @sql=@sql+N'TABLE_CODE='''+@TableCode+''' and COLUMNS_ISKEY=''√'' 'set @sql=@sql+N'order by COLUMNS_SORT 'set @sql=@sql+N')'set @sql=@sql+N'A'exec sp_executesql @sql,N'@cnt int output,@Table_Name varchar(20) output',@cnt output,@Table_Name output --計算主鍵的個數,表名

insert ##tselect '<'+@TableCode+' TABLE_NAME=''+@Table_Name+'' PK=''+cast(@cnt as varchar)+''>'union allselect space(4)+'<COLUMNS COLUMNS_NAME=''+cast(COLUMNS_NAME as varchar)+'''+space(8)+'TABLE_CODE=''+isnull(@TableCode,'')+'''+space(8)+'COLUMNS_VISIBLE=''+isnull(COLUMNS_VISIBLE,'')+''/>'from TB_TABLECOLUMN_T where TABLE_CODE=''+@TableCode+''union all select '</'+@TableCode+'>'set; @TableCodeS=right(@TableCodeS,len(@TableCodeS)-@i); set; @i=CHARINDEX(',',@TableCodeS); endif len(@TableCodeS)>0beginset @sql=''set @sql=N'select @cnt=count(*),@Table_Name=max(Table_Name) from 'set @sql=@sql+N'('set @sql=@sql+N'select top 100 percent * from TB_TABLECOLUMN_T where 'set @sql=@sql+N'TABLE_CODE='''+@TableCodeS+''' and COLUMNS_ISKEY=''√'' 'set @sql=@sql+N'order by COLUMNS_SORT 'set @sql=@sql+N')'set @sql=@sql+N'A'exec sp_executesql @sql,N'@cnt int output,@Table_Name varchar(20) output',@cnt output,@Table_Name output --計算主鍵的個數,表名insert ##tselect '<'+@TableCodeS+' TABLE_NAME=''+@Table_Name+'' PK=''+cast(@cnt as varchar)+''>'union allselect space(4)+'<COLUMNS COLUMNS_NAME=''+cast(COLUMNS_NAME as varchar)+'''+space(8)+'TABLE_CODE=''+isnull(@TableCodeS,'')+'''+space(8)+'COLUMNS_VISIBLE=''+isnull(COLUMNS_VISIBLE,'')+''/>'from TB_TABLECOLUMN_T where TABLE_CODE=''+@TableCodeS+''union all select '</'+@TableCodeS+'>'union all select '</ZehuaTableInfoXml>'end

exec('master..xp_cmdshell ''bcp ##t out ''+@fname+'' /P'' /c''') drop table ##T

go

/*

測試環境:

Windows 2000 Server + SQL 2000測試通過

*/