export sql query results to xml

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

James RizzitanoAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
arnoldConnect With a Mentor Commented:
add to the end of your query, for XML AUTO and it will format it for you.

If you have a specific XML structure, you would need to define it.

You've not specified the version of ms sql you have, here is a link to MS layout of the for XML for the various MS SQL version starting with SQL 2005
http://msdn.microsoft.com/en-us/library/ms190922%28v=sql.90%29.aspx
0
 
aikimarkConnect With a Mentor Commented:
have you tried using the For XML clause?
0
 
James RizzitanoAuthor 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.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
arnoldConnect With a Mentor Commented:
Are you looking for the file to be created automatically?
Is this a sql agent job?
0
 
James RizzitanoAuthor 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.
0
 
arnoldCommented:
Why use an intermediary process when you can use crystal to directly query the databse?
0
 
James RizzitanoAuthor 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.
0
 
James RizzitanoAuthor Commented:
Only a partial answer
0
All Courses

From novice to tech pro — start learning today.