22,209
社区成员
发帖
与我相关
我的任务
分享
CREATE PROC p1
AS
DECLARE @x XML
SET @x=(select * from Test1 FOR XML RAW('entry'),ELEMENTS,ROOT('so_order_body'))
SELECT @x.query('<ufinterface xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" roottag="so_order" billtype="30" replace="Y" sender="30" isexchange="Y" filename="销售订单头.xml" proc="add" operation="req">
<so_order>
<so_order_head>
<pk_corp>深圳华强电子商务股份有限公司</pk_corp>
<creceipttype>30</creceipttype>
<cbiztype>电商淘宝零售</cbiztype>
<binitflag>N</binitflag>
<dbilldate>2016-06-16</dbilldate>
<ccustomerid>SWJ</ccustomerid>
<cdeptid>经营管理部</cdeptid>
<cemployeeid>彭斌</cemployeeid>
<coperatorid>顾问3</coperatorid>
<csalecorpid>深圳华强电子商务股份有限公司</csalecorpid>
<ccalbodyid>深圳华强电子世界网络技术有限公司</ccalbodyid>
<creceiptcustomerid>SWJ</creceiptcustomerid>
<creceiptcorpid>SWJ</creceiptcorpid>
<ndiscountrate>100</ndiscountrate>
<bfreecustflag>N</bfreecustflag>
<ibalanceflag>1</ibalanceflag>
<dmakedate>2016-06-16</dmakedate>
<fstatus>1</fstatus>
<vnote>华强官方旗舰店</vnote>
<vdef5>20160616MDBJTZ</vdef5>
<bretinvflag>N</bretinvflag>
<boutendflag>N</boutendflag>
<binvoicendflag>N</binvoicendflag>
<breceiptendflag>N</breceiptendflag>
<bpayendflag>N</bpayendflag>
</so_order_head>
{ /so_order_body } </so_order></ufinterface>')
GO
DECLARE @Sqlcmd NVARCHAR(max)
SET @Sqlcmd='EXEC master.sys.xp_cmdshell ''bcp "EXEC Demo.dbo.p1" queryout E:\hqdst\'+ CONVERT(VARCHAR(10),GETDATE(),112)+REPLACE(CONVERT(VARCHAR(8),GETDATE(),8),':','')+'.xml -c -q -T'''
EXEC(@Sqlcmd)
DECLARE @x XML
SET @x=(select * from Test1 FOR XML RAW('entry'),ELEMENTS,ROOT('so_order_body'))
SELECT @x.query('<ufinterface xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" roottag="so_order" billtype="30" replace="Y" sender="30" isexchange="Y" filename="销售订单头.xml" proc="add" operation="req">
<so_order>
<so_order_head>
<pk_corp>深圳华强电子商务股份有限公司</pk_corp>
<creceipttype>30</creceipttype>
<cbiztype>电商淘宝零售</cbiztype>
<binitflag>N</binitflag>
<dbilldate>2016-06-16</dbilldate>
<ccustomerid>SWJ</ccustomerid>
<cdeptid>经营管理部</cdeptid>
<cemployeeid>彭斌</cemployeeid>
<coperatorid>顾问3</coperatorid>
<csalecorpid>深圳华强电子商务股份有限公司</csalecorpid>
<ccalbodyid>深圳华强电子世界网络技术有限公司</ccalbodyid>
<creceiptcustomerid>SWJ</creceiptcustomerid>
<creceiptcorpid>SWJ</creceiptcorpid>
<ndiscountrate>100</ndiscountrate>
<bfreecustflag>N</bfreecustflag>
<ibalanceflag>1</ibalanceflag>
<dmakedate>2016-06-16</dmakedate>
<fstatus>1</fstatus>
<vnote>华强官方旗舰店</vnote>
<vdef5>20160616MDBJTZ</vdef5>
<bretinvflag>N</bretinvflag>
<boutendflag>N</boutendflag>
<binvoicendflag>N</binvoicendflag>
<breceiptendflag>N</breceiptendflag>
<bpayendflag>N</bpayendflag>
</so_order_head>
{ /so_order_body } </so_order></ufinterface>')
DECLARE @Sqlcmd NVARCHAR(max)
SET @Sqlcmd='EXEC master.sys.xp_cmdshell ''bcp "EXEC Demo.dbo.p1" queryout E:\hqdst\'+ CONVERT(VARCHAR(10),GETDATE(),112)+REPLACE(CONVERT(VARCHAR(8),GETDATE(),8),':','')+'.xml -c -q -T'',no_output'
EXEC(@Sqlcmd)
DECLARE @Sqlcmd NVARCHAR(max)
SET @Sqlcmd='EXEC master.sys.xp_cmdshell ''bcp "EXEC Demo.dbo.p1" queryout E:\hqdst\'+ CONVERT(VARCHAR(10),GETDATE(),112)+REPLACE(CONVERT(VARCHAR(8),GETDATE(),8),':','')+'.xml -w -q -T'',no_output'
EXEC(@Sqlcmd)
-w
用以上命令试试,你生成的XML有特殊字符时可能会出现