We help IT Professionals succeed at work.

export sql query results to xml

204 Views
Last Modified: 2015-01-15
Hi Everyone,

I have the following sql query that I would like to store the results into an xml file on the local file system.  I am not a SQL programmer and the query was generated from another program.  If I run the query I am returned the proper results.  I just want to have the query put those results in an xml file called c:\slx\commission.xml

Any help would be appreciated.
use epicor905
declare @curcomp char(8);
set @curcomp = 'AI';
if object_id('tempdb..#EpiConstants') is not null drop table #EpiConstants; 
create table #EpiConstants(  FirstDayOfMonth datetime,  LastDayOfMonth datetime,  FirstDayOfNextMonth datetime,  LastDayOfNextMonth datetime,  FirstDayOfPrevMonth datetime,  LastDayOfPrevMonth datetime,  FirstDayOfWeek datetime,  LastDayOfWeek datetime,  FirstDayOfNextWeek datetime,  LastDayOfNextWeek datetime,  FirstDayOfPrevWeek datetime,  LastDayOfPrevWeek datetime,  CurrentWeek integer,  WeekDay integer,  CurrentMonth integer,  FiscalPeriod integer,  FiscalYear integer,  CurrTime integer,  Day integer,  Year integer,  Yesterday datetime,  Today datetime, 
  Tomorrow datetime,  CurrentCompany char (8),  CurComp char(8),  CurrentPlant char(8),  CurPlant char(8),  CurrentUserID char(20),  CurrentEmployeeID  char(8),  MFGSYS  char(8),  MFGSYSName char(20),  CurrentLanguageID char(8),  CurLangID char(8),  CurrentCompanyName char(50),  CurCompName char(50),  DefaultUM char(6),  DefUM char(6),  CurrentJobNumber char(14),  CurJobNumber char(14),  GarbageField char(8),   BAQSetNum smallint);
insert #EpiConstants values('3/1/2014','3/31/2014','4/1/2014','4/30/2014','2/1/2014','2/28/2014','3/23/2014','3/29/2014','3/30/2014','4/5/2014','3/16/2014','3/22/2014',13,4,3,3,2014,45853,26,2014,'3/25/2014','3/26/2014','3/27/2014',@curcomp,@curcomp,'MfgSys','MfgSys','triciad','','EP','Epicor','enu','enu','Advanced Instruments Inc.','Advanced Instruments Inc.','EA','EA','','', 'j8d!hf?0', 1);
declare @CLst table(CID char(8));
insert @CLst values('');insert @CLst values(@curcomp);
declare @TLst table(CID char(8),TID char(8));
insert @TLst values('AI','AFRICA');insert @TLst values('AI','AB');insert @TLst values('AI','ASIAPAC');insert @TLst values('AI','BC');insert @TLst values('AI','CANADA');insert @TLst values('AI','CEEURO');insert @TLst values('AI','LATAMER');insert @TLst values('AI','MB');insert @TLst values('AI','MIDEAST');insert @TLst values('AI','NB');insert @TLst values('AI','NL');insert @TLst values('AI','NT');insert @TLst values('AI','NS');insert @TLst values('AI','NU');insert @TLst values('AI','ON');insert @TLst values('AI','PE');insert @TLst values('AI','QC');insert @TLst values('AI','SK');insert @TLst values('AI','SOUAMER');insert @TLst values('AI','MIDWEST');insert @TLst values('AI','NOREAST');insert @TLst values('AI','SOUEAST');insert @TLst values('AI','SOUWEST');insert @TLst values('AI','WEST');insert @TLst values('AI','WEUROPE');insert @TLst values('AI','YT');insert @TLst values('AI','');;
declare @BAQResult table(BAQSetNum smallint,BAQJoinFlag smallint,[RM] varchar(30),[InvcHead_InvoiceDate] datetime,[DistDir] varchar(15),[DistName] varchar(40),[InvcDtl_OrderNum] int,[Customer_CustID] varchar(10),[Name] varchar(50),[ZipCode] varchar(15),[State] varchar(8),[Part_PartNum] varchar(50),[Part_PartDescription] text,[OrderDtl_Commissionable] tinyint,[InvcDtl_OurShipQty] decimal(22,8),[ExtPrice] decimal(12,2),[InvcHead_InvoiceNum] int,[SalesRep_CommissionPercent] decimal(6,2)); 

insert into @BAQResult select  1, 1, left(left( (case when not  (( UD04.Key2 ) is null)  then  UD04.Key2  else  SalesRep.Name  end) ,30),30),InvcHead.InvoiceDate,left(left( (case when  Customer.GroupCode  =  'DOMD'  or  Customer.GroupCode = 'INTD'  then  'Distributor'  else  'Direct'  end) ,15),15),left(left( (case when  Customer.GroupCode  =  'DOMD'  then  Customer.Name  else  ''  end) ,40),40),InvcDtl.OrderNum,Customer.CustID,left(left( (case when  InvcDtl.UseOTS  =  1  then  InvcDtl.OTSName  else  (case when not  (( UD04.Key3 ) is null)  then  UD04.Key3  else  Customer.Name  end)  end) ,50),50),left(left( (case when  invcdtl.UseOTS  =  1  then  invcdtl.OTSZIP  else  (case when not  (( UD04.Key4 ) is null)  then  UD04.Key4  else  (case when  Customer.GroupCode  =  'DOMD'  then  ''  else  Customer.Zip  end)  end)  end) ,15),15),left(left( (case when  invcdtl.UseOTS  =  1  then  invcdtl.OTSState  else  (case when  Customer.GroupCode  =  'DOMD'  then  ''  else  Customer.State  end)  end) ,8),8),Part.PartNum,Part.PartDescription,OrderDtl.Commissionable,InvcDtl.OurShipQty, InvcDtl.DocExtPrice  - ( InvcDtl.DocDiscount ),InvcHead.InvoiceNum,SalesRep.CommissionPercent
from #EpiConstants inner join InvcHead on (InvcHead.Company is null or InvcHead.Company in (select CID from @CLst)) inner join SalesRep on (SalesRep.Company = InvcHead.Company and SalesRep.SalesRepCode = InvcHead.SalesRepList) and ((SalesRep.Company is null or SalesRep.Company in (select CID from @CLst))) inner join Customer on (Customer.Company = InvcHead.Company and Customer.CustNum = InvcHead.CustNum) and ((Customer.Company is null or Customer.Company in (select CID from @CLst)) and (((Customer.TerritoryID is null) or exists(select 1 from @TLst where CID=Customer.Company and TID=Customer.TerritoryID)) or exists(select 1 from ShipTo st_ where st_.CustNum=Customer.CustNum and st_.Company = Customer.Company and ((st_.TerritoryID is null) or exists(select 1 from @TLst where CID=st_.Company and TID=st_.TerritoryID))) )) inner join InvcDtl on (InvcDtl.Company = InvcHead.Company and InvcDtl.InvoiceNum = InvcHead.InvoiceNum) and ((InvcDtl.Company is null or InvcDtl.Company in (select CID from @CLst))) left outer join UD04 on (UD04.Company = InvcHead.Company and UD04.Key5 = cast(InvcHead.OrderNum as varchar(50))) and ((UD04.Company is null or UD04.Company in (select CID from @CLst))) left outer join Part on (Part.Company = InvcDtl.Company and Part.PartNum = InvcDtl.PartNum) and ((Part.Company is null or Part.Company in (select CID from @CLst))) inner join OrderHed on (OrderHed.Company = InvcHead.Company and OrderHed.OrderNum = InvcHead.OrderNum) and ((OrderHed.Company is null or OrderHed.Company in (select CID from @CLst))) inner join OrderDtl on (OrderDtl.Company = InvcDtl.Company and OrderDtl.OrderNum = InvcDtl.OrderNum and OrderDtl.OrderLine = InvcDtl.OrderLine) and ((OrderDtl.Company is null or OrderDtl.Company in (select CID from @CLst)) and (  OrderDtl.Commissionable = 1 )) 

 order by InvcHead.InvoiceDate asc;

select [RM],[InvcHead_InvoiceDate],[DistDir],[DistName],[InvcDtl_OrderNum],[Customer_CustID],[Name],[ZipCode],[State],[Part_PartNum],[Part_PartDescription],[OrderDtl_Commissionable],convert(decimal(16,2),[InvcDtl_OurShipQty]),[ExtPrice],[InvcHead_InvoiceNum],[SalesRep_CommissionPercent] from @BAQResult, #EpiConstants where BAQJoinFlag = 1 and (  InvcHead_InvoiceDate >=  '1/1/14' );
select 0;

Open in new window

Comment
Watch Question

aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks for the help.  I have set the query to use "for xml path ('Sale'), root ('RM'), ELEMENTS XSINIL;" and have the output the way I want it.  Now I need to know how to make the output save to a file on the local file system.  I am using MS SQL Server 2008r2 with SSMS.
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Yes.  I would like to make the file to be replaced nightly.  I am hoping to run a crystal report from the xml data.
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Why use an intermediary process when you can use crystal to directly query the databse?

Author

Commented:
The crystal report is being run by a separate web application and I did not want to have store authentication information in the report.

Author

Commented:
Only a partial answer
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.