--导出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'
--测试--测试数据 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/*--生成的xml文件内容<?xml version="1.0" encoding="gb2312"?> <table name="tx"> <record a="1"> <b>aaa</b> <c>bbb</c> </record> <record a="2"> <b>ccc</b> <c>ddd</c> </record> <record a="3"> <b>eee</b> <c>fff</c> </record> <record a="4"> <b>ggg</b> <c>hhh</c> </record> <record a="5"> <b>iii</b> <c>jjj</c> </record> </table>--*/
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'
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/*--生成的xml文件内容<?xml version="1.0" encoding="gb2312"?>
<table name="tx">
<record a="1">
<b>aaa</b>
<c>bbb</c>
</record>
<record a="2">
<b>ccc</b>
<c>ddd</c>
</record>
<record a="3">
<b>eee</b>
<c>fff</c>
</record>
<record a="4">
<b>ggg</b>
<c>hhh</c>
</record>
<record a="5">
<b>iii</b>
<c>jjj</c>
</record>
</table>--*/