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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 46

Assisted Solution

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

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.
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

LVL 79

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 79

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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

615 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