I created this code to build and output to an xml file but when I try to put it into a stored procedure or trigger it errors on the Output To line. After some research I have discovered that the Output To command can only be used in iSQL and that I need to use the Unload command except that I can figure out the syntax for it. I would appreciate any help I could get on this one.
We are using SQL Anywhere 10.0.1.4051.
IF EXISTS (SELECT * FROM sysobject WHERE object_id =
OBJECT_ID('FLO.BillingXMLExport') AND object_type = 6) THEN
DROP PROCEDURE FLO.BillingXMLExport
END IF;
Create Procedure "FLO"."BillingXMLExport" (
@BID int)
Begin
select
1 as [tag],
null as [parent],
null AS [File!1],
f.FeedlotName as [File!1!FeedlotName!Element],
GetDate() as [File!1!GenerationDate!Element],
'Billing' as [File!1!FileType!Element],
null as [Billing!2!BillDate!Element],
null as [Lot!3!LotNum!Element],
null as [Lot!3!LotTotal!Element],
null as [Detail!4!Category!Element],
null as [Detail!4!CategoryTotal!Element],
null as [CategoryDetail!5!Item!Element],
null as [CategoryDetail!5!ItemQtyTotal!Element],
null as [CategoryDetail!5!ItemQtyUOM!Element],
null as [CategoryDetail!5!ItemChargeTotal!Element]
From Feedlot f
UNION ALL
select
2 as [tag],
1 as [parent],
null,
null,
null,
null,
b.InvoiceDate,
null,
null,
null,
null,
null,
null,
null,
null
From Feedlot f,Billing b
Where b.BillingId = @BID
UNION ALL
select
3 as [tag],
2 as [parent],
null,
null,
null,
null,
b.InvoiceDate,
l.LotNum,
sum(c.Amount),
null,
null,
null,
null,
null,
null
From Feedlot f, Billing b
inner join LotBilling lb on b.BillingID = lb.BillingID
inner join lot l on lb.LotID = l.LotID
inner join (select lb2.lotID, lb2.LotBillingID, sum(lbed2.Amount)
as Amount
from LotBilling lb2
inner join LotBillingEvent lbe2 on lb2.LotBillingID =
lbe2.LotBillingID
inner join LotBillingEventDetail lbed2 on
lbe2.LotBillingEventID = lbed2.LotBillingEventID
where lb2.BillingID = @BID
group by lb2.lotID, lb2.LotBillingID
UNION ALL
select lb2.lotID, lb2.LotBillingID, sum(lbap2.charge)
as Amount
from LotBilling lb2
inner join LotBillingAnimalPen lbap2 on
lb2.LotBillingID = lbap2.LotBillingID
where lb2.BillingID = @BID
group by lb2.lotID, lb2.LotBillingID) as c on lb.LotID
= c.LotID and lb.LotBillingID = c.LotBillingID
Where b.BillingId = @BID
Group By f.FeedlotName, b.InvoiceDate, l.LotNum
UNION ALL
select
4 as [tag],
3 as [parent],
null,
null,
null,
null,
b.InvoiceDate,
l.LotNum,
null,
c.Code,
sum(c.Amount),
null,
null,
null,
null
From Feedlot f, Billing b
inner join LotBilling lb on b.BillingID = lb.BillingID
inner join Lot l on lb.LotID = l.LotID
inner join (Select lb.LotID, s.SysCode as Code, sum(lbed.Amount)
as Amount
From LotBilling lb
inner join LotBillingEvent lbe on
lb.LotBillingID = lbe.LotBillingID
inner join LotBillingEventDetail lbed on
lbe.LotBillingEventID = lbed.LotBillingEventID
inner join SysCode s on lbed.ChargeTypeSID =
s.SysCodeID
Where lb.BillingID = @BID
Group By lb.LotID, s.SysCode
Union All
Select lb.LotID, 'Pen Chrg' as Code, sum(lbap.Charge)
as Amount
From LotBilling lb
inner join LotBillingAnimalPen lbap on
lb.LotBillingID = lbap.LotBillingID
Where lb.BillingID = @BID
Group By lb.LotID) As c on lb.LotID = c.LotID
Where b.BillingId = @BID
Group By f.FeedlotName, b.InvoiceDate, l.LotNum, c.Code
UNION ALL
select
5 as [tag],
4 as [parent],
null,
null,
null,
null,
b.InvoiceDate,
l.LotNum,
null,
c.Code,
null,
c.Item,
sum(c.Qty),
c.UOM,
sum(c.Amount)
From Feedlot f, Billing b
inner join LotBilling lb on b.BillingID = lb.BillingID
inner join Lot l on lb.LotID = l.LotID
inner join (Select lb.LotID, s.SysCode as Code, p.ProductCode as
Item, sum(isnull(aep.charge,0)*aep.actualqty)--lbed.Amount)
as Amount, sum(aep.ActualQty) as Qty,
su.SysUnitCode as UOM
From LotBilling lb
inner join LotBillingEvent lbe on
lb.LotBillingID = lbe.LotBillingID
inner join LotBillingEventDetail lbed on
lbe.LotBillingEventID = lbed.LotBillingEventID
inner join AnimalEventProduct aep on
lbe.AnimalEventID = aep.AnimalEventID and lbed.ChargeTypeSID = 328
inner join Product p on aep.ProductID =
p.ProductID
inner join SysCode s on lbed.ChargeTypeSID =
s.SysCodeID
inner join SysUnit su on aep.SysUnitID =
su.SysUnitID
Where lb.BillingID = @BID
Group By lb.LotID, s.SysCode , p.ProductCode,
su.SysUnitCode
UNION ALL
Select lb.LotID, s.SysCode as Code,
p.ProcessCode as Item, sum(lbed.Amount) as Amount, sum(1) as Qty, 'Ea'
as UOM
From LotBilling lb
inner join LotBillingEvent lbe on
lb.LotBillingID = lbe.LotBillingID
inner join LotBillingEventDetail lbed on
lbe.LotBillingEventID = lbed.LotBillingEventID
inner join AnimalEventProcess aep on
lbe.AnimalEventID = aep.AnimalEventID and lbed.ChargeTypeSID = 330
inner join Process p on aep.ProcessID =
p.ProcessID
inner join SysCode s on lbed.ChargeTypeSID =
s.SysCodeID
Where lb.BillingID = @BID
Group By lb.LotID, s.SysCode , p.ProcessCode
UNION ALL
Select lb.LotID, s.SysCode as Code,
e.EventCode as Item, sum(lbed.Amount) as Amount, sum(1) as Qty, 'Ea'
as UOM
From LotBilling lb
inner join LotBillingEvent lbe on
lb.LotBillingID = lbe.LotBillingID
inner join LotBillingEventDetail lbed on
lbe.LotBillingEventID = lbed.LotBillingEventID
inner join AnimalEvent ae on lbe.AnimalEventID
= ae.AnimalEventID and lbed.ChargeTypeSID = 332
inner join [Event] e on ae.EventID = e.EventID
inner join SysCode s on lbed.ChargeTypeSID =
s.SysCodeID
Where lb.BillingID = @BID
Group By lb.LotID, s.SysCode , e.EventCode
) As c on lb.LotID = c.LotID
Where b.BillingId = @BID
Group By f.FeedlotName, b.InvoiceDate, l.LotNum, c.Code, c.Item,
c.UOM
ORDER BY [Lot!3!LotNum!Element], [Detail!4!Category!Element],
[CategoryDetail!5!Item!Element]
FOR XML Explicit;
Output to c:\\BillingTest.xml DELIMITED BY '' QUOTE '' HEXADECIMAL
ASIS;
End
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.