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://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
