Re: AW: Writing XML with RPGLE V7R1M0

Birgitta

I agree that I've not done enough to learn the SQL approach - and I also have a different situation - I am using XML that is given me from an outside source - actually, MS Office.

There is a lot of content in that XML that is not our data. I really don't want to put all of that into my programs, especially things that can cause very interesting problems when I get something just a little wrong, like how many rows and columns in the final output. I can hardly wait to work with pivot tables!

I just sent something with some considerations as I see it for choosing an approach. I think there is a place for the several ways - I can think of 3 approaches right now.

Thx
Vern

On 8/21/2014 12:29 AM, Birgitta Hauser 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.