sql server导出查询结果到xml文件到硬盘本来是用程序导出的xml文件到C盘下面,现在想请教各位能不能用sql server存储过程来实现? 唐大神正在努力,稍安勿躁[解决办法]
declare @xml nvarchar(max);with ttas(select FromCorpID , ActDate , Actor , CorpOrderID , BillID , Name, UpperCorpOrderID, Codefrom(select FromCorpID='00000000000000043621', ActDate='2014-01-16 15:32:44' , Actor=N'蔡琳' , CorpOrderID='2995975' , BillID='20130002995975' , Name='102' , UpperCorpOrderID='' , Code='81206620073443503824'union allselect FromCorpID='00000000000000043621', ActDate='2014-01-16 15:32:44' , Actor=N'蔡琳' , CorpOrderID='2995975' , BillID='20130002995975' , Name='102' , UpperCorpOrderID='' , Code='81206620073443470219'union allselect FromCorpID='00000000000000043621', ActDate='2014-01-16 15:32:44' , Actor=N'蔡琳' , CorpOrderID='2995975' , BillID='20130002995975' , Name='102' , UpperCorpOrderID='' , Code='81206620073443499601')t)select @xml = CAST(xx as nvarchar(max))from(select * from(select FromCorpID , ActDate , Actor , CorpOrderID , BillID , Name, cast((select UpperCorpOrderID,Code from tt t2 where t1.FromCorpID = t2.FromCorpID and t1.Actor = t2.Actor and t1.CorpOrderID = t2.CorpOrderID and t1.BillID = t2.BillID for xml raw('Data')--,root('') ) as xml) DataField from tt t1group by FromCorpID , ActDate , Actor , CorpOrderID , BillID , Name)afor xml raw('Bill'),type)b(xx)select @xml = '<Document License="license" SN="computer" Version="3.0" xmlns:xsi="http://www1.drugadmin.com"><Event ID="StockIn">' +@xml + '</Event></Document>'select cast(@xml as xml) as 'dataxml'/*<Document xmlns:xsi="http://www1.drugadmin.com" License="license" SN="computer" Version="3.0"> <Event ID="StockIn"> <Bill FromCorpID="00000000000000043621" ActDate="2014-01-16 15:32:44" Actor="蔡琳" CorpOrderID="2995975" BillID="20130002995975" Name="102"> <DataField> <Data UpperCorpOrderID="" Code="81206620073443503824" /> <Data UpperCorpOrderID="" Code="81206620073443470219" /> <Data UpperCorpOrderID="" Code="81206620073443499601" /> </DataField> </Bill> </Event></Document>*/
ActDate='2014-01-16 15:32:44' , Actor=N'蔡琳' , CorpOrderID='2995975' , BillID='20130002995975' , Name='102' , UpperCorpOrderID='' , Code='81206620073443470219'union allselect FromCorpID='00000000000000043621', ActDate='2014-01-16 15:32:44' , Actor=N'蔡琳' , CorpOrderID='2995975' , BillID='20130002995975' , Name='102' , UpperCorpOrderID='' , Code='81206620073443499601')t)select @xml = CAST(xx as nvarchar(max))from(select * from(select FromCorpID , ActDate , Actor , CorpOrderID , BillID , Name, cast((select UpperCorpOrderID,Code from tt t2 where t1.FromCorpID = t2.FromCorpID and t1.Actor = t2.Actor and t1.CorpOrderID = t2.CorpOrderID and t1.BillID = t2.BillID for xml raw('Data')--,root('') ) as xml) DataField from tt t1group by FromCorpID , ActDate , Actor , CorpOrderID , BillID , Name)afor xml raw('Bill'),type)b(xx)select @xml = '<Document License="license" SN="computer" Version="3.0" xmlns:xsi="http://www1.drugadmin.com"><Event ID="StockIn">' +@xml + '</Event></Document>'select cast(@xml as xml) as 'dataxml'/*<Document xmlns:xsi="http://www1.drugadmin.com" License="license" SN="computer" Version="3.0"> <Event ID="StockIn"> <Bill FromCorpID="00000000000000043621" ActDate="2014-01-16 15:32:44" Actor="蔡琳" CorpOrderID="2995975" BillID="20130002995975" Name="102"> <DataField> <Data UpperCorpOrderID="" Code="81206620073443503824" /> <Data UpperCorpOrderID="" Code="81206620073443470219" /> <Data UpperCorpOrderID="" Code="81206620073443499601" /> </DataField> </Bill> </Event></Document>*/