export sql query results to xml

Posted on 2014-12-30
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

Question by:James Rizzitano
  • 4
  • 3
LVL 45

Assisted Solution

aikimark earned 200 total points
ID: 40524969
have you tried using the For XML clause?
LVL 77

Accepted Solution

arnold earned 300 total points
ID: 40524972
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

Author Comment

by:James Rizzitano
ID: 40525201
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.
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

LVL 77

Assisted Solution

arnold earned 300 total points
ID: 40525242
Are you looking for the file to be created automatically?
Is this a sql agent job?

Author Comment

by:James Rizzitano
ID: 40531441
Yes.  I would like to make the file to be replaced nightly.  I am hoping to run a crystal report from the xml data.
LVL 77

Expert Comment

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

Author Comment

by:James Rizzitano
ID: 40531537
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 Closing Comment

by:James Rizzitano
ID: 40551186
Only a partial answer

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question