I'll second this. Did my first one a few years ago for a vendor requirement and it took under 30 minutes.
Roger Harman
COMMON Certified Application Developer - ILE RPG on IBM i on Power
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Eliason, Ellen
Sent: Tuesday, June 4, 2024 10:57 AM
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: RE: Writing XML
IF you should happen to have the GoAnywhere product from Fortra, it can be done quite easily from there.
Ellen
----------------------------------------------------------------------
message: 1
date: Tue, 4 Jun 2024 16:52:18 +0200
from: Dave <dfx1@xxxxxxxxxxxxxx>
subject: Writing XML
Hi,
Got given a bunch of PFs and told to generate an XML document from them.
Discovered the DB2 function XMLGROUP but it doesn't look easy to use for a complicated document. Don't we have a "standard way" of doing this yet?
Tia
------------------------------
message: 2
date: Tue, 4 Jun 2024 15:57:53 +0100
from: Jon Paris <jon.paris@xxxxxxxxxxxxxx>
subject: Re: Writing XML
The SQL functions are about as standard as you can get but I agree they are pretty ugly.
There is tooling in Python, node and PHP to do this for you if that is an option.
Otherwise in the past I have used CGIDEV2 templates and used them to generate XML docs.
Jon P.
On Jun 4, 2024, at 3:52?PM, Dave <dfx1@xxxxxxxxxxxxxx> wrote:
Hi,
Got given a bunch of PFs and told to generate an XML document from them.
Discovered the DB2 function XMLGROUP but it doesn't look easy to use
for a complicated document. Don't we have a "standard way" of doing this yet?
Tia
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
https://linkprotect.cudasvc.com/url?a=https%3a%2f%2flists.midrange.com
%2fmailman%2flistinfo%2frpg400-l&c=E,1,05z9Nq6uwXPKXqhXJr8V6gc6qzHVMXC
z7KUl90388LlhYtC7ryjp4bftQsVkyl40dJjj1LOSkIkpkkEJGn_kHV3bJnM74qcvxotoo
W_bGMcV&typo=1 or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://linkprotect.cudasvc.com/url?a=https%3a%2f%2farchive.midrange.com%2frpg400-l&c=E,1,esPgI1laU7V30r4FgHarQ5K_556vrLf7DNJEfiDMe_o6tkrjYPp0YdKzLXEzoIjcCornmXYjKhqCt5MaMG4gpwcxbkI9skIbDWL5FSNDvEV6Pf0,&typo=1.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
------------------------------
message: 3
date: Tue, 4 Jun 2024 17:07:38 +0200
from: Daniel Gross <daniel@xxxxxxxx>
subject: Re: Writing XML
Hi Dave,
the DB2 XML functionality looks more complicated as it is. Some time ago, I've written a piece for my blog, where I generated XML from SQL.
->
https://linkprotect.cudasvc.com/url?a=https%3a%2f%2fqpgmr.de%2fcreate
-> -xml-with-sql&c=E,1,zFPl_lStgeXM-HDumtIeLRrvok5xjHehId92hzlkO3agzbeaS
-> lbAkFDck_m4O0xmyqVUicnYlSMqsy06XrGd2srBpUoHZM4sW3UmxX_AoYwUKdTyN4mhk9
-> rgYTU,&typo=1
I tried to have different XML structures in the example - it it's a bit "over engineered" - but there is XMLELEMENT, XMLAGG, XMLATTRIBUTES, XMLDOCUMENT and XMLSERIALIZE - everything you need to build a XML document.
HTH
Daniel
Am 04.06.2024 um 16:52 schrieb Dave <dfx1@xxxxxxxxxxxxxx>:
?Hi,
Got given a bunch of PFs and told to generate an XML document from them.
Discovered the DB2 function XMLGROUP but it doesn't look easy to use
for a complicated document. Don't we have a "standard way" of doing this yet?
Tia
------------------------------
message: 4
date: Tue, 04 Jun 2024 09:05:51 -0700
from: VERNON HAMBERG Owner via RPG400-L <rpg400-l@xxxxxxxxxxxxxxxxxx>
subject: Re: Writing XML
+1 on CGIDEV2 - here is a simple example of how to do this -
https://linkprotect.cudasvc.com/url?a=https%3a%2f%2fitpscan.ca%2fblog%2fiSeries%2fxml_from_cgidev2.php&c=E,1,3M2HrHAasfMCrU4j1BH0jSFsRkm8HqxtNCeQBkTHX-zeZ3OZLqp9VaYxm9thwIx1Lut61JgE56HtgHhqk3pagY66kzPS8Q_AobW8MAfI2gj-YiCcB8w1bPj-RpI,&typo=1
You can probably google for more.
Cheers
Vern
On Tue, 4 Jun, 2024 at 9:58 AM, Jon Paris <jon.paris@xxxxxxxxxxxxxx> wrote:
To: rpg programming on the ibm i (as/400 and iseries)
The SQL functions are about as standard as you can get but I agree they are pretty ugly.
There is tooling in Python, node and PHP to do this for you if that is an option.
Otherwise in the past I have used CGIDEV2 templates and used them to generate XML docs.
Jon P.
On Jun 4, 2024, at 3:52?PM, Dave <dfx1@xxxxxxxxxxxxxx<mailto:dfx1@xxxxxxxxxxxxxx>> wrote:
Hi,
Got given a bunch of PFs and told to generate an XML document from them.
Discovered the DB2 function XMLGROUP but it doesn't look easy to use
for a complicated document. Don't we have a "standard way" of doing this yet?
Tia
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email:
RPG400-L@xxxxxxxxxxxxxxxxxx<mailto:RPG400-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit:
https://linkprotect.cudasvc.com/url?a=https%3a%2f%2flists.midrange.com
%2fmailman%2flistinfo%2frpg400-l&c=E,1,M7I4n7m8p4wo2HWP5IefW6_iF7NKOgJ
P69Smgc4KNB5y78Y9-7sNUuRJGCu_vExMaJwzKSIo4eP1sKuwExTJP_tAeJIcizMMFU5Cg
M49VLgW3DQVqGFsnjUaPw,,&typo=1 or email:
RPG400-L-request@xxxxxxxxxxxxxxxxxx<mailto:RPG400-L-request@xxxxxxxxxx
ange.com> Before posting, please take a moment to review the archives
at https://linkprotect.cudasvc.com/url?a=https%3a%2f%2farchive.midrange.com%2frpg400-l&c=E,1,x-8vwgE-DM3tnABwHg2ZKPN735pkdu66y3a1tFcBQTixLMWOSsFW11gH_xNgjsL76I3GMWpmorXTlbc31J8YJ95bPtI_AuJnXfES4nbKti3gnStpAP9bT5fjrg,,&typo=1.
Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx<mailto:RPG400-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit:
https://linkprotect.cudasvc.com/url?a=https%3a%2f%2flists.midrange.com%2fmailman%2flistinfo%2frpg400-l&c=E,1,vUT8srSaJ1si9_5wRzceTORhIFNWL-gzmc_j7gCqJkbHVDJlaeR8IKxAOvEfHeEr69ZXbfP6aFvPyUB0GUAYfVRy68l0AhOD5jPZHoZJAVavnujD04SekihEUJM,&typo=1
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx<mailto:RPG400-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives at
https://linkprotect.cudasvc.com/url?a=https%3a%2f%2farchive.midrange.com%2frpg400-l&c=E,1,MZdBP_1OP_kdL0UaHgU2BR7dOcEhZif_GPt4H5TUK3kNXsxmjLCmpFgvMCpVrEFi7csYr8uVkqP0U3nFevivHao7x-gAdn2qtNoXaljPVwBSeiYGnHQ,&typo=1.
Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.
------------------------------
message: 5
date: Tue, 4 Jun 2024 18:45:50 +0200
from: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
subject: RE: Writing XML
XMLGROUP is only for converting a table (or SELECT statement) into XML.
For everything else we have publishing functions (such as XMLELEMENT, XMLATTRIBUTES, XMLNAMESPACES) and even a lot of functions which make live easier, i.e. XMLCONCAT, XMLFOREST, XMLROW.
The problem is to convert the relational database into a hierarchical form.
... and nesting multiple SELECTs make the SQL Statement unmaintainable.
But there are better ways. Instead of nested sub-selects you use common table expressions (CTE) which can be considered as kind of temporary view or table that are only valid for the current SELECT Statement. A CTE can be used in the next CTE and also in the final SELECT statement. And each CTE can be executed on its own.
In the first few CTEs you generate the raw data you want to share and then you start generating the XML Statement from the lowest level. ... embed the generated XML then in the next level and so on until you have the complete XML in the final select statement:
With CTE1 as (Select ....),
CTE2 as (Select ....),
...
CTEn as (Select ...)
Select *
From ...
Here a little more complex example:
With -- 0. Select all Orders delivered in December 2009
DelOrders as (Select CustNo, h.*, a.*
From OrderHdrx h join AddressX a using (CustNo)
Where DelDate between '2009-12-01' and '2009-12-31'),
-- 1. Address information:
-- <Customer
CustNo="xx"><Name></Name><Street></Street><ZipCode></ZipCode><City></City>
-- </Customer>
Address as (Select OrderNo, 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 DelOrders),
-- 2. Order Header Information:
-- <Mandant></Mandant><OrderNo></OrderNo>
--
<DeliveryDate><DeliveryDate><OrderType></OrderType><DeliveryTerms></Delivery
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 DelOrders),
-- 3. Joining Order Header and Address Information (CTE No. 1 and 2)
HdrAddr as (Select Company, OrderNo, XMLConcat(OrderNoXML, CustXML,
HdrAddXML) OrderXML
from Header Left Join Address Using(OrderNo)),
-- 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;;
It is not more difficult that writing a modular RPG with multiple sub-procedures (even easier)!!!
SQL ist not more complicated than RPG.
Mit freundlichen Gr??en / Best regards
Birgitta Hauser
Modernization ? Education ? Consulting on IBM i Database and Software Architect IBM Champion since 2020
"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!"
"Train people well enough so they can leave, treat them well enough so they don't want to. " (Richard Branson) "Learning is experience ? everything else is only information!" (Albert
Einstein)
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Dave
Sent: Tuesday, 4 June 2024 16:52
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: Writing XML
Hi,
Got given a bunch of PFs and told to generate an XML document from them.
Discovered the DB2 function XMLGROUP but it doesn't look easy to use for a complicated document. Don't we have a "standard way" of doing this yet?
Tia
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://linkprotect.cudasvc.com/url?a=https%3a%2f%2flists.midrange.com%2fmailman%2flistinfo%2frpg400-l&c=E,1,6ERPa1EZiaVu9psgW39H7zngqcFTyafP9RxsPHd7oQXF9tEn40jOoBJlpVlLyD6DkpiKtqgkJrS_-2nT6KzXEQPGyzXoFf7uAcGL67hRgqp-7k558N9fwqIv2XdW&typo=1
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://linkprotect.cudasvc.com/url?a=https%3a%2f%2farchive.midrange.com%2frpg400-l&c=E,1,zS-yYOGomqtd5Rg-JaJGbe6AsOgsvrOdkKQX0jwFZnnkiJNwWcUMm9TXaP9MYb1bIVcVbWRxDQbqx_BBK_q5L_kLN4nZ4kInrOy3nd8Lag5YQWP1ioIW5iVWig,,&typo=1.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
------------------------------
message: 6
date: Tue, 4 Jun 2024 09:47:54 -0700
from: "James H. H. Lampert via RPG400-L" <rpg400-l@xxxxxxxxxxxxxxxxxx>
subject: Re: Writing XML
Hmm. In most cases, if I have to send XML out of RPG, I just build it in D-specs.
--
JHHL
------------------------------
Subject: Digest Footer
--
This is the RPG programming on IBM i (RPG400-L) digest list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://linkprotect.cudasvc.com/url?a=https%3a%2f%2flists.midrange.com%2fmailman%2flistinfo%2frpg400-l&c=E,1,0HQTpGEIy-KuIyYY0eSX-053dygRZMC1lVn2eaQgYRpv7FP1edjs-MNzvf6wFw12qax07b8q4OdX8_JRXJ3labR6yg-5r3e2_Wdwgfg37VY,&typo=1
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://linkprotect.cudasvc.com/url?a=http%3a%2f%2farchive.midrange.com%2frpg400-l&c=E,1,gEJT88CGETqPUXFxI3-cFaGEW4wD4nTB6Be-2WZ-qdu6XHxuqJa_JPJB5vKxLo92Hg2ZytGPWlNc5VQM4LFbQiHd71zqFFdB6Y6dquTI3M-9UmETgVHp&typo=1.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
------------------------------
End of RPG400-L Digest, Vol 23, Issue 148
*****************************************
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
midrange.com
