Re: Writing XML with RPGLE V7R1M0 -- RPG400-L

I don't disagree that if you build in pieces Birgitta it is not as bad. Assuming of course that you already competent with CTEs and other SQL features.

But I do think it dangerously comes close to being write-only code. I have been looking at your example for quite a while now and I still can't just "read" it. I can work it out slowly - but I can't just understand it.

With the powerExt approach or with CGIDEV2 or other templating systems what is happening is far more obvious and readable.

By the way, I still can't see how you omit elements completely using SQL - it may be in your example but I'm darned if I can see it. The logic that can be applied to the build is also limited. SQL gets very lumpy very quickly when anything beyond a simple Case needs to be used.

As I see it, it is a case of using the appropriate tool for the job. I can see using SQL for a simple table dump similar to your original example. Once it gets as complex as this example I want something easier to read and maintain. Right tool for the job - and in my opinion SQL is not the right tool at this level of complexity. Maybe I'll change my mind as I become more familiar with it.

On 2014-08-21, at 1:29 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx> wrote:

Jon, Vern,

It's only the way you are used to do it.

The SQL syntax is really powerful.
Using CTEs allow you to the complex structure into sub-structures. Those
sub-structures can be tested and put together.
You start with the lowest level, build the XML string add join fields and
test the result. Build the next level join with the lower level, test it
again etc.
And at the end the complete XML document is created.

In RPG I'd use the same practice. Writing sub-procedures, testing those
sub-procedures, calling the sub-procedures from other procedures etc.

In real live I create and use SQL views may be in composition with global
variables and reduce my RPG or whatever (embedded SQL) Code to a minimum:
Exec SQL Set GblVaStart = :HostStartDate;
Exec SQL Set GblVarIncoTerms = :HostIncoTerms;
... set other Global Variables
Exec SELECT XMLDoc into :IFSFile From SQLView;

BTW in my classes I have them both, those guys who prefer the pure RPG/Cobol
(in composition with CGIDEV2) solution and others who prefer the pure SQL
version.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Vernon
Hamberg
Gesendet: Wednesday, 20.8 2014 21:22
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: Re: Writing XML with RPGLE V7R1M0

Jon

I had a similar response when I first tried to write some XML here - the SQL
approach was ghastly as to syntax, and CGIDEV2 was devilishly trivial.

I just don't have the time to dig so deeply into the SQL syntax - i think
it's tremendously powerful - but this is one area I don't really want to use
a "black-box" approach - I want to see better what I'm doing.

Maintainability and speed are key for me.

Cheers
Vern

On 8/20/2014 11:48 AM, Jon Paris wrote:

Thanks for that - I think it has convinced me that I don't want to build

XML this way <grin> but I will study it further.

I'd probably get on better if I could get past my thorough dislike for

SQL's ghastly syntax.

On 2014-08-20, at 11:54 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>

wrote:

Jon,

I only used this simple SQL statement because I wanted to show how to
write into the IFS with embedded SQL.
But iIf you want to have a more complex SQL statement that generates
an XML document that is written with a single SQL-Statement into the
IFS here is a more complex one:
Joining Address Master, Order Header, Order Detail and Item master

tables:

With -- 1. Address information:
-- <Customer

CustNo="xx"><Name></Name><Street></Street><ZipCode></ZipCode><City></City>

-- </Customer>
Address as (Select CustNo, XMLElement(Name "Customer",
XMLAttributes(Trim(CustNo) as

"CustNo"),

XMLConcat(XMLElement(Name "Name",
Trim(Trim(CustName1) concat ' ' concat Trim(CustName2))),
XMLForest(Trim(Street)
as "Street",
Trim(ZipCode)
as "ZipCode",
Trim(City) as
"City"))) CustXML
From Addressx),

-- 2. Order Header Information:
-- <Mandant></Mandant><OrderNo></OrderNo>
--
<DeliveryDate><DeliveryDate><OrderType></OrderType><DeliveryTerms></D
elivery
Terms>
Header as (Select Company, OrderNo, CustNo,
XMLForest(Company as "Mandant",
OrderNo as "OrderNo") OrderNoXML,
XMLConcat(XMLElement(Name "DeliveryDate",
Char(DelDate, ISO)),
XMLElement(Name "OrderType",
Case When OrderType = 'DO'
Then 'Domestic'
When OrderType = 'EX'
Then 'Export'
When OrderType = 'UO'
Then 'Express'
else '???' End),
XMLElement(Name "DeliveryTerms",
Case When DelTerms = 'CPT'
Then 'Delivered Free'
When DelTerms = 'EXW'
Then 'Ex Works'
Else '???' End))
HdrAddXML
From OrderHdrX
Where DelDate between '2009-12-01' and
'2009-12-31'),

-- 3. Joining Order Header and Address Information (CTE No. 1 and 2)
HdrAddr as (Select Company, OrderNo, XMLConcat(OrderNoXML,
CustXML,
HdrAddXML) OrderXML
from Header Join Address Using(CustNo)),

-- 4. Item Information
-- <Item

ItemNo=""><ItemDescription><ItemDescription><PricePerUnit

Currency=""></PricePerUnig>
-- </Item>
Item as (Select i.*,
XMLElement(Name "Item" ,
XMLAttributes(Trim(ItemNo) as "ItemNo"),
XMLConcat(XMLElement(Name
"ItemDescription", Trim(Descript)),
XMLElement(Name "PricePerUnit",

XMLAttributes('EUR' as "Currency"),
Price))) ItemXML
from ItemMastX i),

-- 5. Order Detail Information
-- <Position PosNo=""><Item Information from CTE No. 4=Item>
--
<DeliveryQuantity></DeliveryQuantity><DeliveryValue
Currency""></DeliveryValue>
-- </Position>
Position as (Select Company, OrderNo,
XMLElement(Name "Position",
XMLAttributes(OrderPos as "PosNo"),
XMLConcat(ItemXML,
XMLElement(Name
"DeliveryQuantity", DelQty),
XMLElement(Name "DeliveryValue",

XMLAttributes('EUR' as "Currency"),
DelQty *
Price))) PosXML
From OrderDetX join Item using (Company, ItemNo)
Where DelQty > 0),

-- 6. Concatenating Order Details per Order
-- <Positions><Order Position Information - CTE No. 5>
-- </Positions>
AllPos as (Select Company, OrderNo,
XMLElement(Name "Positions",
XMLAGG(PosXML)) PosOrderNoXML
from Position
Group By Company, OrderNo),

-- 7. Joining Order Header / Address / Order Position and Item
information
-- <Order><OrderHeader and Address information - CTE No. 3>
-- <Order Positions and Item information - CTE No. 5>
-- </Order>
Order as (Select XMLElement(Name "Order",
XMLConcat(OrderXML,
PosOrderNoXML)) OrdersXML
from HdrAddr join AllPos using (Company,
OrderNo))

-- Final Select Statement
-- Concatenating Order Informationen No. 7 and generating an XML
Document

Select xmldocument(xmlElement(Name "Orders", XMLAGG(OrdersXML)))
From Order;

An here is the Result (I only copied the first Order)

<Orders>
<Order>
<Mandant>10</Mandant>
<OrderNo>BNR2009-12-15/1</OrderNo>
<Customer CustNo="10003">
<Name>Goldbach GmbH
***********Sonder-Aktion******</Name>
<Street>Hanauerstr. 21</Street>
<ZipCode>63755</ZipCode>
<City>Alzenau</City>
</Customer>
<DeliveryDate>2009-12-01</DeliveryDate>
<OrderType>Domestic</OrderType>
<DeliveryTerms>Delivered Free</DeliveryTerms>
<Positions>
<Position PosNo="1">
<Item ItemNo="CF001">
<ItemDescription>Champagne flute

1</ItemDescription>

<PricePerUnit
Currency="EUR">,60</PricePerUnit>
</Item>
<DeliveryQuantity>20</DeliveryQuantity>
<DeliveryValue
Currency="EUR">12,00</DeliveryValue>
</Position>
<Position PosNo="2">
<Item ItemNo="CF003">
<ItemDescription>Champagne flute

3</ItemDescription>

<PricePerUnit
Currency="EUR">1,45</PricePerUnit>
</Item>
<DeliveryQuantity>24</DeliveryQuantity>
<DeliveryValue
Currency="EUR">34,80</DeliveryValue>
</Position>
<Position PosNo="4">
<Item ItemNo="BS002">
<ItemDescription>Book shelves
2</ItemDescription>
<PricePerUnit
Currency="EUR">66,15</PricePerUnit>
</Item>
<DeliveryQuantity>40</DeliveryQuantity>
<DeliveryValue
Currency="EUR">2646,00</DeliveryValue>
</Position>
</Positions>
</Order>

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training
them and keeping them!"

-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Jon Paris
Gesendet: Wednesday, 20.8 2014 17:22
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: Re: Writing XML with RPGLE V7R1M0

Your example does seem to work well Birgitta - but even the simplest
of XML documents usually requires a bit more "shape". The script as
given (modified a bit to fit a DB I had to hand) produces this:

<Customers>
<Customer>
<ID>A0011</ID>
<PersName>Acme Best Brew </PersName>
<ZipCode>0</ZipCode>
<Zip-Plus>0</Zip-Plus>
<City>Acme </City>
</Customer>
... repeat Customer element

But what I wanted was:

<Customers>
<Customer>
<ID>A0011</ID>
<PersName>Acme Best Brew </PersName>
<Address> <<<< New compound element
<ZipCode>55901</ZipCode>
<Zip-Plus></Zip-Plus>
<City>Acme </City>
</Address>
</Customer>

And I have looked throughout the IBM and other examples and have not
been able to see how this is done. In all the other approaches
described in this thread providing this "shape" is trivial. I'd love
to know how the SQL approach handles it.

Also Zip-Plus is commonly an optional element and should not be
present when empty - how do I achieve that?

On 2014-08-20, at 12:57 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>

wrote:

Hi Brian,

You may check the following article:
Using RPG to exploit IBM DB2 XML support
http://www.ibm.com/developerworks/ibmi/library/i-using-rpg/index.htm
l

Using embedded SQL or better Reference Files is an even easier way
for writing the result to the IFS. (The most complex part is
building the SQL statement for generating the XML document.

The following souce code is a complete program for generating an XML
document and writing this document directly to the IFS:

D MyXMLDoc S SQLTYPE(XML_CLOB_File)
D

//******************************************************************
**
***
/Free
Exec SQL Set Option Commit=*None, DatFmt=*ISO,

CloSQLCsr=*EndActGrp;

Clear MyXMLDoc;
MyXMLDoc_Name = '/home/Hauser/WrtSQLXML.xml';
MyXMLDoc_NL = %Len(%Trim(MyXMLDoc_Name));
MyXMLDoc_FO = SQFOVR; //Replace if exists

Exec SQL
Select XmlDocument
(xmlgroup(EmployeeNo as "PersNo",
Trim(Trim (FirstName) concat ' ' concat
Trim(Name)) as "PersName",
Address as "Street",
ZipCode as "ZipCode",
City as "City"
Order By City Desc, Name
Option Row "Employee"
Root "Staff"))
into :MyXMLDoc
From LobStaff;

If SQLCODE < *Zeros;
Dsply 'Error occured';
else;
Dsply 'XML Document generated';
EndIf;

*InLR = *On;
/End-Free

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not
training them and keeping them!"

-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Brian Stapleton
Gesendet: Tuesday, 19.8 2014 21:56
An: rpg400-l@xxxxxxxxxxxx
Betreff: Writing XML with RPGLE V7R1M0

Good Afternoon

I need to learn how to write XML documents with RPGLE V7R1M0 onto our

IFS.

How can I educate myself on doing this?

I would prefer to stay as native RPGLE as possible, and am not
looking to purchase a 3rd party solution.

Brian Stapleton
Information Services
Carhartt, Inc
5750 Mercury Drive
Dearborn, MI 48126
Desk - 313-749-6586
Fax - 313-271-3455
BStapleton@xxxxxxxxxxxx<mailto:BStapleton@xxxxxxxxxxxx>

[Description: Description: Carhartt_emaillogo]

Confidential: This electronic message and all contents contain
information from Carhartt, Inc. and its affiliates which may be
confidential or otherwise protected from disclosure. The information
is intended to be for the addressee only. If you are not the
addressee, any disclosure, copy, distribution or use of the contents
of this message is prohibited. If you have received this electronic
message in error, please notify us immediately at (313) 271-8460 and

destroy the original message and all copies.

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email:
RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list
options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email:
RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list
options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at http://archive.midrange.com/rpg400-l.

Jon Paris

www.partner400.com
www.SystemiDeveloper.com

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email:
RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list
options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email:
RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list
options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/rpg400-l.

Jon Paris

www.partner400.com
www.SystemiDeveloper.com

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

Jon Paris

www.partner400.com
www.SystemiDeveloper.com