Output an XML File from Stored Procedure/Trigger

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

xml output

asked 20 Apr '12, 13:11

Jennifer D
16●1●1●2
accept rate: 0%

edited 21 Apr '12, 10:21

Graeme Perrow
9.6k●3●79●124

flat view

One Answer:

active answers oldest answers newest answers popular answers

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.

permanent link

answered 20 Apr '12, 13:38

Mark Culp
24.9k●10●141●297
accept rate: 41%

edited 20 Apr '12, 13:41

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

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

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

xml ×29
output ×28

question asked: 20 Apr '12, 13:11

question was seen: 4,658 times

last updated: 21 Apr '12, 10:21

SAP SQL Anywhere

Output an XML File from Stored Procedure/Trigger

Follow this question

Related questions