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.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to set UNMASK permission only to only one table in SQL Server 2016 ? 11 75
script to send html report 3 32
Sql Stored Procedure 65 26
SQL Replication question 9 41
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

785 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