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 asked 20 Apr '12, 13:11 Jennifer D Graeme Perrow |
The OUTPUT statement is a dbisql client statement and is not understood by the server. Change your procedure to use the UNLOAD statement. You could also use the xp_write_file() builtin function to write to a text file as described in the answer to this question. answered 20 Apr '12, 13:38 Mark Culp Although, the UNLOAD statement does not support the HEXIDECIMAL ASIS clause.
(20 Apr '12, 13:47)
Chris Keating
So if I use UNLOAD how do I get it as an XML because my understanding of UNLOAD is that unload can only do ASCII? Cheers, Jennifer
(20 Apr '12, 14:53)
Jennifer D
Replies hidden
1
You can use the FOR XML clause to SELECT the data as an XML document and then UNLOAD that document. See http://dcx.sybase.com/index.html#1201/en/dbusage/sqlxml-s-5588898.html for more information on the FOR XML clause.
(21 Apr '12, 08:27)
Mark Culp
|