SQLRPGLE to XML file

[jarredmattison]

I am new to RPGLE, and I need to create some SQLRPGLE programs whose output is a standard XML file. Below is what I have, more stuff to add, but I want to get good XML output before I try and add all the data in the file. Eventually I will put this code into a host report in the infor IDF in case any of you need to know that. The table it is pulling from is from Infor XA.

The Code

ctl-opt dftactgrp(*no) actgrp(*caller);

//************************************************** **********************
// Files
//************************************************** **********************
dcl-s Outfile sqltype(XML_CLOB_FILE);
dcl-s Path varchar(100) inz('/home/jarred/PurchaseOrder.xml') ;



exec sql
set option // Standard SQL Compiler Override Options
naming = *sys, // Naming
commit = *none, // Commitment Control
dynusrprf = *owner, // Dynamic User Profile
closqlcsr = *endmod; // Close Cursor


clear Outfile ;
Outfile_Name = %trimr(Path) ;
Outfile_NL = %len(%trimr(Outfile_Name)) ;
Outfile_FO = SQFCRT ;


//************************************************** **********************
// Write PO Header Information
//************************************************** **********************
exec sql VALUES(Select XMLGROUP( ORDNO AS "ORDER_NUMBER",
REVNB AS "REVISION")

from amflibn.pomast
where ORDNO = 'P962241')
INTO: Outfile;
*inlr = *on ;

This more or less does what I need it to, but the value for ORDNO is not "P962241". Instead I get the following back.

<?xml version="1.0" encoding="UTF-8"?><rowset><row><ORDER_NUMBER>1/n28vL08Q==</ORDER_NUMBER><REVISION>0</REVISION></row></rowset>

I think it has something to with the CCSID encoding, but am not sure if that is an SQL option to change that, if it is a PF record format, or the XML output that I need to change.

Any help would be great!

UPDATE:
So it is definitely related to the CCSID of the PF being 37, but I am not sure how to change the CCSID results for the entire query. Is there a compiler option, or something I can have in the d-spec. I can't cast each coulmn value because then the XMLELEMENT starts requiring ")" that are not needed.

[B.Hauser]

XMLGROUP and all other XML SQL commands convert the data into UTF-8.
What is the SQLCODE or SQLSTATE immediately after the VALUES ... INTO Statement?
How is the ORDNO column defined (Data Type / Length)?
If you run the SELECT Statement interactively (from IBM i Access Solutions) - How looks the result like?
How are you looking at the XML Data?

[jarredmattison]

OK. I played around with it a bit more, and it sounds like you need a bit more context. The code below is the SQL view I made. The only field that comes through correctly is po.ordno, but that has to be cast as a char with the ccsid37. I am pretty sure the whole table (and most of them in MAPICS/XA) are all encoded with CCSID 37.

Running the query through IBM iAccess Solutions provides me the correct values, but I have to have the "Translate CSSID 65535" for it to work correctly. if I use SQL XML in the iAccess Solutions, I don't have a problem. All I want to do is save the resulting info into an XML file on the IFS. I view the file on the IFS through the IFS directory and download it to my computer.

create view xamodnn.fmspoh
select
cast(po.ordno as char(7) ccsid 37),
po.revnb,
po.vndnr,
vdr.vname,
vdr.vadd1,
vdr.vadd2,
vdr.vcity,
vdr.vzipc,
vdr.vstac,
vdr.vcntr,
vdr.vetel,
vdr.vcont,
po.viads,
po.fobds,
po.trmds,
shp.shpnm,
shp.ship1,
shp.ship2,
shp.scity,
shp.shpzp,
shp.SSTAC,
shp.SCNTR,
po.connm,
po.contl,
buy.buynm,
buy.buyph,
buy.eadr,
po.uusapm,
po.eadr,
po.apal
from pomast as po
join buyerf as buy
on po.buyno = buy.buyno
join shpmst as shp
on po.house = shp.house
and po.bilid = shp.shpid
join vennam as vdr
on po.vndnr = vdr.vndnr

SQLRPGLE CODE

 ctl-opt dftactgrp(*no) actgrp(*caller) ccsidcvt(*excp: *list);

//************************************************** **********************
// Files
//************************************************** **********************
//dcl-f pomast usage(*input) keyed CCSID(37);
dcl-s Outfile sqltype(xml_clob_File);
dcl-s Path varchar(100) inz('/home/jarred/PurchaseOrder.xml') ;



exec sql
set option // Standard SQL Compiler Override Options
naming = *sys, // Naming
commit = *none, // Commitment Control
dynusrprf = *owner, // Dynamic User Profile
DatFmt=*ISO, // Date Format
closqlcsr = *endmod; // Close Cursor


clear Outfile ;
Outfile_Name = %trimr(Path) ;
Outfile_NL = %len(%trimr(Outfile_Name)) ;
Outfile_FO = SQFOVR ;


//************************************************** **********************
// Write PO Header Information
//************************************************** **********************




exec sql SELECT
XMLDOCUMENT(
XMLELEMENT(NAME "PO_Header",
XMLELEMENT(NAME "OrderNumber", ordno),
XMLELEMENT(NAME "OrderRev", revnb),
XMLELEMENT(NAME "SHIP_NAME", SHPNM)
))
into :Outfile
from xamodnn.fmspoh
where ordno = 'P10829';


*inlr = *on ;

Please let me know if you need anything else. I am happy to learn.

**EDIT: This code will be called from an unattached job from within the ERP. I presume that it will use what ever codepage that job uses, when it kicks off this job. Can I specify which CCSID the job runs in, or do I have to pass that as a parameter when I call it from qcmd.

[B.Hauser]

If you have to use Convert CCSID 66535 then your table or better your columns are generated with no CCSID (i.e. CCSID 65535). Without CCSID (or CCSID 65535) the XML commands are not able to convert the data correctly into UTF8, because they dos not know what is the original CCSID. If you select option CCSID 65535 the columns/values with CCSID 65535 are automatically casted into the job CCSID.

What you can do: Cast in your view all character fields to CCSID 37 ... and then rerun your program. Now the data should be generated correctly.

[jarredmattison]

This is what exactly what I thought I had to do, but was hoping there was a more elegant solution.

Thanks!