/*
\'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\'
备注: 根据中间表生成符合标准格式的XML文件
**********************************************************************/

/*
exec P_ZehuaSavexml \'WQ_STINFO_B,WQ_PHY_D\',\'D:xx.xml\'
*/
go
create proc P_ZehuaSavexml
@TableCodeS varchar(8000),
@fname varchar(1000)=\'c:tmp.xml\' --默认保存的XML文件地址
as
declare @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 ##t
select Re=\'<?xml version="1.0" encoding="gb2312"?>\'
union all select \'<ZehuaTableInfoXml>\'
set @i=charindex(\',\',@TableCodeS)
while @i>0
begin
set @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 ##t
select \'<\'+@TableCode+\' TABLE_NAME="\'+@Table_Name+\'" PK="\'+cast(@cnt as varchar)+\'">\'
union all
select 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)
end
if len(@TableCodeS)>0
begin

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=\'\'\'+@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 ##t
select \'<\'+@TableCodeS+\' TABLE_NAME="\'+@Table_Name+\'" PK="\'+cast(@cnt as varchar)+\'">\'
union all
select 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测试通过

*/